Share via

Windows Server 2012 R2 - AD FS: Migrate Your AD FS Configuration Database from WID to SQL Server

  • Article

AD FS can be configured to store its configuration data in the Windows Internal Database (WID) or an instance of SQL Server. The WID option may be sufficient for many usage scenarios. If you have outgrown the WID option, take the following steps to upgrade to SQL server:

  1. Backup ADFS server

  2. Open a command prompt and issue the following command: NET STOP adfssrv

  3. Start an instance of SQL Server Management Studio in Administrator mode.

  4. Connect to WID @ \.\pipe\MICROSOFT##WID\tsql\query from SQL Server Management Studio. (this part has changed)

  5. Run the following query "SELECT name, physical_name AS current_file_location FROM sys.master_files" to find the location of the AdfsConfiguration and AdfsArtifactStore in WID. The default paths are: 

    • C:\Windows\WID\Data\AdfsConfiguration.mdf
    • C:\Windows\WID\Data\AdfsConfiguration_log.ldf
    • C:\Windows\WID\Data\AdfsArtifactStore.mdf
    • C:\Windows\WID\Data\AdfsArtifactStore.ldf

  6. Restart WID from SQL Server Management Studio (to make sure there is no lock on the databases)

  7. Detach both databases by running the following query:

    USE [master]
    GO
    EXEC master.dbo.sp_detach_db @dbname = N'AdfsArtifactStore'
    GO
    EXEC master.dbo.sp_detach_db @dbname = N'AdfsConfiguration'
    GO

  8. Copy the databases from the "C:\Windows\WID\Data" folder to the desired directory on the SQL Server. The default location is: "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA". You may want to "copy" the database instead of pointing SQL at the original WID database file so you have a backup.

  9. On the SQL Server, bring up the SQL Server Management Studio and connect to the SQL instance (or default instance) where the ADFS databases will be hosted on. Create a login with the ADFS service account.

  10. Attach the databases (default database location. Modify as needed):
    USE [master]
    GO
    CREATE DATABASE [AdfsConfiguration] ON
     ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdfsConfiguration.mdf' ),
     ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdfsConfiguration_log.ldf' )
      FOR ATTACH
    GO
    USE [master]
    GO
    CREATE DATABASE [AdfsArtifactStore] ON
     ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdfsArtifactStore.mdf' ),
     ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdfsArtifactStore_log.ldf' )
      FOR ATTACH
    GO
    ALTER DATABASE AdfsConfiguration set enable_broker with rollback immediate
    GO

  11. Make sure the ADFS service account has the "db_genevaserivce" role and access to both databases.

  12. Open up firewall for port 1433 on the SQL Server. Without this step, you'll get an error

  13. On the ADFS Server, from a PowerShell console running in Administrator mode, type in the following three lines:

     $temp= GEt-WmiObject -namespace root/ADFS -class SecurityTokenService
     $temp.ConfigurationdatabaseConnectionstring=”data source=[sqlserver\instance]; initial catalog=adfsconfiguration;integrated security=true”
     $temp.put()
     * Note: replace [sqlserver\instance] with actual server\instance. If not running as an instance, just server.

  14. Change the connection string property in AdfsProperties:
     Set-AdfsProperties -ArtifactDbConnection "Data Source=[sqlserver\instance];Initial Catalog=AdfsArtifactStore;Integrated Security=True"
     Original value: Data Source=np:\.\pipe\microsoft##wid\tsql\query;Initial Catalog=AdfsArtifactStore;Integrated Security=True
     

  15. Restart ADFS service: NET STOP ADFSSRV and then NET START ADFSSRV

The instructions outlined above is for Windows Server 2012R2. For ADFS 2.0, please refer to this article:
ADFS 2.0 : Migrate Your ADFS Configuration Database to SQL Server