Sysadminsblog.com Anything for sysadmins!

28Jun/110

Migrating the ADFS 2.0 Configuration Database to MS SQL

By default when you configure ADFS 2.0 it will create a Windows Internal Database for its configuration database. However if you have a MS SQL server running already this is kind of unnecessary. Thankfully it’s possible to migrate the ADFS 2.0 databases to MS SQL.

Preparations

It’s smart to start with a backup of the Federation Server.

If your federation server is running in a farm and it’s behind a load balancer, temporarily remove it from the load balancing configuration.

On the Primary federation service

  1. Download the SQL Server 2008 Management Studio Express software and install it (you’ll need sqlcmd)
  2. Stop the ADFS 2.0 service. Start an elevated command prompt and type:
    net stop adfssrv
  3. Connect to the Windows Internal Database and detach the databases by running the following commands:
    sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query<br/>use master<br/>go<br/>sp_detach_db 'adfsconfiguration'<br/>go<br/>sp_detach_db 'adfsartifactstore'<br/>go
  4. Connect to the MS SQL server and attach the databases by running the following commands (note that the paths are local paths on the SQL server so make sure that the files are on the local server):
     sqlcmd -S &lt;SQLServer\SQLInstance&gt;<br/>use master<br/>go<br/>sp_attach_db 'adfsconfiguration', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsconfiguration.mdf', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsconfiguration_log.ldf'<br/>go<br/>sp_attach_db 'adfsartifactstore', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsartifactstore.mdf', 'c:\windows\sysmsi\ssee\mssql.2005\mssql\data\adfsartifactstore_log.ldf'<br/>go<br/>alter database AdfsConfiguration set enable_broker with rollback immediate<br/>go
  5. Change the configuration database connection string to point to the new MS SQL server by running the following PowerShell commands:
    $SecTokenServ = Get-WmiObject -NameSpace root/ADFS -Class SecurityTokenService<br/>$SecTokenServ.ConfigurationdatabaseConnectionstring="data source=&lt;SQLServer\SQLInstance&gt;; initial catalog=adfsconfiguration;integrated security=true"<br/>$SecTokenServ.Put()
  6. Start the ADFS 2.0 service. Start an elevated command prompt and type:
    net start adfssrv
  7. Change the configuration database connection string to point to the new MS SQL server by running the following PowerShell commands:
     Add-PSSnapin Microsoft.ADFS.PowerShell<br/>Set-ADFSProperties -ArtifactDBConnection "data source=&lt;SQLServer\SQLInstance&gt;; initial catalog=adfsartifactstore;integrated security=true"
  8. Stop and start the ADFS 2.0 service:
    net stop adfssrv
    net start adfssrv

Don’t forget to add the primary federation server to the load balancing configuration.

To migrate other ADFS 2.0 servers in the farm start by removing the server from the load balancing configuration and stopping the service (net stop adfssrv) on that server. Then start at step 5 of steps above.

Afterwards add the servers back into the load balancing configuration to have it accept requests.

Be Sociable, Share!

Posted by Mischa Oudhof