Lesson Learne #390:The timeout period elapsed while attempting to consume the pre-login handshake

[Jose_Manuel_Jurado]

Today, we got a scenario with the following error message: "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.  The duration spent while attempting to connect to this server was – [Pre-Login] initialization=646; handshake=29359;  (.Net SqlClient Data Provider)" 
Following I would like to share with you my lessons learned about it. 

Understanding the "Connection Timeout Expired"

Error: The "Connection Timeout Expired" error occurs when the client application fails to receive a response from the Azure SQL Database during the pre-login handshake phase. This phase involves the exchange of information and negotiation of settings between the client and the server before establishing a secure connection. The error message suggests that either the pre-login handshake failed or the server was unable to respond within the specified timeout period.

Decoding the Error Message:

To gain deeper insights into the error, let's dissect the error message itself: "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. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=646; handshake=29359; (.Net SqlClient Data Provider)."

• "Connection Timeout Expired" indicates that the client application was unable to receive a response from the Azure SQL Database within the specified timeout period.
• "The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement" implies that the timeout occurred during the phase where the client expects to receive a response from the server to acknowledge the pre-login handshake.
• "This could be because the pre-login handshake failed or the server was unable to respond back in time" suggests two potential causes for the error: a failed pre-login handshake or a delay in the server's response.
• "[Pre-Login] initialization=646; handshake=29359" provides specific duration values, in milliseconds, for the time spent on each step of the pre-login handshake process. The "initialization" time refers to the client-side preparation, while "handshake" time represents the server-side processing.

 

Possible Causes and Solutions:

1. Pre-login handshake failure:

   • Verify the client and server compatibility by ensuring they support the same versions and protocols.
   • Check authentication settings to ensure proper configuration and credentials.
   • Review encryption settings and ensure both client and server agree on the encryption method.
   • Inspect firewall settings to ensure they allow the client to communicate with the Azure SQL Database.

2. Server unresponsiveness:

   • Check network connectivity between the client and the Azure SQL Database.
   • Monitor the performance of the Azure SQL Database to identify potential bottlenecks or resource limitations.
   • Consider adjusting the connection timeout settings to allow for longer waiting periods.

Resolution

In this service request we found that the client application is trying to reach the Azure SQL Server but a firewall that is in the middle is preventing the access to this IP. When establishing a connection to Azure SQL Database, the client connects through a gateway that acts as a proxy between the client and the actual database server. The gateway IP addresses can change dynamically based on Azure infrastructure updates. Whitelisting the gateway IP address subnets ensures that the firewall allows connections from all the potential IP addresses used by the Azure SQL Database service.

Sometimes, we could get other issues, like waiting for PreLogin response, SNI Timeout:

The error message "SNI timeout detected" indicates that the client application was waiting for a response during the PreLogin phase of the connection establishment but did not receive it within the specified timeout period.

SNI stands for Server Name Indication, which is an extension to the Transport Layer Security (TLS) protocol. SNI allows the client to indicate the hostname it is attempting to connect to during the TLS handshake, allowing the server to present the appropriate SSL certificate.

The SNI timeout occurs when the client application does not receive the expected response during the PreLogin phase, which is the initial step in the connection process where the client and server exchange information and negotiate settings.

A SNI timeout can be caused by various factors, including network connectivity issues, firewall restrictions, server unavailability or overload, or misconfiguration of the client or server settings.

Tools

During the troubleshooting we might use the following tools to determine the issue beside the logs of the firewall at client level. 

1. Wireshark: Wireshark is a widely used open-source packet capture and analysis tool. It allows you to capture network packets and analyze the traffic, including the TDS protocol. You can examine the captured packets to verify the port and analyze the TDS communication.

2. Microsoft Message Analyzer: Microsoft Message Analyzer is a network traffic capture and analysis tool provided by Microsoft. It offers advanced features for filtering, examining, and analyzing network traffic, including the TDS protocol. You can use it to capture and analyze the traffic to verify the port and analyze the TDS communication.

3. Telnet: Telnet is a command-line tool available on most operating systems, including Windows. You can use Telnet to establish a basic TCP connection to the Azure SQL Database's IP address and port. By attempting a Telnet connection, you can verify if the port is accessible and responsive.

Enjoy!