Why can’t I connect to my SQL Azure database?

We’ve been running our Azure Hands On Experience labs throughout the US Central Region for the last month or so and the SQL Azure exercises require the user to connect a newly created SQL Azure database from their development workstation.

Inevitably, at least one machine is configured with one or more settings that prevent a connection from succeeding, either from SQL Server Management Studio or Visual Studio’s Data Connections tree in Server Explorer (my preferred method btw).

Here are the troubleshooting tips we’ve been using in the labs:

1. Double check your software versions.

If you’re using SQL Server Management Studio, it needs to be SQL Server Management Studio 2008 R2 (the free express or full edition). If you go to the Help | About menu, the dialog will say R2 in the logo product logo.

If you want to connect from directly inside Visual Studio 2010, make sure you have Visual Studio 2010 Service Pack SP1 installed as well. If you go to the Help | About menu item in Visual Studio 2010, the popup dialog will say SP1 immediately after the product version number. Also, make sure you have the latest Windows Azure SDK installed. It will set up the hooks for azure database connectivity from within Visual Studio 2010.

2. Double check your SQL Server Native Client Configuration

If you’re getting an error that mentions named pipes it means the SQL client networking protocol settings on your development machine are set to connect to servers using the named pipes protocol by default. This won’t work with SQL Azure because named pipes is a network protocol optimized for local LAN traffic. All connections to SQL Azure are done using TCP/IP so you need TCP/IP enabled.

To do this, run SQL Server Configuration Manager go to your start menu and select All Programs | SQL Server 2008 R2 | Configuration Tools | SQL Server Configuration Manager.

You need to make sure the TCP/IP protocol is enabled under the SQL Native Client 10.0 Configuration tree item.  (On 64 bit machines you might have both 32 bit and 64 bit tree branches – be sure to enable TCP/IP in both). It’s generally a good ide to set TCP/IP as a higher priority protocol over named pipes as TCP/IP is the most common network protocol for SQL severs these days. This msdn article has more details about the configuration options.

3. Check your client side firewall rules.

SQL Servers communicate over TCP/IP through port 1433. The Windows Firewall software that shipped with XP SP2, Vista and Windows 7 is pretty good about about asking you when a new port is about to be used and letting you choose if you want it opened. If you're using another client side firewall solution, be sure to enable port 1433 for outbound connections. If your firewall software is set on an application by application basis, set up a rule for SQL Server Management Studio or Visual Studio appropriately.

4. Check the SQL Azure server firewall rules

When you create your SQL Azure database, you were asked to set up firewall rules to allow outside connections to the server. When you add a rule the dialog box lets you to set a range of IP addresses. It also shows your current IP address. In most cases your current IP address is chosen from a bank of addresses on the network you’re connected to.

To check these settings, log into your windows azure account, navigate to the databases area, and choose the database you’re trying to connect to from the tree on the left side of the screen. You should see the dropdown option to view and edit the Firewall Rules for your database server. Add or edit a rule to enable access for the development machine you’re trying to connect with.

As a minimum, you should add your current IP address as it is shown at the bottom. The hands on labs recommend adding a large enough address range so that if you have to reboot and end up acquiring a slightly different address you’ll still be able to connect with having to add another rule. You cans that I did this here.

Note: The address shown for Your current IP address is the public address that the server sees your internet traffic coming from. In the big chain of firewalls between you and your SQL Azure database, this is the address as it will be recognized from the network you currently reside in. It may or may not match the actual IP address your individual machine says it’s using. That’s OK. What it’s seeing is the most public internet facing firewall that you’re going through to get to the database. That’s all the server needs to know to allow you in.

5. Check your corporate firewall rules

If you still can’t connect and all of the above items check out and you’re currently connected to corporate network, chances are your corporate IT folks have another firewall in place to keep the baddies out and to keep you from accidentally letting them in.

If you’ve got an alternative method to connect to the internet such as a wireless hotspot or a guest network, try connecting to it and see if your attempt to reach your SQL Azure server succeeds. If so, then you know there’s a corporate firewall blocking you.

You’ll have to check with your network admins to see if they allow outbound TCP/IP connections on port 1433. Chances are you’ll have to do some paperwork to get that set up.

These are the troubleshooting steps we’ve found fruitful during labs. If you’re still stuck beyond this point, get a colleague to try connecting to your server from their machine. If they succeed, then you know there’s a configuration difference. It’s just a matter of finding the difference.