18th Aug 2009
Connecting to SQL Azure
So, now that SQL Azure CTP1 has been released there are a few things that most people will probably run into when trying to connect to SQL Azure with SQL Server Management Studio (SSMS), SQLCMD and OSQL.
SQL Server Management Studio
Update March 2010: With the release of the SQL Server 2008 R2 Tools November CTP working with SQL Azure from SSMS is much better. Please read my new post if you want to use the updated tools.
One of the best parts about working with SQL Azure is that you can use the tools that you already know and love. There are some differences though with the System Views, System Stored Procedures, System Tables and SMO that break some of those tools. The current version of SSMS 2008 is able to connect to SQL Azure but the Object Browser doesn’t work. There is also a trick to getting connected.
When you fire up SSMS and are presented with connection dialog box, if you enter your credentials you will get the following error.
TITLE: Connect to Server
——————————
Cannot connect to MY_SERVER_NAME.ctp.database.windows.net.
——————————
ADDITIONAL INFORMATION:
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.00.9006&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
The key to getting around this is to click on the Cancel button on the initial dialogue box.
Then click on the New Query button.
You will be presented with a new connection dialogue that looks the same as the initial one.
Enter your credentials, if you are trying to connect to a specific database click on the Options and enter a database name because USE <database> is not supported.
Once you connect you will get a error
TITLE: Microsoft SQL Server Management Studio
——————————
Unable to apply connection settings. The detailed error message is: ‘ANSI_NULLS’ is not a recognized SET option.
You can just click OK.
Now you are good to go! You can issue queries, visualize explain plans, etc.
SQLCMD and OSQL
For those of us who like to use command line tools, SQLCMD and OSQL are also supported. The key when connecting with these tools is that you must use the <username>@<servername> in the connection string.
sqlcmd -S MY_SERVER_NAME.ctp.database.windows.net -U MY_USERNAME@MY_SERVER_NAME -d DATABASE_NAME
The key here is that you must use MY_USERNAME@MY_SERVER_NAME… For example, if your server name is h38ssfjeiwh201, your username is admin and connecting to sample_db your connection would be.
sqlcmd -S h38ssfjeiwh201.ctp.database.windows.net -U admin@h38ssfjeiwh201 -d sample_db
Since SQL Azure doesn’t currently support the USE <database> you need to specify a database name.
More Information
- SQL Azure MSDN Docs – http://msdn.microsoft.com/en-us/library/ee336282.aspx
- Connecting to SQL Azure Demo in the Windows Azure Platform Training Kit – http://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78
Great info Zach, thanks. I’m able to connect to my SQL Azure database with SSMS, but was wondering if Microsoft plans to add Object Browser support. It’s awfully dark in the query window…
Great question Lenni… SSMS will have support for SQL Azure in the Object Browser with the caveat that certain functions don’t apply to SQL Azure. A lot of those differences will be around manageability. We don’t have a solid timeframe yet for enhanced SSMS support but I’d expect we’ll see something before PDC.
Great info and site.
Thanks for the tips. Is their a trick for running aspnet_regsql.exe against SQLAzure?
I was corrected that OSQL is not officially supported
[...] information but there’s a few quirks with the CTP you have to deal with. Zach owns blog post on using SQL Azure is the best place to start to figure out how to connect. Like I said [...]
I tried to connec to SQL Azure database through SSMS as suggested above but got an error saying “Cannot connect to xxx.ctp.database.windows.net. The server was not found or accessible. Verify that instance name is correct and SQL server is allowed to configure remote connections”
Please help
@Tejas, I’m just getting back into the office from a climbing trip… Are you still having connectivity issues? If so, please send me an email and we can take this offline.
@Zach,
I have resolved the connectivity issues.
@Tejas -what did you do to resolve the connectivity issues? I’ve been running into the same problem for two days. I can’t even ping the server.
@mike, I’ll send you an email now to take this offline to try to figure out what’s going on. We can follow-up on the comments once we get it resolved so others can benefit.
[...] to connect to SQL Azure. The first is using sqlcmd, a command-line utility and the second is using SSMS query window. Unfortunately, there is no current support for using SSMS “Connect to Database [...]
My first try:
sqlcmd -S xxx.ctp.database.windows.net -U herial@xxx
ly -P xxxxxx
HResult 0×35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
My second try:
sqlcmd -S tcp:xxx.ctp.database.windows.net -U herial@xxx -P xxxxxx
HResult 0x274C, Level 16, State 1
TCP Provider: A connection attempt failed because the connected party did not pr
operly respond after a period of time, or established connection failed because
connected host has failed to respond.
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I also tried SSMS, the same error occurs. Any comments for my issue?
A problem when I try to mask the server name in the last reply:
sqlcmd -S xxx.ctp.database.windows.net -U herial@xxx
ly -P xxxxxx
->
sqlcmd -S xxx.ctp.database.windows.net -U herial@xxx -P xxxxxx
Hi Zack, I’m having the same issue as Tejas, I can’t connect to my azure server. Any ideas?
Regards,
Rick
Your SQLCMD gives me the error that says there is a syntax error at “-S”.
Would you by any chance know why?
I had success of connecting to SQL Azure with a UDL file as described in my blog:
http://hodentek.blogspot.com/2009/09/my-experience-connecting-to-sql-azure.html
@Jayaram, could you provide some more details? This does not look familiar.
@Rick, Did you get this resolved yet? Most of these issues we seen have been using the wrong server name, pass, etc.
I use the syntax checker in SSMS with query running in the SQLMode
I get the syntax error at ‘S’.
BTW I found that IIS7 Database Server is a better and more stable connection UI than SSMS.
i am also getting connectivity issues same as of Tejas & mike h … pls give info on how to resolve this connectivity issue ?
[...] apparently it works on SQL Server Management Studio 2008, with a small workaround. Get the free version here: Mangement Studio Express [...]
Thanks! Thanks to you I can now use SSMS!
Hi Zach, I’m getting below error when I try connecting to azure database. Same error from SSMS2K8, SQLCMD and UDL file
Error:
network-related or instance-specific error has occurred while establishing a connection to SQL Server.
Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
Kindly help!
Using the new query tool I was able to connect to the server..
I created a second database using the Portal, but I cannot access it. I see it in the drop down, but i get an error.
Also, w/o the object browser, How do I create a database or logins etc.
–Mindy Jeanne
Cerontek Dog Pedigree Component is ideal for running your own dog website. You can maintain a large dog pedigree database and share it with the world! Our dog pedigree component is built for Joomla 1.5 sites and features 4 doglist layouts, dog gallery, dog profile with generation chart, dog tips, dog breeds, dog names and more. The dog pedigree component provide user tools to add/edit and manage dog profiles. Also the pedigree component bundlepack comes 9 cool modules to enhance user experience.
Hey administrator, I have a small request. I was just simply googleing for some info on the subject you wrote and found this post. Some great stuff you published here. May i if possible discuss this particular post on my latest web site I’m creating? That would be great
. I will return again afterwards to see how you replied. Cheers, Kerri Macall
Skip Hop Studio Diaper Tote Tote is awesome.So numerous pockets – and also not these modest, worthless pockets, either. All of the actual storage compartments are a good dimension and really help maintain everything organized. The handles are a fantastic length and fit nicely more than the shoulder; the handle shoulder straps also remain put on my shoulder, which is vital when you are carrying a infant. The bag appears wonderful, as well. Not too fancy, but not too casual. (I’ve it in black) The material is soft (can’t believe of a greater word) so it is effortless to squeeze in to tight spaces – but yet it is sturdy. I’ve a Fleurville Lexi carrier and I really like it, as well, but this handbag is actually kind of inflexible. I believe that Skip*Hop may become my daily tote. It’s a bit huge – so if you are not really in to substantial hand bags this may possibly be much better as an over-night baby diaper handbag.
Facebook Profile Themes
Profile Themes for Facebook
Thank you very much for sharing this site! It has been very useful for me.
I love the way you express yourself. I found the article very pleasing to read. I’ll be back next follow your publications with the same enthusiasm
‘:; I am really thankful to this topic because it really gives up to date information ‘`-
Good tips about dogs. I own an 8 yr old golden retriever and I love him to death. Will come back for sure! .
Thank you for a great Blog and some superb posts. As a new Blogger, I know that I have a lot to learn, and who better to learn than from someone like yourself who has achieved so much? Your Blog is a true inspiration.
Excellent web site. Lots of useful information here. I am sending it to a few friends ans also sharing in delicious. And certainly, thanks for your sweat!
Quite useful information about SQL.
Thanks for the tips
I believe other website owners should take this web site as an model, very clean and superb user pleasant style and design .
Excellent weblog here! Also your web site rather a lot up very fast! What host are you the use of? Can I get your associate link for your host? I desire my web site loaded up as fast as yours lol
Can there be any methods to hook up to your website without having opt-in to the RSS? I can’t understand why however I can’t have the RSS loaded on the reader although I can read that via the chrome.
Definitely, what a fantastic website and revealing posts, I will bookmark your blog.All the Best!
I and my friends were found to be looking at the best key points on your web site then all of the sudden came up with a horrible suspicion I never thanked you for those techniques. All of the guys became as a consequence happy to learn all of them and have now truly been tapping into these things. I appreciate you for truly being simply kind and also for obtaining variety of outstanding things millions of individuals are really desirous to learn about. Our own sincere apologies for not expressing gratitude to earlier.
Hi! This is a very informative post and useful too. thanks for providing a link for SQL Azure MSDN Docs.
I really like that. You touched my heart!
Okay say thank you God Lauryn is back this is why
Squeeze pages templates may be a powerful email list construction instrument if they weren’t much a pain to make.
This is the best blog for anyone who needs to search out out about this topic. You realize a lot its virtually laborious to argue with you (not that I truly would want…HaHa). You positively put a new spin on a subject thats been written about for years. Great stuff, simply nice!
Whats up! I simply would like to give an enormous thumbs up for the nice data you might have here on this post. I can be coming back to your weblog for more soon.
Youve some genuinely valuable information composed here. Outstanding job and maintain posting superb stuff.
Hi my family member! I want to say that this post is awesome, nice written and come with almost all important infos. I’d like to peer more posts like this .
Thank you for discussing such an topical article with all of us. I’ve bookmarked your blog will come back for a re-read again. Keep up the good work. A very useful article – Thank you very much I wish you don’t mind me writing about this post on my blog I will also leave a link back Thanks..
http://recreationxleisure.com/
Excellent weblog here! Also your internet site rather a great deal up extremely fast! What host have you been the use of? Can one get your associate link for your host? I desire my site loaded as fast .
What a wonderful article. Thank you. super great article. everyone can learn from this and help themselves be happy in life.
http://spirittourism.com/
Pretty component to content. I just stumbled upon your website and in accession capital to assert that I acquire actually loved account your blog posts. Anyway I will be subscribing for your feeds or even I success you get admission to constantly fast.