You already have a working ADFS setup which has been configured to use the Windows Internal Database (WID) to store its configuration database. However, things may have changed since you implemented it and you may now have one (or more) of the below requirements which will need an upgrade to SQL server.

  • Need more than five federation servers in the ADFS Farm (supporting more than 10 relying parties)
  • Leverage high availability features of SQL or
  • Enable support for SAML artefact resolution or WS Federation token replay detection.

The below step-by-step procedure should help you with the migration of the ADFS configuration database from WID to SQL with minimal or no downtime (however, plan accordingly such that it has the least impact in case something goes wrong).

The steps also cover configuration of each of the ADFS servers (Primary and Secondary) in the farm to use the SQL Server for its configuration database.

For simplicity, i have used the below scenario comprising of:

Proposed Design

DMZ

  • Two Web Application Proxies (WAP) – wap1 and wap2
  • External load balancer (ELB) in front of the WAPs.

Private / Corporate network

  • Two ADFS Servers – adfs1 and adfs2
  • Internal Load Balancer (ILB) in front of the ADFS Servers
  • SQL Server (Standalone). Additional steps need to be performed (not covered in this blog) when using SQL Server with high availability options such as SQL Always-On or Merge Replication

Backups

Ensure you have a complete backup of your ADFS servers. You can use Windows Server Backup or your thirty-party backup solution to backup the ADFS servers.

Load Balancer Configuration

During the course of this exercise the internal load balancer will be configured multiple times to ensure a smooth migration with minimal impact to end users.

Remove the primary ADFS Server (adfs1) from the internal load balancer configuration such that all traffic is directed to the secondary server (adfs2).

Primary ADFS Server steps

  • Stop the ADFS windows service by issuing “net stop adfssrv” in an elevated command prompt or via the Windows Services Manager.

net stop adfssrv

  • Download and install SQL Server Management Studio (SSMS) (if not already present)
  • Launch SSMS in Administrator mode
  • Connect to your WID using \\.\pipe\MICROSOFT##WID\tsql\query as the server name in SSMS.

SSMS connect dialog

You should be able to see the two ADFS databases (AdfsArtifactStore and AdfsConfiguration) as shown below:

SSMS showing the two ADFS databases

  • To find the physical location of the ADFSConfiguration and ADFSArtifactStore in WID, run the below query  by starting up a ‘New Query’. The default path is C:\Windows\WID\Data\.

[code lang=”sql”]
SELECT name, physical_name AS current_file_location FROM sys.master_files
[/code]

Results showing physical location of DB files

  • Restart WID from SSMS. This is just to ensure that there is no lock on the databases. Right Click on the WID db and select ‘Restart‘.

Restarting the database

Restarting the database

  • Now we need to detach both the databases. Run the below query on the WID using SSMS

[code lang=”sql”]
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N’AdfsArtifactStore’
GO
EXEC master.dbo.sp_detach_db @dbname = N’AdfsConfiguration’
GO
[/code]

Running the commands on the WID

  • Now copy the databases identified earlier from the Primary ADFS Server to your SQL Server’s Data directory (for example C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA).

p8

SQL Server – Steps

  • On the SQL Server, bring up the SQL Server Management Studio (SSMS) and connect to the SQL instance (or default instance) where the ADFS databases will be hosted.
  • Create a login with the ADFS windows service account (which was used for the initial ADFS setup and configuration). I used Contoso\svcadfs.

Adding SQL Server user

  • Now attach the databases copied earlier on the SQL server. Run the below using the SQL Server Management Studio. Modify the path as appropriate if the db files were copied to a location other than ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA’

[code language=”sql”]

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

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

[/code]

  • On successful execution of the above, you should be able to see the two ADFS databases in SSMS (you may need to do a refresh if not displayed automatically)

Two databases shown in SSMS

  • Ensure that the ADFS Service Account has the “db_genevaservice” role membership on both the databases

Grant service account right database role

Firewall Configuration

Ensure that the SQL Server is reachable from the ADFS servers on port 1433. You may need to update network firewalls and / or host firewall configuration on the SQL Server (depending on the type of network setup you may have).

Primary ADFS Server Steps

  • Start the ADFS windows service by issuing “net start adfssrv” from an elevated command prompt or from the Windows Services Manager
  • Launch a PowerShell console in Administrator Mode and execute the below lines in order

[code lang=”powershell”]

$temp= GEt-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring="data source=[sqlserver\instance];initial catalog=adfsconfiguration;integrated security=true"
$temp.put()

[/code]

Note: replace [sqlserver\instance] with actual server\instance. If not running as an instance, just server. I am using ‘SQLServer’ as it is the hostname of the SQL server being used in this example.

PowerShell Configuration

  • Change the connection string property in “AdfsProperties” by issuing the below command from the PowerShell console

[code lang=”powershell”]

Set-AdfsProperties -ArtifactDbConnection "Data Source=[sqlserver\instance];Initial Catalog=AdfsArtifactStore;Integrated Security=True"

[/code]

Note: Change [sqlserver\instance]  with the name of your SQL server and instance (as applicable)

PowerShell Configuration

  • Restart the ADFS Service by executing “net stop adfssrv” and “Nnet start adfsrv” from an elevated command prompt or from the Windows Services Manager.

Restarting service

  • To check if the configuration has been successful, run “Get-AdfsProperties” from a PowerShell console. You should see the ADFS properties listed (as below) with the key being Data Source=SQLServer; Initial Catalog=AdfsArtifactStore; Integrated Security=True

Output from Get-AdfsProperties

