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!
