ADFS health check for connection between ADFS and SQL Database

Question

Problem summary: HTTP probes towards ADFS & WAP is not enough if the ADFS service is still running but the connection between ADFS and SQL database is dead.

ADFS Environment:

ADFS Environment

Using HTTP probes in Environment: ADFS environment with HTTP Probes

HTTP probes: The normal way of having health checks towards the ADFS environment, is to setup HTTP probes that runs HTTP checks towards each WAP & ADFS server URL or IP. They run health checks over HTTP port 80. Gets a 200 (OK) returned. The response to these probe endpoints is an HTTP 200 OK and is only checking the server/service locally, with no dependence on back-end services(SQL cluster\Database)

Conclusion: Using HTTP probes towards ADFS & WAP servers is not enough

Problem description: The HTTP port is going directly to the WAP and ADFS servers respectively. This means that they only check if the servers & services themselves are OK. There's a known problem where the connection between the ADFS backend and the SQL server dies for 2-3 minutes. During this time, the ADFS backend server times out, if you're unlucky. The problem here is when the ADFS backend server times out, the ADFS service itself is still running.(so as far as the HTTP probe is concerned the ADFS is still upp and running.) The HTTP probe is signalling that the ADFS service is OK. So the load balancer is till sending end users to the ADFS service that has a dead connection towards the SQL database because its service is still running. End-users ends up getting error during authentication.

Question: How can I setup a proper health check between ADFS --> SQL cluster/database? So that you can see that communication between ADFS --> SQL does not work as intended. As in the case when the service on the ADFS servers are still running, but the database connection between ADFS and SQL database is dead. I would want that health check to be used for monitoring as a first stop. Secondary, you could build some recovery steps that could be executed thanks to this health check.

Answer 1

• You can create an ‘UDL’ file and mention the required database connection details in it along with the username and password of the account used to access the SQL Server DB instance mentioned in ADFS server. Please find the steps below to create an UDL file: -

1. Create a text file and change its extension to ‘.udl’ and open it.

2. In the providers tab, select the ‘Microsoft OLE DB Provider for SQL Server’ and on the connection tab, enter the server’s name, username and password, also check the box which states ‘allow saving password’.

3. Then select the database from the dropdown when initial connection for database existence is done through the file and hit the test connection button.

4. You will get a proper result whether the connection from that server is successful or not.

5. You can also specify the connection timeout in seconds along with the permissions to that database to be selected. Also, in the ‘All’ tab, you can select and set various parameters regarding this database connection check including saving the security information used for testing the DB connection.

6. Once done, you can then set a task scheduler through a script to be run periodically and check the status of the connection through this file by the following command: -

   'Get-content C:\UDLs\Test.udl' --> after running this file

In this way, you can probe whether the connection between your ADFS and SQL servers is intact or not as a first stop towards health check.

Please refer the screenshots below for reference: -

https://learn.microsoft.com/en-us/sql/connect/oledb/help-topics/data-link-pages?view=sql-server-ver15