AD FS troubleshooting: SQL connectivity

With Active Directory Federation Services (AD FS), you can use remote SQL servers for AD FS farm data. You see issues if the AD FS servers in your farm can't communicate with the back-end SQL servers. This article provides basic steps to test the communication with the back-end servers.

Acquire the SQL database connection string

The first thing to test when you check SQL connectivity is if AD FS has the correct SQL connection information. Use PowerShell for this task.

1. Open Windows PowerShell.
2. Enter $adfs = gwmi -Namespace root/ADFS -Class SecurityTokenService, and then select the Enter key.
3. Enter $adfs.ConfigurationDatabaseConnectionString, and then select the Enter key.

The connection string information appears.

Create a UDL file to test connectivity

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

1. Open Notepad and save the file as test.udl. In the Save as type dropdown list, select All Files.

2. Double-click test.udl.

3. Fill in the following information:

   1. Select or enter a server name: Use the data source from the preceding connection string.
   2. Enter information to log on to the server: Use the AD FS service account or an account that has permissions to sign in remotely. If the account is a Windows account, use Integrated Windows Authentication. Otherwise, enter the username and password.
   3. Select the database on the server: Use the Initial Catalog from the preceding string. An example is AdfsConfigurationV3.

   

4. Select Test Connection.

   

Use SQL Server Management Studio to test connectivity

You can also download and install SQL Server Management Studio (SSMS) to test database connectivity.

1. Download and install SSMS.

   

2. Open SSMS and enter the server name from the preceding data source.

3. Use the AD FS service account or an account that has permissions to sign in remotely. If the account is a Windows account, use Integrated Windows Authentication. Otherwise, enter the username and password.

   

4. After the left pane populates, expand databases and verify that you can see the AD FS databases.

   

Related content

• AD FS troubleshooting