Lesson Learned #268:Error occurred during the pre-login handshake-The semaphore timeout period

[Guest Jose_Manuel_Jurado]

In many situations, we used to have service request with the error message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121).

 

 

 

For example, in this service request our customer got the following error message using SQL Server Management Studio:

 

 

 

Answer: TITLE: Microsoft SQL Server Management Studio

------------------------------

 

Error connecting to 'servername.database.windows.net'.

 

------------------------------

ADDITIONAL INFORMATION:

 

Failed to connect to server servername.database.windows.net. (Microsoft.SqlServer.ConnectionInfo)

 

------------------------------

 

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error: 121)

 

For help, click:

https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-121-database-engine-error

 

------------------------------

 

The semaphore timeout period has expired

 

------------------------------

 

 

 

In this situation, our first recomendation is to review our article Lesson Learned #212: Hands-On-Labs:TCP Provider: The semaphore timeout period has expired - Microsoft Community Hub

 

 

 

After reviewed the situation, we found that the communication were reaching the Azure SQL Managed Instance but the outbound traffic was not being delivered to the Virtual Machine to complete the handshake. Once we added an entry to the Azure SQL Managed Instance route table, allowed the traffic to return to the Virtual Machine, the issue was solved.

 

 

 

Enjoy!

 

Continue reading...