Configure SQL Azure to Access from Local SSMS

/in

I have found 2 different ways for how to access and work in SQL Azure database:

1)  On Windows Azure Portal:

=> Open portal and go to SQL Databases section => Select database => click on Manage URL in quick glance section, right side of the panel.

This will redirect to Management Portal-SQL Azure, login into the database by inputting required fields then start work into the database.

As it directly accessible from a browser, so the approach helpful while SSMS is not installed on the machine or accessing SQL Azure database.

But as a developer, considering the SSMS tool always installed on the machine and most of the developers are much comfortable for accessing SQL server objects through SSMS than a browser.

So to make it work, first we have to configure SQL Azure in SSMS, for it follows below steps:

2) Configure SQL Azure to open in local SSMS, which gives a feeling like working SQL Server of on-premises:

2.1) Configurations required on Windows Azure Portal:

  • Allow public IP address into SQL Azure environment:

Move into SQL databases section on portal => select Servers in the main panel.

  • Click on -> right side in Server Name cell
  • Move into CONFIGURE section and add new Rule with start IP address 0.0.0.0 and End IP 255.255.255.255 and save it by click on SAVE button in the bottom of the screen.

You can set rule by adding current client IP address, but it changes frequently so each and every time you have to add the current public IP address into firewall rules on Windows Azure.

2.2) Local machine level configurations:
Open SQL server configuration manager from Start Menu.

  • Navigate to Protocols for MSSQLServer node => enable TCP/IP protocol.
  • Open TCP/IP properties by right click it and then properties or by double click, and mentioned Port no: 1433 in IPAll section. Apply & close window.
  • Open Windows firewall => Advanced settings
  • Navigate at Inbound Rules => Create new Rule
  • Select Rule type => Port
  • Move into Protocols and Ports section and mentioned Port no: 1433 for TCP.
  • Do not make any changes in the Action and Profile section.
  • Set Name for the rule in the last section and finish.

2.2) Connect to SQL Azure in SSMS:

  • Provide below information into Connect to Server (this is sample information, you have to mention as per your configurations)

Server Name: tcp:<ServerName>.database.windows.net,1433 (it should be fully qualified server name, pick from the windows azure portal)

Login: <UserId>@<Servername>

Password: <Password> which you set for a database in the portal

For example:

Server Name: tcp:qm1qbebehe.database.windows.net,1433

Login: UserId@qm1qbebehe

Password: ********