Lesson Learned #268:Error occurred during the pre-login handshake-The semaphore timeout period
Published Jan 04 2023 10:10 AM 1,772 Views

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

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

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

 

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.

 

In other situation with Azure SQL Database if our customer is connecting within Azure or has a redirect connection, check if outbound connections 11000-12000 is allowed, if not, change the connection policy to Proxy or use private endpoint.

 

Enjoy!

Version history
Last update:
‎Jan 31 2023 05:42 AM
Updated by: