Connecting to SQL Azure from Visual Studio on the new Azure Portal

There are circumstances when you need to directly connect to a SQL Azure database from a client app like “Visual Studio” or “SQL Server Management Studio”. Here are the steps you should follow to make it happen:

1. Login to the new Azure portal at http://portal.azure.com

2. Click on SQL databases on the left-side navigation.



3) Next, click on the database that you wish to connect to:



4) You need to grab the database connection string for you database. On the right-side blade, click on “Show database connection strings”.



5) The “ADO.NET (SQL authentication)” connection string will be displayed on the right-side and will look like this:

Server=tcp:adbserver.database.windows.net,1433;Initial Catalog=Aspnet2Azure;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Copy this connection string and paste it into a text editor, like Notepad, as adjust your username and password. Below, is an example of what it could look like:



Temporarily, park the connection string in the text editor as we will use it later.

6) Start Visual Studio. In “Server Explorer”, right-click on “Data Connections” then select “Add Connection…”.



In the “Add Connection” dialog, do the following:

Server name: enter the value of Server from your connection string, without “,1433”. From my above example, this would be: tcp:adbserver.database.windows.net

Authentication: SQL Server Authentication

User name: enter the username for the database

Password: enter the password for the database

Save my password: enable this checkbox



Select or enter a database name: click on the drop-down-list.

You should receive an error message like this like this:



This message suggests that you need to allow the client IP address to access the database. This is a firewall setting that needs to be made on Azure.

7) Return to the the Azure portal. Click on “Set server firewall”.



On the blade that appears on the right side, click on “+ Add client IP”.



Your client IP address will be added to the list of acceptable IP addresses:



Don’t forget to click on save to apply the firewall rule change.

8) Return to Visual Studio.



Click on the Refresh button, enter the name of your database, then click on OK. You should be able to connect:

Conclusion

In addition to Visual Studio, the above steps work for any client like “SQL Server Management Studio”.