Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment

In this article, we’ll explain How to trace and solve the “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment” 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.

We will also concentrate on solving “Connection Timeout Expired pre-login handshake SQL Server” error by checking the following:

---

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake

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
  • The public server role and the user is mapping the correct database.

But unfortunately, I’m still getting “Connection Timeout Expired pre-login handshake SQL Server” error!

---

Why I get “Connection Timeout Expired pre-login handshake SQL Server”?

The “Connection Timeout Expired pre-login handshake SQL Server” 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.

---

How to solve “Connection Timeout Expired pre-login handshake SQL Server”?

In this section, we’re gonna explain the above root causes of “Connection Timeout Expired pre-login handshake SQL Server” 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! Please, 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.

---

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

Get SQL Alias Name in Windows Server

Steps

• 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 or (SQL Alias) to avoid this issue.

---

Check Network Connectivity

Of course, you will get “Connection Timeout Expired pre-login handshake SQL Server” if there is any issue related to network settings.

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

• Perform telnet cmdlet with the TCP/IP port (The default port is 1433) to make sure the port is opened and reachable.

telnet SQL Server Virtual IP Opened Port

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

• Again, perform a “Ping” command with the Database Server IP for each node and for SQL VIP to ensure that the application server can reach the database server and vice versa.

ping VIP

You might also like to check the detail steps at How to Check SQL Server Instance Connectivity from the application server to database server for a specific user

---

Use UDL file to test the SQL Server instance accessibility

It doesn’t 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 check SQL server instance connectivity from the application server to the database server.

---

Check If TCP/IP port for the Database Engine instance is blocked by Firewall

The default port for TCP/IP is 1433. so that you should make sure that The Database Engine is listening on the assigned TCP/IP port that is by default 1433.

Get SQL Server TCP/IP port number

Get SQL Server TCP/IP port number using SQL Management Studio

• 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.

Get SQL Server TCP/IP port number using 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 now to perform telnet the SQL Server VIP and its TCP/IP port.

Note: if the telnet command is not recognized, you should install the 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 below result.

• Otherwise, you will get the below message that means the port is blocked.

In this case, you must make sure that the SQL Server TCP/IP port is allowed by

• Firewall.
• Antivirus.

Allow SQL Server TCP/IP Port in Firewall

• In database 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.

Note: if you have an antivirus engine running on your server, so you should also make sure that the port is allowed.

---

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, we have tried to explain the common root causes for “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” and providing the proposed solutions for each root cause in details.

Applies To

• SQL Server 2017.
• SQL Server 2014.
• SQL Server 2012.

You might also like to read

• SQL Server Best Practices for SharePoint 2019.
• 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.

Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.