Saturday, October 10, 2009

How to access SQL Azure from SQL Management Studio

It now more than a month Microsoft announced CTP of SQL Azure… SQL Azure a service to host full RDBMS database on Cloud. currently this service is available as CTP, so have very limited access and less features, but still it is a SQL server running there at cloud and very soon we can expect all SQL server features on SQL Azure.

Today when i started working on it, the best thing i found in it is, “you can access SQL Azure from your machine using your favorite SQL IDE tool, i.e.  “SQL Server Management Studio 2008”.

But very first time when i tried to access it, i got a nice error but thanks to my friend Jacob, he redirected to me to a site where that author mentioned a small but funny trick to use it.

so what i did -

I simply open SQL Server management studio 2008 from start menu, I entered full server name as “servername.ctp.database.windows.net”, my login name and password. and said connect. a simple and standard process which we follow every time to access SQL Server database.

Capture

this Error saying “Invalid Object name sys.configurations. (Microsoft SQL Server Error: 208)”

now how to connect to SQL Azure, i asked one of my friend Jacob who is a SQL MVP, and thanks to him he redirected me to a nice site where athlete gave one funny trick to make it work.

basically what you have to do is when you start SQL Server Management studio it prompt you a Connect to Server Windows (like this)

 Capture2

Now don’t Enter SQL Azure server name and login details here… simply say cancel

Capture3

and now say “New Query” it will again open a similar windows like previous one. now click on option button.

Now at login tab enter full server name, then select authentication type as SQL Authentication as SQL Azure only support SQL authentication and the Enter you login and password.

 Capture4

after this move to connection Properties Tab and Enter Database name which you created at SLQ Azure portal http://sql.azure.com

Please note this is important to enter this name as, SQL Azure don't allow you to connect  Server, it allow you to connect to Database.

Capture5

after your enter database name them simply click “connect”

if everything goes right then you will prompted with following error message

“Unable to apply connection settings, The detailed error message is “ANSI_NULLS” is not a recognized SET option.

Capture6

don't worry about this error, simply click “OK” .

Now you are ready to run your queries. try and Enjoy SQL Azure.

3 comments:

Anonymous said...

thanks for explaining all the steps..

I followed these steps but its giving the problem while connecting it to the server..

I am using server name as provided at sql azure and its giving error as ur client IP address is not allowed to access the server..

Are there any settings i hav to do with Firewall..??

Iwan said...

i always get this error: http://uploadz.eu/images/bo1bhviwcb2d5rjmxxi.png

what do i do wrong?

BoulderRider said...

I follow the steps and it works to connect but I can't get the object explorer to ever connect?