If you have been using the SQL Azure Portal for creating SQL Databases and connecting to them from your SQL Server Management Studio, you would have realized the ability to query the Azure Cloud Databases from within the management studio. While this provides great opportunities in terms of relation capabilities and accessibility over the Cloud, there has been a recent upgrade to SQL Azure and in that process there has been a Firewall Support added to the SQL Azure Portal access.
Specifically when you try to connect to SQL Azure, you would face the error
Cannot connect to <sql azure server name>
Cannot open server <servername> requested by the login. Client with IP Address "<your IP Address>” is not allowed to access the host server.
Login failed for user ‘<username>’. (Microsoft SQL Server, Error: 40615)
As the error message indicates, the SQL Azure Portal has declined access to the portal for your IP Address. This specific filtering helps companies protect their data hosted in the cloud from access by just any host. This allows companies to restrict the access to a specific set of hosts such that they do know that those are safe.
As a result, your existing applications / querying from SQL Server Management Studio might just fail. Note that by default no IP’s are allowed to access your server.
Also, there has been a change in the way the server is accessed as well as the credentials specified. Lets see them step by step.
Steps to enable IP Access in the SQL Azure Portal
Visit the SQL Azure Portal at https://sql.azure.com and sign-in with the LiveID that you used to create the SQL Azure database(s)
Click on the Project name to open the Server Administration Page
You will find the Server Name, Administrator username and Server Location details specified. You would require these when connecting to the SQL Azure database from your Management Studio. Note that the server name is no longer <servername>.ctp.database.windows.net and just simply <servername>.database.windows.net
You would also find the databases listed under the Databases tab and additionally you would find the “Firewall Settings” tab. Click on the “Firewall Settings” tab you will find a screen as below:-
Check the “Allow Microsoft Services access to the server” check box. The page posts and then adds a “MicrosoftServices” Record Name as below
Click on the “Edit Record” to open up “Custom Firewall Settings” window that lists your IP address as well. You can also specify a range in case you want to. Once you have typed the IP Address, click “Submit” (Note that if you dont have an IP Address range, you would have to type your actual IP Address in the bottom textbox after the “to” which means the top one is 0.0.0.0 to <IP Address you specified>.
You can add more IP Addresses by clicking on “Add Record” button and repeating the above steps. Once you do this, you would be able to access the SQL Azure database from SQL Server Management Studio.
Steps to access the SQL Azure Database using Management Studio
I had already written on this, in my earlier post that you can check. But, in the interest of folks who have patiently read this post from the beginning, I am repeating the steps below
Open SQL Server Management Studio
Dismiss the default login window that comes up (a temporary work around)
Click on “New Query” and type the credentials. The servername is “<servername>.database.windows.net” and the Login is simply <username> and not username@servername as in the past. Click on “Options” to specify the Database that you created in SQL Azure. Otherwise, the default would connect to Master. Once you enter the password in the “Login” tab and click “Connect” you should be able to connect to the SQL Azure database.
If you get any error, check if you had dismissed the initial login prompt and clicked on “New Query” in the Management Studio.