Sporadic Azure SQL Semaphore Timeout/Handshake error when connecting as login, but login@servername seems to work?

Question

I am having issues reliably connecting to Azure SQL Databases from on-prem. (so yes, firewalls involved). When I connect (SSMS or invoke-sqlcmd) using just mylogin, sometimes it works but frequently I'll get a message like:

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 0 [Pre-Login] initialization=67;handshake=14948;

However, I've noticed that if I connect as mylogin@the-server-name (not the FQDN, just the part of the servername we can choose) then it seems to be a lot more reliable.

Is this something I can do something about? Firewall weirdness? Any help appreciated. Looking in the ring buffer ( SELECT CAST(record as xml) as record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY') I have records, but I'm not seeing anything about failed connections, just killed connections. (and totallogintimeinmilliseconds is null)

Answer 1

This indicates a database connectivity issue involving the security handshake. It appears to be a problem with IPV4 and IPV6. Some connections appear to try to utilize IPV4, while others use IPV6 (the behavior appears to be random), causing problems if one of the protocols is not enabled. This is a common SQL server problem.

The solution is to enable both protocols as follows:

1. Open the SQL Server Configuration Manager application.
2. Access the SQL Server Network Configuration node.
3. MYSQLINSTANCE Protocols (Left-click) (where MYSQLINSTANCE is the name of the SQL server used).
4. Right-click TCP/IP in the right-hand window.
5. Then select Properties.
6. The IP Addresses tab should be selected.
7. Make sure that both Active and Enabled are set to Yes for each IP address provided.