Membership not working with Azure SQL database-level firewall

Question

We have an Azure Cloud application which stores user login (Membership) info into an Azure SQL database. We have several versions of this database, one for production and one for development. We want our development database to be open to the world and the production limited to Azure cloud applications. While the databases were open to the world, all worked fine. However, after I modified server-level firewall rule in Azure SQL to limit the allowed IPs, excluding the IP of my home computer, and added a database-level rule which allows the IP of the home computer, Membership stopped working. Now, I can access the database from home using Microsoft SQL Management Studio, and even connect to it with Visual-Studio Server Explorer (which uses the connectionString from web.api). However, the Membership does not allow a connection to the database from home anymore. Membership.GetUser() returns:

{System.Data.SqlClient.SqlException (0x80131904): Cannot open server 'XXXXXX' requested by the login. Client with IP address 'XXX.XXX.59.158' is not allowed to access the server. To enable access, use the Windows Azure Management 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. Login failed for user 'visualbee'. This session has been assigned a tracing ID of '23e9447b-f169-49ab-88df-305172323803'. Provide this tracing ID to customer support when you need assistance.

The database connection string defined in web.config is:

<add name="usersDB" connectionString="data source=XXXX.database.windows.net;Initial Catalog=XXX.YYY.UsersDatabase;User ID=uuu@gggg;Password=pppppp;Encrypt=true;Trusted_Connection=false;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Membership and RoleManager definitions from web.config:

<membership defaultProvider="DefaultMembershipProvider" hashAlgorithmType="SHA1">
  <providers>
    <clear />        
    <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=XXbf3856ad364eXX" connectionStringName="usersDB" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="2147483647" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />        
  </providers>
</membership>

<roleManager enabled="false" defaultProvider="DefaultRoleProvider">
  <providers>
    <clear />
    <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />        
    <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=XXbf3856ad364eXX" connectionStringName="usersDB" applicationName="/" />        
  </providers>
</roleManager>

Database-level firewall rule: (as you can see, it excludes nobody)

How many connection string do yo have in connectionStrings inside web.config? Could you also post membership and roleManager tags inweb.config? — Win
might be you have dynamic IP? or you can check connection string via webmatrix on live mode? — Sandip Bantawa
Your description is a little confusing... is the issue with a web application deployed to Azure or from the same application running in debug mode on your local development machine (at home)? Dynamic IP allocation can be a problem - you may be able to connect via SSMS but cannot from your code. I've had this exact behaviour on 4G hotspots where each call comes from a range of IP addresses which get blocked by the Azure SQL DB firewall. — Simon W
Thanks for the remarks. Win: I added the membership and roleManager tags to the post. I do have several other connection strings in web.config, but looking into the MemberShip structure, I saw that it indeed tries to access the current database. brykneval: I have a static IP, which is the same as the one reported by the error message, where it says this IP is blocked by the server. You can also see in the database-level rule I added to the post, that all IPs are supposed to be accessible. Simon W - it doesn't work on my local machine, but as I wrote to brykneval, this should not be a problem. — Arie Livshin

Arie Livshin Arie Livshin · Accepted Answer · 2015-02-15T20:36:28

I finally found a solution by experimenting. I added a database-level rule to the 'System-Databases' database:

exec sp_set_database_firewall_rule N'AllowAll','0.0.0.0','255.255.255.255'

Now I am able to connect to the user database using Membership. Other databases with appropriate database-level firewall rules are also publicly accessible. The rest of the databases return a "Client with IP address XXX is not allowed to access the server".

Membership not working with Azure SQL database-level firewall

1 Answers