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.


  • 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

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;Initial Catalog=AdfsArtifactStore;Integrated Security=True”

Configuration DB Connection string:

net stop adfssrv

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

$temp.ConfigurationdatabaseConnectionstring=”data; initial catalog=AdfsConfigurationV3;integrated security=true”


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


Get-ADFSProperties | select *DB*


Happy learning!! 🙂

By Ashok M

A technology enthusiast with 9+ years of experience in Planning, Designing, Implementation, Migration and Operations of various Microsoft Infrastructure & Cloud Services. Extensive knowledge of Cloud Computing, Microsoft Messaging & Collaboration, Digital Transformation, IT Services & Emerging technologies. • One of the Authors of the book – “Reimagine Remote Working with Microsoft Teams : A practical guide to increasing your productivity and enhancing collaboration in the remote world” - • Blogger at CloudExchangers - • Microsoft Community Contributor in Microsoft Q&A - • Microsoft Certified Professional in MS Azure, Microsoft365, MS Teams and Skype for Business