AD FS Troubleshooting - SQL Connectivity

AD FS provides the ability to use remote SQL Server's for the AD FS farm data. You will see issues if the AD FS servers in your farm cannot communicate with the backend SQL servers. The following document will provide some basic steps to testing the communication with the backend servers

Acquire the SQL database connection string

The first thing to test when checking SQL connectivity is, if AD FS has the correct SQL connection information. This can be done using PowerShell.

To acquire the SQL connection string

Open Windows PowerShell
Enter the following: $adfs = gwmi -Namespace root/ADFS -Class SecurityTokenService and hit Enter
Enter the following: $adfs.ConfigurationDatabaseConnectionString and hit enter.
You should see the connect string information.

Create a Universal Data Link (UDL) file to test connectivity

A Universal Data Link file or UDL file is basically a text file that contains the a database connection string. By using the information we obtained above we can test whether or not the SQL server is responding to connections.

To create a test.udl file to test connectivity

Open Notepad and save the file as test.udl. Make sure that you have All Files selected from the drop-down for Save as type.
Double-click on test.udl
Fill in the following information: a. Select or enter a server name: Use the Data Source from the connection string above b. Enter information to log on to the server: Use the AD FS service account or an account that has permissions to logon remotely. If the account is a windows account use integrated authentication otherwise enter the username and password. c. Select the database on the server: Use the Initial Catalog from the string above.

Example: AdfsConfigurationV3.

4.Click Test Connection.

Use SQL Server Management Studio to test connectivity

You can also download and install SSMS to test database connectivity.

To test connectivity with SSMS

Download and install SQL Server Management Studio.

2.Open SSMS, enter the Server Name. The data source from above.

3 .Use the AD FS service account or an account that has permissions to logon remotely. If the account is a windows account use integrated authentication otherwise enter the username and password.