Connecting to Azure SQL Database

2

May 25, 2016 by Kenneth Fisher

Recently I created my first Azure SQL Database. But now I need to connect to it.

You can connect using any number of tools. We can actually see several options by going back to our Azure Portal. Select the SQL Databases list, a specific SQL Database, and then the Quick Start option. (It looks like a broken cloud to me.)

ConnectAzureSQLDatabase1

Personally my tool of choice is SSMS. You can either follow the link show in the quick start menu or follow this one. I should point out that SSMS 2016 is free to download and use can can connect back to SQL 2008 (well actually 2000 but no guarantees that everything will work correctly).

Now install SSMS .. I’ll wait.

So now that SSMS is installed the next thing we need is the server name to connect to.

One of the properties of the SQL Database is the Server Name.

ConnectAzureSQLDatabase2

Unfortunately with a long name it’s abbreviated (it’s what you called it + “.database.windows.net” if that helps) and I haven’t figured out a way to copy it directly. What I did do is take a look at the connection strings and copy one of them.

ConnectAzureSQLDatabase3

Driver={SQL Server Native Client 11.0};
Server=tcp:myfirstserverinthecloud.database.windows.net,1433;
Database=MyFirstDatabaseInTheCloud;
Uid=sqlstudent144@myfirstserverinthecloud;
Pwd={your_password_here};Encrypt=yes;
TrustServerCertificate=no;Connection Timeout=30;

Enter that server name into a normal Object Explorer or Query Window connection window. Use the user name and password that you set up as your administrator. (There is of course a way to create other logins and I’m sure I’ll go into that at a later date.)

ConnectAzureSQLDatabase4

However the first time you hit Connect you will get an error.

ConnectAzureSQLDatabase5

I tried signing in and using the form above to create my new firewall rule but kept getting an authentication error. That being the case I went a different route.

First I go to the properties of my server, then to the firewall settings, type in the IP(range) and save.

ConnectAzureSQLDatabase6

Note: You can remove an IP(range) by hitting the ellipsis (…) and selecting delete. You do need to make sure to hit SAVE afterwards though.

Now that my IP is in place I can connect with no problems!

ConnectAzureSQLDatabase8

2 thoughts on “Connecting to Azure SQL Database

  1. dewitte says:

    Thanks for posting this. I need to look at Azure again. Funny story – we went to an Azure conference a few years ago and during the conference we did a kind of test where all the attendees set up virtual machines in the cloud and then started running load balancing program/tests of some kind (I forget now). Anyway, they were trying to show how solid the system was. This was a Saturday. We all went home and forgot about it until the next week when some of us received emails that we’d used up ALL of our free resource time. Apparently, it would have been smart to shut down those processes before we left. I haven’t tried even logging in since that time.

  2. […] is where we will need that server name from before. Don’t forget that in order to connect we need a firewall rule in place. Also pay special attention to the Edition (Basic, Standard, Premium, and Data Warehouse), Size […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: