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
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:
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:
7. Repeat these steps for every federation server in this Windows Internal Database-based farm.
Jorge de Almeida Pinto [MVP-DS] edited Revision 29. Comment: The config DB connection string is set on a PER SERVER basis, therefore you must adjust it on ALL ADFS STS servers. The artifact DB connection is an ADFS property stored in the config DB, therefore changing it once on one ADFS STS server is enough. When retargeting any second or later ADFS STS server with a new connection string for the config DB it will automatically get the new artifact connection string already changed on the first ADFS STS server
Richard Mueller edited Revision 28. Comment: Added tags
Sean Ivey [MSFT] edited Revision 26. Comment: Step 3.3, changed use mastergo to use master <return> go
Brian Desmond -MVP- edited Revision 25. Comment: Added missing line breaks
Ed Price - MSFT edited Revision 22. Comment: Formatting code
Ed Price - MSFT edited Revision 12. Comment: TOC
Ed Price - MSFT edited Revision 11. Comment: Font
Odin1 edited Revision 9. Comment: fixed the code block in Step 3 item 4
Odin1 edited Revision 7. Comment: I corrected a typo by inserting some new lines for the SQLCMD code in step 4.3.
Odin1 edited Revision 8. Comment: re-doing change for apearance
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
On point 3 of Step 3 please modify "use mastergo" to "use master" and on next line "go"