This completes the migration of the ADFS configuration database from WID to SQL and also the configuration of the Primary ADFS server to use the SQL Database. Now we need to configure the secondary ADFS server(s) to use the SQL Database.

Load Balancer Configuration

Update the internal load balancer to:

  • Add the Primary ADFS (adfs1) to the load balance configuration and
  • Remove the secondary ADFS (adfs2) server which needs to be reconfigured to point to the SQL Server.

Secondary ADFS Server steps

  • Stop the ADFS Windows service by issuing “net stop adfssrv” in an elevated command prompt
  • To change the configuration database connection string to point to the new SQL ADFS configuration database run the below command lines (in order) from a PowerShell Console

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

Note: Change [sqlserver\instance] with the name of your SQL server / instance as used for the primary server configuration.

PowerShell Configuration

  • Start the ADFS Service by executing “Net Start ADFSSRV” from an elevated command prompt and verify that the service starts up successfully. ( I have had the issue where my ADFS server was (strangely) not able to resolve the NETBIOS name of the SQL Server, hence the service wouldn’t start properly. Also, check if the federation service is running using the service account that was provided login to the SQL Database)
  • To check if the configuration has been successful, run “Get-AdfsProperties” from a PowerShell console. You should see the ADFS properties listed (as below) with the key being  Data Source=SQLServer; Initial Catalog=AdfsArtifactStore; Integrated Security=True

Output from Get-AdfsProperties

Repeat above steps for each of the secondary servers (if you have more than one) and ensure that all ADFS servers are added back to the internal load balancer configuration.

I hope this post has been useful.

Category:
ADFS, Identity and Access Management, Office 365, Security, Technology
Tags:
, , , , , , ,

Join the conversation! 17 Comments

  1. Is it possible to migrate from SQL to WID?

  2. Thank you so much , this is such a help full for me. Thanks again mate.

  3. Hi,

    Quick question. I am not able to finish configuring ADFS WAP server that is pointing to a secondary ADFS server. Is this normal?
    The configuration to the primary ADFS is fine.

    Many thanks.

    DRN

    • Hi DRN,
      I would check the networking between your DMZ and that second AD FS server.
      If the WAP can’t communicate full mesh between your AD FS servers, it won’t finalize setup.

      You can trick it by editing your hosts file if you need to though.

      Cheers, Lucian

  4. We are quite a small shop and just have 2 ADFS servers in a ADFS farm.
    The ADFS servers are located in 2 different states and the ADFS farm is working without any issues.
    The only issue encountered was the WAP at DR was not able to communicate with the secondary ADFS server.

    Cheers

  5. I just completed this process and wanted to let you know that the code snippet you have for your secondary adfs server has some goofy formatting errors. You can see that by comparing it with the screen shot.

  6. Hi, I can’t start AD FS after pointing to SQL. I can connect to database from AD FS server using SMS. Any hint? Thanks in advance!

    Log Name: AD FS/Admin
    Source: AD FS
    Date: 3/24/2017 5:56:51 PM
    Event ID: 352
    Task Category: None
    Level: Error
    Keywords: AD FS
    User: *******\svc_adfs
    Computer: *****************
    Description:
    A SQL operation in the AD FS configuration database with connection string Data Source=[SQL2008R2\SQL_ADFS];Initial Catalog=adfsconfiguration;Integrated Security=True failed.

    Additional Data

    Exception details:
    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

  7. What was the “Contoso\svcadfs” user for? You used integrated security all the way? ..What about all the dbo owner issues in application logs afterwards?

  8. I used backup to backup database using SSMS & restore on another SQL server. By default you will not be able to back, here’s is the trick.
    Login using ADFS service account.
    Run SSMS as Administrator & backup database. & you are good to go.

  9. Thank you, every step and reference was correct. You rocks!

  10. This is really helpful.
    Few helpful comment
    1. For the Primary ADFS Server change
    a. ADFS Service should be stopped before executing the change for the ConfigurationdatabaseConnectionstring
    b. ADFS Service should be in start mode while executing the AdfsArtifactStore connection string change

    2. For movement of WID to SQL server for ADFS 4.0 running on Windows 2016

    ConfigurationdatabaseConnectionstring need to be modified for initial catalog=adfsconfiguration will be replaced by initial catalog=adfsconfigurationV3 like below

    $temp.ConfigurationdatabaseConnectionstring=”data source=<SQLServer\SQLInstance>; initial catalog=adfsconfigurationV3;integrated security=true”

  11. I’m unable to start the service on the secondary server after updating the connectionstring. I’ve confirmed port access to 1433, can resolve NETBIOS, DNS is not a factor, service account is set correctly and service account has all the right permissions on the DB. My primary server has no issue connecting to the migrated DB. All I get in event log is
    “The Active Directory Federation Services service terminated with the following error:
    An exception occurred in the service when handling the control request.” under eventid 7023. Any thoughts or suggestions?

  12. On the ADFS Server I performed the sp_detach_db on N’AdfsArtifactStore’ AND N’AdfsConfiguration’

    From then onward ADFS service will not start. I assume this is because the ADFS databases are detached. I re-attach but still same error below.

    The first PS code does not return any error.

    Set-AdfsProperties fails:

    ADMIN0017. Exception occured while connecting to the configuration service. URL maybe incorrect or AD FS service is not running.

    I can telnet to the SQL Server 2012 on 1433

    Any ideas?

  13. Yeh, there is something wrong in the steps
    You cannot execute the powershell commands to re-point the databases on SQL server without having the ADFS service up and running
    Yet, you cannot start the service once the databased are detached from WID

    So how on earth did anyone complete the above steps successfully?

Comments are closed.