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.