14 Days Free Technical Video Training from WintellectNOW

  

How to Connect to SQL Azure through SQL Server Management Studio 2008 (SSMS 2008)

Tags: SQL Azure, Windows Azure

Most developers on the Microsoft stack that use SQL Server, and who are migrating their databases to SQL Azure, likely work in SQL Server Management Studio 2008 (SSMS 2008), Visual Studio, or a mixture of both, for DBA and data management tasks. Fortunately, SQL Azure has two options for those who need to administer, create, maintain, and develop using SQL Azure: The SQL Azure Management Portal and SSMS 2008.

Before you start -- If you want to try out the online tools or see take SQL Azure for a spin with SSMS 2008, sign up for a 90 day free trial.

Connect to SQL Azure from SSMS 2008

You can use SQL Server Management Studio (including, and especially, SQL Express) to connect to SQL Azure, but you first need to have some information handy that you can find in the SQL Azure Online Management Portal. Once there, you can view your subscription information including the information you need to connect to a SQL Azure server or database.

image

Here is the information you need:

  1. The fully qualified server name. See the blocked out, red, parts of above image, as to where you can locate your server info.
  2. Valid credentials that you have already setup via the online SQL Azure Management Portal (of course, the password is not available for viewing, as it should be memorized anyway)

Enter this information into the SQL Server 2008 Connect to Server dialog, and click the Connect button to authenticate. Don't forget you must choose SQL Server Authentication before you may enter credentials.

image

Upon successful authentication, SQL Server Management Studio opens. This is the exact same SSMS you are familiar with, with the only difference being that you have connected to SQL Azure instead of a SQL Server on your LAN.

SNAGHTMLa64d4e1

From here you can run queries, manage tables, and do all the SQL administrative tasks you need to. Note there is a SQL Azure Database node in the Template Explorer that you can access from the View menu. Of course if you are using SQL Azure you'll want the SQL Azure SDK for Visual Studio 2010.

Connect to SQL Azure from Visual Studio

The same credentials and authentication happen in both tools, SSMS and Visual Studio. This means all that you need to do is open the SQL Server Object explorer and connect exactly as you would any SQL Server in your LAN. Once connected, you can enjoy administering and working with SQL Azure inside of Visual Studio.

SNAGHTMLcf0cc28

Troubleshooting SQL Azure Connectivity

The Firewall check failed error is very common, as you need to enter an IP Address range to connect to SQL Azure from various client programs (i.e., SSMS). Here's the error text:

Firewall check failed. Cannot open server 'SERVERNAME' requested by the login. Client with IP address XXX.XXX.XXX.XXX is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

If you get this error you can reset it from the Windows Azure Mgmt Portal. Just navigate to the server you need access to, then add in the IP Range, as shown in the image below:

image

Alternatively, you can use SQLCMD in the Windows command prompt which will look something like this:

C:\>SQLCMD –U<user>@<server> -P<password> -S<server>.database.windows.net

exec sp_set_firewall_rule N'Allow Windows Azure','0.0.0.0','0.0.0.0'

SQLCMD Azure commands: http://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx

NOTE: Only the server-level principal login (this is the primary/master login that you use to connect to the Windows Azure Portal online, a Windows Live Id), while connected to the master database, can configure firewall settings for your SQL Azure server. Also, check out the SQL Azure troubleshooting for other common errors, troubleshooting, and help.

3 Comments

  • Rachel said

    Well it is obvious that your definition of 'proper' is way off. :-p

    On a serious note, I don't know why - because the load balanced-ness of Azure is great for thwarting data loss (removing one need for backup), but you still need backup in case of data corruption (this is an unmet need). There are workarounds, but meh, not the best IMO.

Comments have been disabled for this content.