SQL Azure - Connect to SQL Azure Using SQL Server Management Studio

Introduction:

In this article we are going to see how to connect to the SQL Azure database using SQL Server Management Studio.

Overview:

The SQL Azure Management Portal has enhanced options to develop a database with tables, stored procedures and views. In order to do a traditional development, we can connect to a SQL Azure database locally using the SQL Server Management Studio and do our changes as needed. For this purpose we only need to install SQL Server Client to get the Management Studio free of cost without any license. Let us see in detail step by step how to do this.

Steps:

Log in to the Azure portal using the following link. You can see the screen look similar to below.

http://www.microsoft.com/windowsazure/

Login to the portal using your Microsoft Windows Live credentials with Azure credentials to the Management Portal and you will see the screen as shown in the screen below:

Now we can see the Database Menu at the bottom left; click on that to go to the Database Subscription window as shown in the screen below:

Clicking on the subscription name will provide the complete details of the server created and the new databases created as shown in the screen below:

Now we are ready with the details of the Cloud Server; to open it locally Open SQL Server Management Studio in Programs as shown in the screen below:

A pop up requesting the User credentials will be loaded as shown in the screen below.

In the Server Name we need to give the full name of the SQL Azure server and the login credentials should be also the same admin login which we created in the SQL Azure Cloud server as shown in the screen below:

Now click on the Connect button; we might get an error indicating that the IP is not under the Firewall rule (Check my earlier article on Firewall rule adding for more detail) as shown in the screen below.

Error Message:

TITLE: Connect to Server
——————————
Cannot connect to XXXXXXXXX.database.windows.net.
——————————

ADDITIONAL INFORMATION:

Cannot open server 'XXXXXXXXX' requested by the login. Client with IP address '117.193.194.136′ 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.
Login failed for user 'XXXXXX'.

This session has been assigned a tracing ID of 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'.  Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 40615)
For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=40615&LinkId=20476
——————————
BUTTONS:
OK
——————————
 

Now after adding the IP to the fire wall rule using the SQL Azure Management Portal, try connecting to the server. We may now get an error as shown below:

Error Message:

TITLE: Connect to Server
——————————
Cannot connect to XXXXXXXXXX.database.windows.net.
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid object name 'sys.configurations'. (Microsoft SQL Server, Error: 208)
For help, click:

http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.25.9640&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
——————————
BUTTONS:
OK
——————————

To overcome this follow the following steps as shown step by step.

First Cancel the Authentication window and Click on New Query windows as shown in the Screen below. We will be getting this authentication window again.

Now enter the Azure credentials and click on OPTIONS as shown below:

We can see that more advanced options are available for this connection like connecting to a particular database, network protocol and connection time out etc. Now type manually the SQL Azure database which we are trying to connect to (in our example we are going to use a School database type School as shown in the screen below).

Now click on the Connect button to connect to the SQL Azure database. Now we can see the Management Studio is connected to the SQL Azure online database as shown in the screen below:

We can query and use it as needed locally without connecting to the online Management Portal as shown in the screen below:

Conclusion:

So in this article we have seen how to connect to the SQL Azure Database using SQL Server Management Portal and the issues and errors encountered in this process.