Connecting to SQL Azure with SQL Management Studio

Intro

If you want to manage your SQL Databases in Azure using tools that you’re a little more familiar and comfortable with – for example – SQL Management Studio, how do you go about connecting?

You could read the help article from Microsoft, or you can follow my intuitive screen-based instructions, below:

Assumptions

1. I’m assuming you have a version of SQL Management Studio already installed.  I believe you’ll need at least SQL Server 2008 R2’s version or newer

2. I’m further assuming you’ve already created a SQL Database in Azure

Steps to Connect SSMS to SQL Azure

1. Authenticate to the Azure Portal

2. Click on SQL Databases

3. Click on Servers

4. Click on the name of the Server you wish to connect to…

5. Click on Configure…

If not already in place, click on ‘Add to the allowed IP addresses’ to add your current IP address (or specify an address you wish to connect from) and click ‘Save’

6. Open SQL Management Studio and connect to Database services (usually comes up by default)

1. Enter the fully qualified server name (<server>.database.windows.net)
2. Change to SQL Server Authentication
3. Enter the login preferred (if a new database, the username you specified when yuo created the DB server)
4. Enter the correct password

7. Hit the Connect button

Troubleshooting

1. Ensure you have the appropriate ports open outbound from your local network or connection (typically port 1433)
2. Ensure you have allowed the correct public IP address you’re trying to connect from via the Azure Portal (steps 1-5 above)
3. Ensure you are using the correct server name and user name
1. For SSMS, this is the server name (in step 4) followed by .database.windows.net
4. Ensure you are using SQL Server Authentication
1. For SSMS the username format is username@servername
5. If you forgot the password of your username, you can reset the password in the Azure Portal, in step 4, click on Dashboard:

Lastly…

You can click on the Database (in step 2) to see your connection options: