Migrate upgraded ADFS farm from WID to SQL

Method (1) [Fast and Safe] via Backup and Restore as SQL (Windows 2016 or newer only [FBL 3-4]):

This guide outlines the recommended steps for migrating your Active Directory Federation Services (ADFS) databases from Windows Internal Database (WID) to SQL Server, using ADFS Rapid Restore. Migrating to SQL Server enhances scalability, performance, and high availability of your ADFS deployment.

• The Benefits of Using SQL Server for ADFS and Feature:
• Supports Large Deployments: Ideal for environments with over 100 trust relationships.
• Advanced Features: Enables token replay detection and SAML 2.0 artifact resolution (not available with WID).
• High Availability: Supports clustering, mirroring, and backup for better resilience.
• Centralized Management: All federation servers share a single configuration database.
• Enhanced Security: Supports encrypted communication between AD FS and SQL Server.
• Better Monitoring: Integrates with reporting tools for operational insights.
• Smart Lockout Configure AD FS Extranet Smart Lockout Protection

 

ADFS Rapid Restore Federation Services Rapid Restore tool to back up your current environment and restore it using WID, you can Simplify the Migration process by using the ADFS Rapid Restore Tool in a service window

• Backup for current Primary ADFS Server

1. Install the Rapid Restore tool.
2. Import module by running below PowerShell command:
   

   Import-Module 'C:\Program Files (x86)\ADFS Rapid Recreation Tool\ADFSRapidRecreationTool.dll'

3. Perform the backup to destination folder you want by running below PowerShell command:
   

   Backup-ADFS -StorageType "FileSystem" -StoragePath "C:\ADFS-Backup\" -EncryptionPassword "password" -BackupComment "To restore as SQL string" -BackupDKM

    

   Restore Into same ADFS Server: to perform this action must be a Domain Admin account with sysadmin rights over destination SQL Server to perform database creations.

 

1. Assign Permissions: Grant the ADFS service account the necessary database roles (sysadmin / db_owner) on the SQL Server instance hosting the ADFS databases to ensure proper operation.

     5. Perform the restore by running below PowerShell in the new or same server that you want to be use the ADFS as SQL database connection string:

 

#Restore to SQL gMAS

Restore-ADFS -StorageType "FileSystem" -StoragePath "C:\ADFS-BACKUP\" -DecryptionPassword "Passw0rd_123!" -DBConnectionString "Data Source=SQLserver.contoso.Local; Integrated Security=True" -GroupServiceAccountIdentifier "contoso\ADFS-gMAS$" -RestoreDKM

#Restore to SQL standard user

Restore-ADFS -StorageType "FileSystem" -StoragePath "C:\ADFS-BACKUP\" -DecryptionPassword "Passw0rd_123!" -DBConnectionString "Data Source=SQLserver.contoso.Local; Integrated Security=True" -ServiceAccountCredential (Get-Credential contoso\ADFS-Srv-Account) -RestoreDKM 

 

• Test the New ADFS Server: Update your local hosts file to point to the new ADFS server's IP address. Access the ADFS sign-in page to verify successful authentication and connectivity to the SQL backend.

•  Once everything is going as expected, add the new ADFS to the load balancer and remove the other ADFS servers one by one from load balancer to change the database string to the new SQL server string.

 

• Updating Database Connection String for the remaining ADFS Servers to point SQL and be careful by remove them from the load balancer and readd them again after test the server by point the IP of the ADFS server itself in the Hosts file.

 

1. Stop ADFS Service by command prompt.
2. Run below PowerShell commands:

Stop-Service adfssrv 

$svc = Get-WmiObject -Namespace root/ADFS -Class SecurityTokenService 

Change

•  Initial Catalog=AdfsConfigurationV4 to your database version.
• <Data Source=SQLserver.Contoso.local>to your SQL server.

$svc.ConfigurationDatabaseConnectionString = "Data Source=SQLserver.Contoso.local;Initial Catalog=AdfsConfigurationV4;Integrated Security=True" ; $svc.Put() 

ReStart-Service adfssrv 

Set-adfsProperties -artifactdbconnection "Data Source=SQLserver.Contoso.local;Initial Catalog=AdfsArtifactStore;Integrated Security=True" 

ReStart-Service adfssrv

After restart the ADFS service check Event Viewer to confirm migration success, look for Event ID 100, indicating the ADFS service started successfully and is connected to SQL Server, By following these steps, you will successfully migrate your ADFS databases from WID to SQL Server, improving HA, reliability and supporting future scalability.

 

Note: by Using RRT Tool, the process is normally quick and automatic, however it performs a Farm installation based on all content of the backed-up database creating a new Database into destination SQL Server informed into DBConnectionString parameter. the credentials to perform this action must be a Domain Admin account with sysadmin rights over destination SQL Server to perform database creations.

 

--------------------------------------------------------------------------------------------------------------------

 

Method (2) by Manual attach the databases in SQL server directly:

This is special works for ADFS 2012/2012R2 and also for above (Windows 2012 or newer [FBL 0-3-4]):

 

Prepare a New ADFS Server

• Deploy a new ADFS server with only the ADFS role installed only Install-WindowsFeature -Name ADFS-Federation. This server will be used for the migration to connect to the SQL database.
• Copy Database Files by ensure the Windows Internal Database (WID) service is stopped to prevent file lock or corruption from C:\Windows\WID\Data to your SQL Server.

#Stop the to be able to copy the databases Stop-Service -Name "Windows Internal Database"

 

// from C:\Windows\WID\Data on any existing ADFS server to your SQL Server. AdfsArtifactStore.mdf AdfsArtifactStore.ldf AdfsConfigurationVx.mdf AdfsConfigurationVx_log.ldf

 

• Attach Databases in SQL Server: Open SSMS and use the "Attach" feature to add both the AdfsArtifactStore and AdfsConfigurationVx databases to the SQL Server instance.

#Go to the SQL server where the ADFS databases exists AdfsConfigurationV4 and give permission by run the below to able to attach the databases #for MSSQLSERVER icacls "C:\ADFS-Database\" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /t #Or SQLEXPRESS icacls "C:\ADFS-Database\" /grant 'NT SERVICE\MSSQL$SQLEXPRESS:(OI)(CI)F' /t

 

• Assign Permissions: Grant the ADFS service account the necessary database roles (sysadmin / db_owner) on the SQL Server instance hosting the ADFS databases to ensure proper operation.

• Join the New ADFS Server to the Farm: On the new ADFS server, run the PowerShell command Add-AdfsFarmNode to join the server to the existing ADFS farm. Use the appropriate parameter for the ADFS service account (either gMSA or standard user).

#gMSA Service Account Add-AdfsFarmNode -CertificateThumbPrint $Thumb -GroupServiceAccountIdentifier "DOMAIN\ADFSgMSA$" -SQLConnectionString "Data Source=SQL.Contoso.local;Integrated Security=True" #Normal Service Account Add-AdfsFarmNode -CertificateThumbPrint $Thumb -ServiceAccountCredential (Get-Credential Contoso\ADFS-Service-Account) -SQLConnectionString "Data Source=SQL.Contoso.local;Integrated Security=True"

 

• Avoid Configuration Changes: At this stage, your ADFS farm is in a "split-brain" database state, where some servers are using WID and others are using SQL. Do not make any ADFS configuration changes until all servers are using the new SQL database connection for all farm if you decided to continue with this SQL approach.

• Test the New ADFS Server: Update your local hosts file to point to the new ADFS server's IP address. Access the ADFS sign-in page to verify successful authentication and connectivity to the SQL backend.

•  Once everything is going as expected, add the new ADFS to the load balancer and remove the other ADFS servers one by one from load balancer to change the database string to the new SQL server string.

# Update AdfsConfigurationV4 database connection string: Stop-Service adfssrv $svc = Get-WmiObject -Namespace root/ADFS -Class SecurityTokenService $svc.ConfigurationDatabaseConnectionString = "Data Source=SQLserver.Contoso.local;Initial Catalog=AdfsConfigurationV4;Integrated Security=True" $svc.Put() ReStart-Service adfssrv #Change Artifact database connection string: Set-adfsproperties -artifactdbconnection "Data Source=SQL.serverContoso.local;Initial Catalog=AdfsArtifactStore;Integrated Security=True" ReStart-Service adfssrv

 

If testing is successful, proceed to the next step: Update Connection Strings on the remaining ADFS servers. 

 

Update Connection Strings on All ADFS Servers: On both Primary and Secondary ADFS servers, stop the ADFS service. Update their SQL connection strings using PowerShell, referencing the Always On Availability Group listener's DNS name if applicable. Sample commands Setting up an AD FS Deployment with Always-On Availability Groups:

 

• Uninstall the "Windows Internal Database" role and restart the server.

Remove-WindowsFeature Windows-Internal-Database

 

Also, all your JS customizations will be kept in the process as well as your entire configuration. The tweak you would have to do is if you enabled the SAML RelayState for IDP-initiated flow (as it used to be done in the .configfile on each server in 2012 R2 and is now a config to do with PowerShell in 2019).