AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server

AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server

The AD FS configuration database stores all the configuration data that represents a single instance of AD FS 2.0 (also known as the Federation Service). You can store this configuration data in either a Microsoft SQL Server® database or using the Windows Internal Database. The Windows Internal Database is a Windows Server feature that is automatically installed on the computer whenever you complete the AD FS 2.0 Federation Server Configuration Wizard for the first time.
Since the wizard does not provide a UI option to choose SQL Server as the store for the AD FS configuration database it is understandable how many would continue to use the wizard defaults to see if it will work well for their infrastructure. It is highly possible that in time you may want to scale out your federation server farm to use more than 5 federation servers by migrating the configuration database to SQL Server. By migrating to SQL you will obtain scale, high availability and also be able to use SQL’s backup mechanisms.

This topic is provided for just this situation and will walk you through all the steps necessary to migrate your existing AD FS configuration data from your current Windows Internal Database store (in a production environment) to a new SQL Server store. Follow steps 1, 2, 3, and 5 on the primary federation server. Follow steps 1,2, 4 and 5 on each of the secondary federation servers in the farm. These steps are included in the following sections:



For more information about the pros and cons of using either Windows Internal Database or SQL Server to store AD FS 2.0 configuration data, see 
The Role of the AD FS Configuration Database in the AD FS 2.0 Design Guide.

Step 1: Backing up the federation server

 
Use Windows Server Backup to back up the entire federation server computer including the AD FS configuration database stored in Windows Internal Database. You can also use Windows Server Backup to restore the AD FS configuration database.
See this article for more detail: AD FS 2.0 - How to backup the Federation Service
 

Step 2: Temporarily disable the computer in the load balancer

 
If your federation server is running in a farm and you have a load balancer, temporarily remove this machine from the load balancer configuration.

Step 3: Performing steps on the primary federation server

 
1.  On the primary federation server in the farm, download the SQL Server 2008 Management Studio Express software and install it on the primary federation server using this link (http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en). This software is necessary in order to install and register the sqlcmd command-line tool which is necessary in an upcoming step.

2.   Stop the AD FS 2.0 Windows Service on the primary federation server. Open an elevated command prompt, type the following command-line to stop the AD FS 2.0 Windows Service, and then press ENTER:

net stop adfssrv

3.   Connect to the Windows Internal Database that currently stores the AD FS configuration database and then detach both the AD FS configuration and artifact databases. In the command prompt window, type the following sqlcmd command-line syntaxes in order, and then press ENTER after each one.

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
use master
go          
sp_detach_db 'adfsconfiguration'
go
sp_detach_db 'adfsartifactstore'
go

4.   Connect to SQL server and attach the configuration and artifact database from the primary federation server. In the command prompt window, type the following sqlcmd command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.

sqlcmd -S <SQLServer\SQLInstance>
use master
go
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'
go
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'
go
alter database AdfsConfiguration set enable_broker with rollback immediate
go

5.   Change the configuration database connection string to point to the new SQL Server-based AD FS configuration database. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.

$temp= GEt-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial catalog=adfsconfiguration;integrated security=true”
$temp.put()

6.   Open an elevated command-line prompt, type the following command-line syntax to start the AD FS 2.0 Windows Service, and then press ENTER:

Net start adfssrv

7.   Change the artifact connection string to point to the new SQL Server-based artifact data location. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the artifact data to. For example, contososrv01\adfs-artifact.

Add-pssnapin microsoft.adfs.powershell
Set-adfsproperties –artifactdbconnection “data source=<SQLServer\SQLInstance>; initial catalog=adfsartifactstore;integrated security=true”

8.   Stop and restart the AD FS 2.0 Windows Service to refresh the new settings. Open a regular command-line prompt, type the following command-line syntaxes to stop and start the AD FS 2.0 Windows Service, and then press ENTER after each one:

Net stop adfssrv
Net start adfssrv

Step 4: Performing steps on the secondary federation server

 
1.   
Make sure the primary federation server has been added back to the load balancer before proceeding with this section.

2.    Make sure the secondary federation server has been temporarily removed from the load balancer before proceeding.

3.    On a secondary federation server in the farm, open an elevated command prompt, type the following command-line to stop the AD FS 2.0 Windows Service, and then press ENTER:

net stop adfssrv

4.    Change the configuration database connection string to point to the new SQL Server-based AD FS configuration database. Open a Windows PowerShell command-line, type the following command-line syntaxes in order, and then press ENTER after each one. In SQLServer\SQLInstance below, type in the appropriate SQL Server and SQL Server instance name where you are migrating the configuration data to. For example, contososrv01\adfs.

$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial catalog=adfsconfiguration;integrated security=true”
$temp.put()

5.   Open a regular command-line prompt, type the following command-line syntax to start the AD FS 2.0 Windows Service, and then press ENTER:

Net start adfssrv
6.    Verify that the service starts up successfully.

7.    Repeat these steps for every federation server in this Windows Internal Database-based farm.

Step 5: Enabling this computer on the load balancer

 
Enable the computer in the load balancer so that requests are sent to it.


 

See Also

Sort by: Published Date | Most Recent | Most Useful
Comments
  • Thanks! Great article.

  • According to this technet article it isn't supported to upgrade the internal database to sql.

    technet.microsoft.com/.../ee913581(WS.10).aspx

    Note  

    The migration of an AD FS configuration database from a Windows Internal Database to an instance of SQL Server is not supported in AD FS 2.0.  

    Is this true?

    Thanks

  • I tried doing these steps in the default UI in management studio and I got an error - something like "not supported". I wll have to try these script directions next time. Very interesting article.

  • Nice Article..Thanks

  • Nice Article..Thanks

  • Nice Article..Thanks

  • On point 3 of Step 3 please modify "use mastergo" to "use master" and on next line "go"

  • In step 4, point 6 you change the connection of the artifact db again, which you also did in step 3.

    Why are you doing this?

    Isn't the location of the artifact db configured in the configuration db and therefore already set...?

  • The service will not restart when at Step 3 - part 6.  Errors in the Event Log state:

    A SQL operation in the AD FS configuration database with connection string Data Source=SQLSRV01;Initial Catalog=adfsconfiguration;Integrated Security=True failed.  

    Additional Data

    Exception details:

    Login failed for user 'xxx\svc_ADFSv2'.

    Do I need to set additional permissions for the service account on the target SQL server?

  • Sorted, added the service account as a Logon on the new SQL server...  Cheers

Page 1 of 2 (15 items) 12