Introduction:

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

Overview:

In SQL Azure Management Portal we have enhanced options to develop a database with tables, stored procedures and Views. In order to do a traditional development, we can connect SQL Azure database locally using the SQL Server Management Studio and do our changes as per our requirement. For this purpose we can install only SQL Server Client to get the Management Studio free of cost with out any license as well.

Let us see in detail step by step on how to perform this task.

Steps:

Log in to the Azure portal using the below 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 will 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 database’s 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 for 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 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 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 over come this follow the below 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 more Advanced options available for this connection like connecting to the particular database, Network protocol and connection time out etc. Now type manually the SQL Azure database which we are trying to connect (In our example since we are going to use School database type School as shown in the screen below)

Now Click on 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 per our requirement 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 comes in this process.