Most Microsoft-based Hybrid Identity implementations use Active Directory Federation Services (AD FS) Servers, Web Application Proxies and Azure AD Connect installations. In this series, labeled Hardening Hybrid Identity, we’re looking at hardening these implementations, using recommended practices.

In this part of the series, we’ll look at the benefits of implementing AD FS with a back-end SQL Server (cluster) as opposed to implementing it with Windows Internal Database (WID):

• SAML Artifact Resolution
• Token Replay Detection

Note:
This blogpost assumes you’re running AD FS Servers as domain-joined Windows Server 2016 Server Core installations. The same information applies to AD FS Servers running Windows Server 2016 with Desktop Experience (Full).

Why deploy AD FS with Microsoft SQL?

There are several reasons to deploy Active Directory Federation Services (AD FS) with a Microsoft SQL Server back-end. The organizations for which I’ve deployed AD FS with SQL Server chose to do so mainly because they have a strategy to centralize their Microsoft SQL databases on a highly-available Microsoft SQL cluster. This way, all the information security measures surrounding that data had to be applied only once.

Other benefits of using a back-end Microsoft SQL Server are:

• AD FS admins have read/write access to the database on all AD FS servers, eliminating the situation where only the primary AD FS server has read/write access and, thus, can only be used to manage the AD FS farm.
• AD FS admins are not limited to 30 AD FS servers in their AD FS farm.
• SQL admins can more easily manage the Microsoft SQL Server, since it is not limited in the same way Windows Internal Database is limited.

However, there are two other reasons as well:

Why would you want SAML Artifact Resolution?

In an AD FS implementation using Windows Internal Database to store and replicate the AD FS configuration, when a SAML authentication is performed, the responding claim is sent back.

With Artifact Resolution, instead of the actual claim, a reference is sent back. Based on the reference, the claim can then be retrieved. When used, Artifact Resolution allows for all parties involved to reference the original SAML claim. All access to the references can then be logged and audited to allow for the permitted access, only.

Artifact Resolution is only available when AD FS is implemented with a back-end Microsoft SQL Cluster.

Why would you want Token Replay Detection?

In an AD FS implementation using Windows Internal Database to store and replicate the AD FS configuration, tokens that are retrieved can be reused to an extent. When Replay detection is enabled, AD FS no longer allows for the WS-Federation passive profile and the SAML WebSSO profile.

Getting ready

To implement an AD FS farm with SQL Server, make sure to meet the following requirements:

Information requirements

In the organization, make sure there is consensus on the name for the AD FS farm.
Make sure a TLS certificate is available for the AD FS farm name, or request it from a Certification Authority (CA). Install the certificate in the personal certificate store for the local machine.

System requirements for proposed AD FS Servers

To create an AD FS Farm, make sure a domain-joined Windows Server 2016 installation is available to commission as an AD FS server. Additionally, make sure the AD FS farm name is resolvable to this machine in the appropriate DNS zones.

Make sure these systems are installed with the latest cumulative Windows Updates. At a minimum, make sure the proposed AD FS Servers running Windows Server 2016 have the July 2019 Cumulative update (KB4507459) installed

System requirements for SQL Servers

For AD FS with SQL Server-based databases, have a SQL Server available on the network, that is also resolvable via DNS and reachable by the proposed AD FS server(s).

Make sure the Microsoft SQL Server is configured with a TLS certificate to be able to encrypt the data with the AD FS Servers.  Also make sure the Microsoft SQL Server installation supports TLS 1.2 by installing the required hotfixes as described in KB3135244.

Privilege requirements

Perform all the below steps with an account that has these specific permissions:

1. Domain Administrator privileges in Active Directory, through a direct membership of the Domain Admins group.
2. Local Administrator privileges on the Windows Server installations you intend to use as AD FS servers. (This is a default permission that comes with Domain Admins privileges when the server is domain-joined)
3. SysAdmin (sa) privileges on the Microsoft SQL Server.

How to install the first AD FS Server with Microsoft SQL Server

Setting up an AD FS farm with Microsoft SQL Server and implementing the first AD FS server, consists of the following steps:

1. Creating a gMSA in Active Directory
2. Creating the database script
3. Creating the databases
4. Installing the AD FS Server role
5. Configuring AD FS

Creating a gMSA

We need a group Managed Service Account (gMSA) to be used as the AD FS service account across the AD FS farm. A gMSA is the safest way to host this account from Active Directory.

When using SQL Server opposed to using WID, however, the serviceaccount becomes really important, because it is not only used to run the AD FS service on AD FS servers in the AD FS Farm, but also to connect to the SQL Server backend: The service account specified when using the script from Export-AdfsDeploymentSQLScript, will be added as a login to the SQL Server installation and will be provided with privileges in the database.

Use the following lines of PowerShell on a system with the Active Directory Module for Windows PowerShell installed, while signed in with a user account that is a member of the Domain Admins group, supposing ADFSServer1 is the hostname of the first AD FS server in the farm:

Import-Module ActiveDirectory

