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

Using PowerShell to set ULS logging level to “extra verbose” to catch all the events in the logs

Stock photo from pixabay.com

This blog post describes how set the SharePoint’s ULS level to “Extra Verbose” (VerboseEx) using PowerShell. This is not possible using the browser UI.

Description of the solution

By default, ULS logging is somewhat non-detailed, and a lot of data that could be used to debug issues is omitted. The UI cannot be used to set this level of logging – it is limited to verbose. In case you really, REALLY need to get all the data logged to ULS, you can use PowerShell to enable verboseex -level tracing, using the following command:

Beware, though: this will generate a lot of data, and it’s likely you won’t be able to do anything with the log files using notepad++ or similar tool, as a single file can be hundreds of megabytes, and handling that might get a little tricky.

You can always reset this setting to default (medium-level tracing) by running the following command:

 

More info about the trace levels:

Disabling anonymous access on a single site through PowerShell

Anonymous access in SharePoint 2013

This post is about managing Anonymous Access on a SharePoint site (SPWeb) using PowerShell commandlets. It’s often a lot more feasible and even easier than using the browser interface!

Description

Assume you have a site collection that’s published to the whole world. You’ll have anonymous access enabled at both web application and site collection -levels, and configured permissions at the root web -level. Now, let’s assume you want to disable anonymous access on a certain site deeper in the site structure. This way anonymous users could access your site at http://site.com and http://site.com/subsite, but not at http://site.com/subsite/deepsubsite. As an added bonus, that web would even be removed from the navigation for anonymous users (security trimming).

Solution(s)

Of course, you could do this through site permissions -page via browser (http://site.com/_layouts/15/user.aspx) by breaking permissions inheritance and disabling anonymous access, but there are multiple situations when this is not feasible – say, for example, that you already have a redirection for that certain url set in the IIS or gateway, and can’t access the page. Luckily, this can also be done with PowerShell.

 

This is a lot faster than through browser, right? 🙂 Just remember to use the right url for the web, SharePoint will find out the right zone for you!