Error connecting to Sql Azure: TCP Provider: No such host is known

Question

I am trying to connect to SQL Azure using OLEdb. When i do, i get the error:

• Source: Microsoft SQL Server Native Client 10.0
• Description: TCP Provider: No such host is known.
• SQLState: 08001
• NativeError: 11001

I can connect using:

• SQL Server Management Studio, and
• Red-Gate SQL Compare

so it's not like there's a firewall, server, or credential problem.

But i cannot connect myself from a native application using the OLEdb provider.

---

First i try a completely wrong username, and no password

Provider=SQLNCLI10;Data Source=tcp:hyperion.database.windows.net,1433;Initial Catalog=Scratch;User ID=adlfk24lksdfj@hyperion;

Gives the (expeccted) error:

Source: Microsoft SQL Server Native Client 10.0
Description: Login failed for user 'adlfk24lksdfj'.
SQLState: 28000
NativeError: 18456

Then i give it a valid username, but no password:

Provider=SQLNCLI10;Data Source=tcp:hyperion.database.windows.net,1433;Initial Catalog=Scratch;User ID=iboyd@hyperion;

That returns the (expected) error:

Source: Microsoft SQL Server Native Client 10.0
Description: Login failed for user 'iboyd'.
SQLState: 28000
NativeError: 18456

Then i give an invalid password:

Provider=SQLNCLI10;Data Source=tcp:hyperion.database.windows.net,1433;Initial Catalog=Scratch;User ID=iboyd@hyperion;Password=trubadour;

That returns the expected error:

Source: Microsoft SQL Server Native Client 10.0
Description: Login failed for user 'iboyd'.
SQLState: 28000
NativeError: 18456

And then the valid connection string

Then i give it the correct¹ username and password:

Provider=SQLNCLI10;Data Source=tcp:hyperion.database.windows.net,1433;Initial Catalog=Scratch;User ID=iboyd@hyperion;Password=correcthorsebatterystaple;

And i get the unexpected, nonsensical error, after a 15 second pause:

Source: Microsoft SQL Server Native Client 10.0
Description: TCP Provider: No such host is known.
SQLState: 08001
NativeError: 11001

¹ Of course those are not my actual credentials. But is there any free test SQL Azure database out there i can test connecting to?

Bonus Reading

• ConnectionStrings.com: Sql Azure
• Why doesn't Microsoft support OLE DB connections to SQL Azure?
• Microsoft Azure Technical Evangalist: You can use it, however it has not been thoroughly tested for all cases. Essentially, it should work for most things, but there might be a few edge cases where it won't work. Until we document those cases, it remains unsupported. That being said, if you were to use and run into errors, we would love to know about it and prioritize that to be fixed.
• Azure connection string exception "network-related orinstance-specific error occurred"

Answer 1

The answer is as horrifying as it is terrible.

I was using:

hyperion.database.windows.com