Floating FB popout byF5debug

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:

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.

[more]

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/

clip_image002

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

clip_image004

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

clip_image006

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

clip_image007

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

clip_image008

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

clip_image009

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

clip_image010

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
——————————

clip_image011

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
——————————

clip_image012

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

clip_image013

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

clip_image014

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)

clip_image015

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

clip_image016

We can query and use it as per our requirement locally without connecting to the online Management portal as shown in the screen below

clip_image017

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Comments (3) -

uri
uri
7/25/2013 6:34:10 AM #

I'm using Azure DB with ssms 2012. I don't see the ALTER and EXEC options in the context menus. In addition to that i dont have context menus!

Is there anything i can do to make it work?

Jean F. Farjon
Jean F. Farjon
8/7/2013 4:09:53 PM #

Thanks for having taken the time to write this article, but it turns out that it is now totally outdated.

Maybe once someone will write a new one for the new portal :-)

manguera contra incendio
manguera contra incendio
10/29/2013 10:34:49 AM #

Hello, how do you think you're? What's new the day in program?I was surfing the web, when I started to seek out information about this matter, but none met my expectations, everyone looked just as comparable and very little could gratify my personalized and specialized desires... until finally I discovered its contents.Input I find it fantastic that they've mixed so many contents, with audiovisual content material. This info that your website provides is actually beneficial...In nations like mine the data is from time to time difficult to discover, a single can last for hrs looking to locate very good articles or blog posts, good films, or very good shots. But its World-Wide-Web web page has all of it... genuinely, also it is possible to say that the information continues to be organized in this kind of a method that discover it can be really easy.Certainly not seen as clean up, clear and clear written content: as trustworthy.I hope to continue as they have completed so much, and that nothing interrupted this site or blog... will not know precisely what exactly is, but I like very And that I assume constantly go to this handle to share facts inside exact same way that you choose to do.Trade tips on this subject is really critical for being capable not merely to collaborate and achieve a friendship, but generally to produce inside the expert subject. Not think about how several moments he received sought this info without to get in a position to seek out you, but it is exact to say that I congratulate you, that your sheet can be a accomplishment.I hope that we can maintain in contact, my page We've put it in my account making sure that I can find. You already know that they'll rely on me to aid with this web page.Greetings, but I have to go, thank you on your attention, great luck in every thing.

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter