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.
Here is the information you need:
- The fully qualified server name. See the blocked out, red, parts of above image, as to where you can locate your server info.
- 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.
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.
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.
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:
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.
Rachel Appel 