This post was most recently updated on July 27th, 2022.
3 min read.This article explains how to work your way around the exception ‘Cannot open server – requested by login…’ The issue is caused by Azure’s firewall logic, which might sometimes appear a little bit overzealous for simple development workloads, and might stop you from accessing your databases from your development machine.
But just as everything else in Azure, the firewall exists for a reason, and working with it just requires a couple of extra clicks, is all. :)
Symptoms
Typically, you encounter this issue, when trying to publish a web project to the Azure app service from Visual Studio. You suddenly get the following (or similar) error message:
“Cannot open server ‘123.123.123.123’ requested by the login. Client with IP address ‘123.123.123.123’ 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.”
This error stops you from publishing/deploying your app. That, as you might know, can be a bit of an issue. But why does this happen?
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
Like so often, there are a couple of solutions available. But the solution most of the time is NOT to run sp_set_firewall_rule, despite what the error message says. :)
If the first solution doesn’t work for you, check out the second one instead!
How to fix the “Cannot open server – – requested by the login. Client with IP address – – is not allowed to access the server.” error in Azure deployments from Visual Studio
Time needed: 10 minutes
- Use the built-in firewall prompt in Visual Studio
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
And here’s the very useful dialog:
Add a Firewall rule to Azure SQL Server
However, 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 below.
You may want to remember this trick, as it’s bound to happen again when your IP address changes :)
(The previous 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… - Modify the firewall via the 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
The URL you can use to access your SQL databases is this:
https://portal.azure.com/#blade/HubsExtension/Resources/resourceType/Microsoft.Sql%2Fservers%2Fdatabases - Select your database from the list you’ll see there
While there, just find your database and edit the firewall rules as shown below (updated 11.11.2019 – this UI keeps changing!):
Accessing database firewall settings via Azure Management Portal.
Using this dialog, you can modify the firewall rules easily. You can add either your IP address or the whole address space of your network if you know it!
Note, however, that these changes apply to the whole server, not just your database.
That’s it :)
Feel free to leave me a comment in the comments section below!
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.
This is his personal professional (e.g. professional, but definitely personal) blog.
- “Performing cleanup” – Excel is stuck with an old, conflicted file and will never recover. - November 12, 2024
- How to add multiple app URIs for your Entra app registration? - November 5, 2024
- How to access Environment Secrets with GitHub Actions? - October 29, 2024
