Configure ADFS 4.0 using SQL 2016 step by step

By Ashok M

Today, we are going to see the step by step configuration of ADFS 4.0 on windows server 2016 using the SQL 2016. With this approach, high availability can be achieved at both ADFS and SQL databases. More servers to be added to the ADFS Farm and SQL Always On can be configured for the ADFS databases.

Pre-Requisites:

  • Administrator account for the installation and configuration of ADFS
  • Service Account for ADFS with “Log on as a service”, “Generate security audits permissions” on the local machine security policy or through the Active directory Group Policy (AD GPO)
    • Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment
  • Certificate for ADFS: Either SAN certificate with ADFS Farm name or the wildcard certificate
  • Recommended to have the public certificate when publishing the ADFS
  • Sysadmin permissions on the SQL server for the administrator account used to install the ADFS

Installing and configuring the ADFS from server manager

Once the configuration is completed, restart the ADFS server. Connect to the SQL instance using the SSMS and 2 databases will be created,

Check the ADFS Properties for the DB Connection String,

Enabled the IDPInitiatedSignon using the command, Set-AdfsProperties -EnableIdpInitiatedSignonPage $true

Login to https://adfs.cloudexchangers.com/adfs/ls/idpinitiatedsignon.aspx

Note: Make sure to create the DNS record for ADFS and check the SPN’s registered incase if there are any issues with the default sign in page.

You can now configure the SQL AlwaysOn Availability Group and add the ADFS databases. Once it is configured, listener name needs to be updated in the DB Connection string in the ADFS properties. It can be done using the below commands,

Artifact DB Connection String:

Set-AdfsProperties –artifactdbconnection “Data source=SQLDBListener.cloudexchangers.com;Initial Catalog=AdfsArtifactStore;Integrated Security=True”

Configuration DB Connection string:

net stop adfssrv

$temp= Get-WmiObject -namespace root/ADFS -class SecurityTokenService

$temp.ConfigurationdatabaseConnectionstring=”data source=SQLDBListener.cloudexchangers.com; initial catalog=AdfsConfigurationV3;integrated security=true”

$temp.put()

net start adfssrv

Note: When adding more servers to the ADFS farm, then the nodes needs to have the network connectivity to the SQL server and the required firewall ports needs to be allowed. By default, it uses 1433. Configuration database connection string needs to be updated on each nodes.

Command to get the DB connection strings,

$adfs = gwi -Namespace root/ADFS -Class SecurityTokenService

$adfs.ConfigurationDatabaseConnectionString

Get-ADFSProperties | select *DB*

References:

https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/design/federation-server-farm-using-sql-server

https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/overview/ad-fs-requirements

Happy learning!! 🙂

About Ashok M

Microsoft Certified Professional with key technical skills including Microsoft Exchange, Windows Server, Microsoft Azure, Office 365, Intune, EMS, Skype for Business, Active Directory, ADFS and has got more exposure to Hyper V, System Center Configuration Manager, Virtualization, Video conferencing room systems, SQL. Have experience in design, implementation, migration & support for various Microsoft infrastructure products. Currently working as "Implementation Engineer" with the UAE's first tier IV Data Center design certification in the region.

Related articles

Leave a Reply