New-ADServiceAccount ADFSgMSA -DNSHostName adfsgmsa.domain.tld -PrincipalsAllowedToRetrieveManagedPassword ADFSServer1

Creating the script

On the first proposed AD FS server, install the AD FS Server role with the following line of Windows PowerShell in an elevated window:

Install-WindowsFeature ADFS-Federation -IncludeManagementTools

With the AD FS Server role installed, we can use the specific Windows PowerShell cmdlet to create the SQL scripts to create the databases on the SQL Server Back-end.

Use the following lines of Windows PowerShell to install the AD FS Server role in an elevated window:

New-Item "C:\ADFSSQLScript" -Type Directory

Export-AdfsDeploymentSQLScript -DestinationFolder "C:\ADFSSQLScript" –ServiceAccountName DOMAIN\ADFSgMSA$

This will create two files in the specified folder:

1. CreateDB.sql
2. Set-Permissions.sql

Creating the databases

Copy both files to the Microsoft SQL Server you intend to use as the back-end for the AD FS farm. Perform these steps:

1. Open the SQL Server Management Studio.
2. From the File menu, click the Open option. Then click File (Ctrl+O).
3. Browse to the CreateDB.sql script and open it.
4. From the Query menu, click the Execute (F5) option.
5. From the File menu, click the Open option. Then click File (Ctrl+O) again.
6. This time, browse to the SetPermissions.sql script and open it.
7. From the Query menu, click the Execute (F5) option.

The CreateDB.sql script creates two databases: the AdfsConfigurationV4 database that stores the AD FS Farm settings, and the AdfsArtifactStore database, that is used for AD FS Artifact Resolution.

Note:
Do not configure the database as part of a SQL Server Always-on Availability Group at this moment. Wait with this action, until you’ve successfully configured the first AD FS server, as the AD FS server wants a lock on the database at that time.

Configuring AD FS

On the proposed first AD FS server, perform the following lines of Windows PowerShell  in an elevated window to configure the AD FS Server role with a SQL Server named SQLServer, listening on the default port (TCP 1433):

$ADFSFarmName = "sts.domain.tld"

$Thumb = (Get-ChildItem -path cert:\LocalMachine\My | Where-Object {$_.Subject -match $ADFSFarmName}).Thumbprint

Install-ADFSFarm -CertificateThumbPrint $thumb -FederationServiceDisplayName "Organization Name" -FederationServiceName $ADFSFarmName -GroupServiceAccountIdentifier "DOMAIN\ADFSgMSA$" -OverwriteConfiguration -SQLConnectionString "Data Source=SQLSERVER;IntegratedSecurity=True"

Restart-Computer

In some environments, complex SQL connection strings might be returned by a SQL admin as answer to the AD FS SQL scripts. In these cases, it is wise to test the SQL connection string manually on the AD FS Servers, before implementation. Use the following lines of Windows PowerShell to this purpose:

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Data Source=SQLServerName:Port;Integrated Security=True;"

# If no error occurs here, then connection was successful.

$conn.Open();

$conn.Close();

How to install additional AD FS Servers with Microsoft SQL Server

After setting up the AD FS farm by implementing the first AD FS server, adding additional AD FS servers to the farm is straightforward.

Make sure:

1. The same TLS certificate is available for the additional AD FS servers as used on the first AD FS server in the AD FS farm. Install the certificate in the personal certificate store for the local machine.
2. The proposed AD FS server is a domain-joined Windows Server installation and you are logged on with a domain account that is a member of the Domain Admins group.
3. The Microsoft SQL Server is available.

On the proposed AD FS server, install the AD FS Server role with the following line of Windows PowerShell in an elevated window:

Install-WindowsFeature ADFS-Federation -IncludeManagementTools

Then, in the same PowerShell window, use the following lines of Windows PowerShell, replacing the values for $ADFSFarmName, -GroupServiceAccountIdentifier and -SQLConnectionString in the same way as you did for the first AD FS server:

$ADFSFarmName = "sts.domain.tld"

$Thumb = (Get-ChildItem -path cert:\LocalMachine\My | Where-Object {$_.Subject -match $ADFSFarmName}).Thumbprint

Add-AdfsFarmNode -CertificateThumbPrint $thumb -GroupServiceAccountIdentifier "DOMAIN\ADFSgMSA$" -SQLConnectionString "Data Source=SQLSERVER;IntegratedSecurity=True"

Restart-Computer

Checking for Token Replay Detection

Token replay detection is enabled by default when you deploy AD FS with SQL Server. However, after deploying AD FS with SQL Server, you may want to check if Token Replay Detection is enabled. Run the following line of Windows PowerShell in an elevated window to do so:

(Get-ADFSProperties).PreventTokenReplays

Concluding

SAML Artifact Resolution and Token Replay Detection should be available for hardened Hybrid Identity implementation. To gain access to these features, install Active Directory Federation Services (AD FS) with Microsoft SQL Server as its back-end.

Further reading

The Role of the AD FS Configuration Database
How does Artifact Resolution work?
AD FS Deployment Topology Considerations