.NET Zone is brought to you in partnership with:

Rob is a professional IT specialist with over 14 years of commercial expertise working for small, medium and large enterprise customers and clients as well as local, state and federal governments within Australia and internationally. Of these fourteen years, six have been served in a professional consultancy capacity, three as a project manager and eleven years in professional software engineering and architecture. Rob is a DZone MVB and is not an employee of DZone and has posted 58 posts at DZone. You can read more from them at their website. View Full User Profile

Connecting to SQL Azure with SQL Management Studio

09.24.2013
| 4844 views |
  • submit to reddit

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

1

2. Click on SQL Databases

2

3. Click on Servers

3

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

4

5. Click on Configure…

5

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

6

7. Hit the Connect button

7

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:

9

Lastly…

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

8

Published at DZone with permission of Rob Sanders, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)