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

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.

connection-timeout-expired.png


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.

Open SQL Server Configuration Manager

SQL Server Services


The SQL Server Browser service is not running.
  • Again, Open SQL Server Configuration Manager.

Open SQL Server Configuration Manager

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

SQL Server Browser


The TCP/IP is disabled.
  • Again, Open SQL Server Configuration Manager.

Open SQL Server Configuration Manager

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

SQL Server Netowrk Configuration - TCP IP


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.

Open Command Prompt as Administrator.png

  • Run the following Command.

CLICONFG.EXE

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

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

Open Command Prompt as Administrator.png

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

Ping

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


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.Open SQL Server Configuration Manager
  • Navigate to SQL Server Network Configuration.
  • Right click on TCP/IP > Properties > IP Address.
  • Scroll down to IPALL > Check TCP Port.

TCP IP Properies Port

To get the TCP Port number via T-SQL. 
  • Open SQL Server Management Studio.

Connect to SQL Server

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

TCP Port with SQL

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

telnet 1433.png

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.

Telnet Connected.png

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

Telnet not Connected.png

To open port on SQL Server.
  • From SQL Server > Open Windows Firewall.

Windows Firewall.png

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

New Inbound Rule

  • In Rule Type Step > Check Port.

New Inbound Rule with Port.png

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

New Inbound Rule with Port 1.png


The client and server are not configured to use the same network protocol.
  • Open  Network and sharing center on SQL Server.

Network and Sharing Center.png

  • Change Adapter Settings.

Change Adapter Settings.png

  • Right Click on Ethernet > Properties.

Ethernet Properties 1

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

Ethernet Properties


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 
Advertisements

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

  1. Hi Mohammed,

    When I tried to connect the DB from SSMS from my application server I am getting the same error as mentioned in the post, but if I try it for the 2nd time I am able to login successfully without any problem.

    SQL is installed in different server and I am trying to connect it from different server using SSMS.

    any help will be really appreciated.

    Regards
    Anand

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s