SQL Azure – Configuring SQL Azure so you can connect from Local SQL Management Studio


In my second post of my SQL Azure series, I’m going to show you how to connect to you SQL Azure server database from SQL Management Studio. You can only connect to SQL Azure from somewhere that is not a Windows Azure service by defining IP Firewall rules.

 

Configuring SQL Azure Firewall rules.

  • Open up the Management Azure Portal and log in using your username and password.
  • Navigate to the SQL databases area.
  • Then for your database, click on the Server Name.

  • Click on the Configure Tab.
  • Find out your external IP address(s) that you will be connecting from, the screen will tell you your current IP address at the top of the screen.
  • In the Rule area below, Put in a Rule Name and then fill out either a range (if using dynamic/multiple IP locations), or the given IP address(es) you will be connecting from.
  • Click Save.

Connecting from a Local SQL Management Studio

You will need to use SQL Server 2012 or above to connect to SQL Azure. Alternatively you can download SQL Server 2014 express for free and download the MgmtStudio Download.

  • Once you have opened the Management SQL Studio, put in the Server name with .database.windows.net at the end. (E.g., Server Name is ctwsc5d11a, then the Server name you put in is ctwsc5d11a.database.windows.net
  • Change the authentication to SQL Server Authentication.
  • Login using the UserName and Password you created when setting up the SQL Azure Server and Database.
  • Click Connect.
  • Once connected you can create Tables, Store Procedures etc. using point and click (which creates valid T-SQL templates) or T-SQL Statements*

(*Please note, not all T-SQL statements that work in Standard SQL works in Azure. For example SELECT INTO doesn’t work unless you predefine the Table and columns first, also there is no designer mode when creating Azure SQL Tables in SQL Management Studio, all you can do is get the T-SQL template to create the table)

Advertisements