In this article, I will explain How to trace and solve the below error

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment.
This could be because the pre-login handshake failed or the server was unable to respond back in time.

---

Scenario

I have an ASP.Net website that connects to SQL server always-on failover cluster instance.

When I configured the Connection String inside Web.Config as SQL Authentication, the connection has been connected successfully.

Server=load balancer IP;Database=Database Name;User Id=User; Password=Password;

But when I tried to configure the Connection String as Windows Authentication.

Server=load balancer IP;Database=Database Name;Integrated Security =true;

I got the below error.

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment.
This could be because the pre-login handshake failed or the server was unable to respond back in time.

By the way,

• The application pool service account has elevated privilege with SQL Server as
  • Public server role and the user is mapping the correct database.

But unfortunately, the issue still persists!

---

Cause:

The “Connection Timeout expired” error commonly occurs in the following cases:

1. An instance of the SQL Server Database Engine is not running.
2. The SQL Server Browser service is not running.
3. The TCP/IP is disabled.
4. The server name was typed incorrectly.
5. There are network problems.
6. The TCP/IP port for the Database Engine instance is blocked by a firewall.
7. The client and server are not configured to use the same network protocol.

---

Solution:

In this section, I will explain the above causes one by one in more details.

An instance of the SQL Server Database Engine is not running.

On each node, you should ensure that each SQL Server Database Engine is started and running properly by doing the following:

• Open SQL Server Configuration Manager.

• Make Sure that the SQL Server Services state is running.
  • In case, it is not running try to start it.
  • In case. it’s not started check SQL Server Service was unable to start.

---

The SQL Server Browser service is not running.

• Again, Open SQL Server Configuration Manager.

• Make sure that The SQL Server Browser service is running.

---

The TCP/IP is disabled.

• Again, Open SQL Server Configuration Manager.

• From right side, Navigate to SQL Server Configuration Network.
• Make sure that TCP/IP is enabled.

---

The server name was typed incorrectly

In my case, the problem was in the server name where I have used the Load Balancer IP that should be the SQL Alias name when using Windows Authentication. so that the connection string should be like this

Server=SQL Alias Name;Database=Database Name;Integrated Security =true

To get the SQL Alias name:

• Run Command Prompt as Administrator.

• Run the following Command.

CLICONFG.EXE

• The following Dialog should be shown > Click on Alias tab > Get the Server Alias.

In the end, you should make sure that you have provided the correct SQL Server name to avoid this issue.

There are network problems

• At the Application server, try to open the CMD as Administrator.

• Run “Ping” command with the Database Server IP for each node and for load balancer IP to ensure that the application server can reach to the database server and vice versa.

• Try to repeat the same “Ping” command from the Database server to the application server IP.

---

Use UDL file, to test the SQL Server instance access

Not mean you can reach the database server, you have access to the SQL server instance! So:

• Try to login with The application pool account that runs the website on the IIS to the application server.
• Then try to create a UDL file as mentioned at How to check SQL server instance connectivity from the application server to database server for a specific user without using SQL server management studio.

---

The TCP/IP port for the Database Engine instance is blocked by a firewall

The default port for TCP/IP is 1433 so you should make sure that The Database Engine is listening on port 1433 and it has not been changed,

To get the TCP port number via SQL Configuration Wizard.

• Open SQL Server Configuration Wizard.
• Navigate to SQL Server Network Configuration.
• Right click on TCP/IP > Properties > IP Address.
• Scroll down to IPALL > Check TCP Port.

To get the TCP Port number via T-SQL. 

• Open SQL Server Management Studio.

• Use Master Database > Click on New Query.
• Run the following Query.

SELECT DISTINCT local_tcp_portFROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL

• The output should be retrieved as shown below.

• Try to telnet the SQL Server IP to its TCP port.

Note: if the telnet command is not recognized, you should install telnet feature as mentioned at ‘telnet’ is not recognized as an internal or external command, operable program or batch file.

• If the port is not blocked, you should get the following.

• Else that the port is blocked and you will need to open it.

To open port on SQL Server.

• From SQL Server > Open Windows Firewall.

• From right side > Select Inbound Rules.
• Right click on  Inbound Rules > Select New Inbound Rule.

• In Rule Type Step > Check Port.

• On Protocol and Port Step > Select TCP, Special Local Ports > Type the SQL Port.

---

The client and server are not configured to use the same network protocol.

• Open  Network and sharing center on SQL Server.

• Change Adapter Settings.

• Right Click on Ethernet > Properties.

• If IPv6 has been checked, so It should be checked on the application server.
• If IPv6 has not been checked, so It should be unchecked on the application server.

---

Conclusion

In this article, I have tried to solve the below issue

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment.
This could be because the pre-login handshake failed or the server was unable to respond back in time.

See also 

• How to Check SQL Server Instance Connectivity without using SQL Server Management Studio.
• ‘telnet’ is not recognized as an internal or external command, operable program or batch file.
• SQL Server Service was unable to start.