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

Azure SQL Error

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

Update 13.7.2016: 

It appears that the pop-up dialog mentioned below sometimes randomly does not appear. In that case, there’s another way to add the firewall rule, see here.

Solution 1

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:

Continue reading

Troubleshooting guide for Windows hosts -file

This post describes how to fix possible issues with Windows hosts-file.

Opening the hosts -file

First of all, hosts file location is usually: C:\Windows\System32\drivers\etc\hosts (link may or may not work depending on your security settings and browser version, but you can always copy-paste it to your text editor.

Editing hosts-file requires elevated privileges for the text editor process, so you’ll need to run it as an administrator.

Continue reading