Connecting SQL Management Studio to SQL Azure

8 Flares Twitter 3 Facebook 0 StumbleUpon 1 Google+ 3 LinkedIn 1 8 Flares ×

connections

One of the first things I tend to do when working with databases on Windows Azure is connect to them from my development machine. This tutorial will walk through the relatively simple task of connecting SQL Management Studio (SSMS) to SQL Azure in order to ease management and querying.

This is the 3rd article in the series regarding getting up and running on windows azure:

Connecting SQL Management Studio to SQL Azure is a fairly simple thing to do, which just has acouple of things that can catch you out. First we need to allow our local IP address within the SQL Azure firewall, then connecting is a breeze.

Series: ASP.NET Web Apps on Windows Azure

This article assumes that you have a copy of SQL Server Managment Studio installed. If not, you should be able to download a copy from the Microsoft website: http://www.microsoft.com/en-gb/download/details.aspx?id=29062

Allowing access to SQL Azure

Here we’re going to set the SQL Azure fierwall to allow connections from our local IP.

  1. Navigate to your SQL Azure database dashboard (more detail instructions can be found in Creating a SQL Azure Database.
  2. Next go to ‘Manage allowed IP addresses’
    Go to manage allowed IP addresses
  3. You should see on this screen that your IP address is listed as ‘Current client IP Address’. Here Azure has recognised your ip address and listed it for you so that you can easily add it to the firewall rules:
    Manage Allowed IPs Current Client IP
  4. To add the firewall rule simple click right arrow and you should see the IP address move into the list of rules:
    Manage Allowed IPs Added Current IP
  5. Finally click the Save button at the bottom of the page
    Manage Allowed IPs Save Changes

Connecting with SQL Server Management Studio

Connecting with SSMS should be pretty straight forward:

  1. In the previous article we discussed how to create and azure database. In this step we collected the ADO.NET connection string, and our logon credentials (username and password). You’ll need these for the next steps.
  2. From the connection string you need to extract the server name. The server name part of the connection string should start with “tcp:” and end with “,1433”:

The “tcp:” portion refers to the protocol used to establish the connection. The “,1433” is the port the connection will be made over.

  1. Open SQL Server Management Studio. You should be prompted to connect to a server:
    a. Enter your server name from the previous step in the server name box
    b. Make sure you select “SQL Server Authentication” for the authentication option.
    c. Enter your user name and password in the login and password boxes
    d. Click connect
    Connect To Sql Azure

You connection should now open up within SSMS.

Troubleshooting

In my experience this works pretty well with one common issue – that being the configuration of the firewall on your development machine and/or your company network. If the crrect ports are not open your connection will fail generall with a message reading something like:

In order for you to be able to make this connection your firewall(s) must be configured to allow connections via TCP port 1433 – this is the default port sql server uses for TCP connections, and the one that SQL Azure uses as a standard.

How to determine if port 1433 is open

A really simple way to test if it is the port that’s getting in the way, you can try to open a telnet session to the database server. To complete this your just need the server name as mentioned earlier, removing the protocol and port portions.

So if your server name from earlier looked like:

You’d just need the part that says

Then follow these simple steps:

  1. Open the command prompt (press the Windows key, then type ‘cmd’ and press enter)
  2. Enter the following telnet somecrazystring.database.windows.net 1433
  3. If the command prompt window turns blank then you have esxtablished the connection, and your port configuration is fine.
  4. If the command prompt takes some time, and then outputs a message like Could not open connection to the host, on port 1433: Connect failed then there is some problem connecting via port 1433 and it is most likely some sort of firewall issue.

Wrapping up

When working with SQL databases, SSMS can prove invaluable for querying, managing and visualizing. Windows Azure maintains a way to connect to SQL Azure databases with SSMS, and makes the job fairly easy. Its little things like this that can make you so much more productive in developing the next generation of apps, whether web or mobile.

I hope that you found this article useful, and it helped you get to grips with both how to connect SSMS to SQL Azure, but also whats going on a little bit. I’d love to hear if you have anything that makes your day easier when working with back-end infrastructure for your apps – its often overlooked, but can make our lives easy when creating apps with back end services.

I just thought I’d drop in a link to this video I found on youtube about connecting SSMS to SQL Azure for those who prefer video tutorials.

The Essential App Marketing Kit
Subscribe To My Newsletter To Get an Entire Chapter From The Book for FREE
Never display this again
8 Flares Twitter 3 Facebook 0 StumbleUpon 1 Google+ 3 LinkedIn 1 8 Flares ×