Blog
Solved: SQL Azure - Client unable to establish connection because an error was encountered during handshakes before login
This one has taken a few hours without any sign of anything useful in search engines, and in the end boiled down to a simple missing outbound firewall rule.
Problem
Trying to connect to SQL Azure using SQL Server Native Client with OLEDB via MDAC* and getting the following error when calling .Open on an ADODB.Connection object:
Number: -2147467259 (bog standard connection failed error number)
Description: Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
As the error came back almost immediately, this suggested an "unsupported version of SQL Server" was causing the issue (as the other "common causes" didn't fit) and so hope of migrating this app to SQL Azure was fading.
Having spoken with Microsoft who assured me it should work, hope returned and I tried the connection from different computers... and it worked with Provider set to either SQLNCLI10 or SQLNCLI11 (depending on what was already available on the computer).
Solution
Simple really - just make sure that outbound traffic is allowed on port 1433 (at least to your SQL Azure database server).
The strange bit is that you don't get a long wait followed by a "SQL Server does not exist or access denied" error when the SQL Server Native Client traffic is blocked. The error message comes back almost immediately suggesting some communication is happening in the background before the handshake error appears.
For reference, the minimum Connection String that worked (with either Provider) was this, but adding Encrypt, TrustServerCertificate, MARS Connection, etc also worked fine:
Data Source=tcp:{server_name}.database.windows.net;
Initial Catalog={database_name};
User ID={user_name}@{server_name};
Password={password}
Microsoft do recommend including DataTypeCompatibility=80 though.
* Yes, this is some legacy VB6 and ASP code that I wanted to see if I could make work with a database migrated to SQL Azure!
By Theo Gray on January 29, 2016 | Permalink | Comment
RSS feed
Reader Comments
Skip to form
There are currently no comments about this article.