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

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

But I have a strange behavior 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; 

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

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

I got the following error.

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

connection-timeout-expired.png

I made sure that the application pool service account has elevated privilege with SQL Server as public server role and user mapping database. but unfortunately, the issue still persist.

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:

Regarding: (1) An instance of the SQL Server Database Engine is not running.

On each node, you should ensure that each SQL Server Database Engine is running by following the mentioned steps below:

  • Open SQL Server Configuration Manager.

Open SQL Server Configuration Manager

SQL Server Services

Regarding: (2) 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

Regarding: (3) 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

Regarding: (4) 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

Finally, you should ensure that you provide the correct SQL Server name to avoid this issue.

Regarding: (5) There are network problems.

  • From Application server, try to open CMD as Administrator.

Open Command Prompt as Administrator.png

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

Ping

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

Regarding: (6) 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

  • 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
  • The firewall is not blocked inbound port 1433.
  • 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

Regarding: (7) 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

Enjoy 🙂

See also, How to Check SQL Server Instance Connectivity without using SQL Server Management Studio.

Advertisements

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s