Azure SQL Error

Fixing error “Cannot open server – – requested by the login. Client with IP address – – is not allowed to access the server.” in Azure deployments from Visual Studio

This post describes how to work your way around the exception ‘Cannot open server – requested by login…’ The issue is caused by Azure’s somewhat annoying firewall logic, and might stop you from accessing your databases from your development machine.

Symptoms

When trying to publish a web project to Azure from Visual Studio, you suddenly get the following (or similar) error message:

Cannot open server ‘xxx.xxx.xxx.xxx’ requested by the login. Client with IP address ‘xxx.xxx.xxx.xxx’ is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.”

Reason

Your IP address has changed (for any reason) and Azure won’t allow your login anymore (as there’s a built-in IP filtering enabled). Azure kind of works as expected, Visual Studio’s error message just isn’t the most useful out there. Luckily, instead of running stored procedures or navigating the constantly evolving Azure Portal to desperately try to find a place where to edit SQL Server firewall rules, you can do this directly and conveniently in Visual Studio.

Solutions

Solution 1 – using the builtin firewall prompt

You’ll need to connect to the SQL Server through Visual Studio, which will prompt you to allow access to the server for your current IP address. Like so:

Connect to Azure SQL Server
Connect to Azure SQL Server

And here’s the very useful dialog:

Add Firewall rule to Azure SQL Server
Add Firewall rule to Azure SQL Server

Update 13.7.2016: 

It appears that the pop-up dialog mentioned above sometimes randomly does not appear. That’ll stop you from modifying the firewall this way. In that case, there’s another way to add the firewall rule, see here.

And there you are!

You may want to remember this trick, as it’s bound to happen again when your IP address changes 🙂

(The last two images from: https://azure.microsoft.com/en-us/documentation/articles/web-sites-dotnet-deploy-aspnet-mvc-app-membership-oauth-sql-database/)

And what if it doesn’t work? Well – that brings us to…

Solution 2 – modifying firewall via Azure Portal

You’ll need to access the database in the management portal. The link in Visual Studio has never worked for me (shown in the picture below), but you can luckily access it via url, too.

Open DB in Azure Management Portal
Open DB in Azure Management Portal

https://portal.azure.com/#blade/SqlAzureExtension (Update 10.4.2017: This link didn’t work anymore. Working one below!)

https://portal.azure.com/#blade/HubsExtension/Resources/resourceType/Microsoft.Sql%2Fservers%2Fdatabases

Accesing databases via Azure Management Portal
Accesing databases via Azure Management Portal

Using this dialog, you can modify the firewall rules easily. You can add either your IP address or the whole space of your network, if you know it!

The following two tabs change content below.

Antti K. Koskela

Solutions Architect / Escalations Engineer at Koskila / Norppandalotti Software / Valo Solutions
Antti Koskela is a proud digital native nomadic millenial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as a Solutions Architect for Valo Intranet, the product that will make you fall in love with your intranet. Working with the global partner network, he's responsible for the success of Valo deployments happening all around the world. He's been a developer from 2004 (starting with PHP and Java), and he's been bending and twisting SharePoint into different shapes since MOSS. Nowadays he's not only working on SharePoint, but also on .NET projects, Azure, Office 365 and a lot of other stuff. This is his personal professional (e.g. professional, but definitely personal) blog.

3 thoughts on “Fixing error “Cannot open server – – requested by the login. Client with IP address – – is not allowed to access the server.” in Azure deployments from Visual Studio

  1. I am rᥱally loving the theme/design of үoսr web site.
    Do you еver run into any internet browser compatibility issues?

    A few of my blog readers have complaineⅾ aƅout my site not working
    correctly in Exploreг but looks great in Chrome. Do you
    have any suggestions to help fix this ρrоblem?

Let me know your thoughts!