How to connect SQL Azure database from SQL Server Management Studio

Tweet

Once SQL Azure Server created from Azure Management Portal, we can create the databases, tables and all related tasks using the same portal itself. SQL Azure Management Portal gives easy way to access the server from the web itself and do all database operation on it.

But as a developer we are already familiar with SQL Server Management Studio (SSMS) for all Database related operations. So how to setup SSMS to connect SQL Azure database from local system?

The the following steps provide how to configure to connect the SQL Azure database from Management Studio.

As the database sits on Microsoft Data Center and can be publically accessible using internet, it is very important to protect the database from anonymous access. So for security reason SQL Azure database can’t be accessed without adding the IP address of the computer where the database is accessed.

1. Find SQL Azure DNS Name, User Id and Password
   
   Before connecting the SQL Azure database from SSMS, collect the login details from Management Portal.
   
   
   1. Fully Qualified DNS name – The DNS can find from the property section when selecting the SQL Azure Server.
   2. User Id – Can find from Management Portal or Ask Subscription Administrator.
   3. Password – Ask Subscription Administrator.
   The following image shows the details of each section.
   
2. Connecting from SQL Server Management Studio.
   
   SQL Azure server can't connect from Management Studio, but it is possible to connect the SQL Azure database using New Query option.
   
   
   1. Open the Management Studio and cancel the Connect to Server popup window at initial stage.
      
      
      
      
   2. From SSMS, create a new query file using New Query tool button or select the menu option using New -> Database Engine Query.
      
      
      
      
   3. It will open a Connect to Server popup window. Provide the Server Name, Login Id and Password and click Option >> button.
      
      
      
      
   4. In the Connect to database type the database you are going to connect with.
      
      
      
      Note: If you are not giving the database name to connect. The SSMS will automatically connect to master database at SQL Azure. After connect to one database in SSMS, it is not possible to change from Available database dropdown box (There will be error The database XYZ is not accessible). So you required to enter the exact database name to connect with.
      
   5. Press the Connect button.
   6. You may be getting the following error while connecting the SQL Azure database.
      
      
      
      
   7. Note the IP Address which shows in the error message and follow the below steps.
      
      1. In the Database Section of the Management Portal, select the SQL Azure Server in which the database required to be accessed.
      2. In the middle of the screen expand the Firewall Rules section. Just below to the Firewall Rules button, there will be a table which shows list of IP addresses (those IP addresses shows that, computers having the IP address of that range can be connected to that server).
         
         
         
         
      3. Add the computer IP Address by clicking Add button. In the popup screen, enter the Role Name, IP Address range.
         Note: The Your current IP address value may be different then the IP address noted in Step 6 if the network configured with NAT. So you can try with this IP Address or the IP Address which you noted in step 6.
         
         
         
         
      4. The Add Firewall Rules popup provides a way to add a single IP or a sequence of IPs (mentioning starts and end IP). But incase if any computer can connect to that database without configuring the IP address in Firewall Rules, that can be done by selecting Allow other Windows Azure services to access this server option. When this option selected, there will be an entry to the firewall rules with MicrosoftServices rule name.
         Note: This will break the security bridge. So it is recommended to not select this option for critical database.
         
         
         
         
   8. In SSMS, try again by clicking Connect button in Connect to Server popup. You will be getting connected to the database in SSMS.
      
      
      
      
   9. Incase if you are getting the following error, TCP port 1433 outbound is not open (or there is a firewall policy stopping your connection). So contact administrator. When opening TCP port 1433, recommended to block inbound and open outbound only for security reason.
      
      
      
      
   Possible Errors:
   
   When connecting to the database, you may be getting the following error Invalid object name ‘sys.configuration’. (Microsoft SQL Server, Error 208)
   
   
   
   The reason may be, you are connecting the SQL Azure server using Object Explorer – Database Engine or File – Connect Object Explorer option.
   
   To connect the SQL Azure database, use New Query and when popup shows, provide User Id, Password and Database to Connect and Press Connect.