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
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 [...]
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.