There is a learning process to switching to SQL Azure from your local SQL Server database, and this was a painful but learning experience for me and this post will hopefully help you to get on that road.

Let’s assume you have created a SQL Azure database, which is pretty straight-forward, and want to now connect to it from a client.

Here’s what you will need…

Tools

You can connect to SQL Azure from within SQL Manager provided you have the SQL Server 2008 R2 edition. If you do not, you can connect a query I think from some posts I read but I’m not sure, so remember to upgrade to the R2 edition if possible, or you can  download the free R2 express edition.

Network settings

To connect to Azure, you will need changes at the server and the client.

At the server side in Azure, you will need to open the firewall to allow an IP range to allow connections from. Azure makes this very easy if configured from the client machine because when you Add new rule, it will display your IP and that makes it easier to specify a range.

In the end, you might end up with a few rules like this:

sql azure firewall

The client side could end up being one of the most frustrating aspects of using Azure. You need to have port 1433 to connect to SQL Azure. Period. There is no workaround that whatsoever. If you’re in a corporate network, this might be an issue. If you’re on a wireless network, this might be an issue because many public ones only have port 80 and a few others open.

You might get the following error if your port is not accessible.

sql azure error1

You might have to work with your corporate IT or ISP to open port 1433.

To check if your SQL Azure database is accessible from your machine, telnet into it. Here’s how: Depending on your Windows installation, you might have to install telnet. Go to Control Panel – Programs and Features. Select IIS and expand that node and check Telnet Client and install it. Go to a DOS prompt and type in telnet, and you will get a telnet prompt. To attempt to connect to your SQL Azure db, enter o xyz.database.windows.net 1433 and you should get a connecting prompt.
Something like this:

sql azure telnet

Otherwise, you’ll get a meaningful error message that you could troubleshoot.
When done, enter q to quit telnet.

Once you’re able to connect to your db in the cloud, it’s time to use it in your application.

Connection strings

To point your app to the SQL Azure db, it is just a matter of replacing your (local) connection string with an Azure connection string. This is made really convenient by the Azure folks because they provide a connection string in the online admin tool that you can just copy over.

Go to the Databases tab, select your database and click the Connection Strings button and you get a useful pop-up.

One gotcha if you make a mistake in putting in your string (which I did) is that you might get an error stating “Format of the initialization string does not conform to specification starting at index NN” This indicates it’s an XML problem and so double-check your string.

I think that covers the basics of connecting to your database in the cloud. Have fun!

Advertisements