ASP.NET MVC membership login works locally, not on azure app service

Question

My ASP.NET MVC application is deployed on Azure app service and was working until today. It won't let me login anymore through the application which uses ASPNET membership. If I run the application in Visual Studio locally pointing to the production azure sql db it works. The deployed app can read data fine but it seems when it tries to login it doesn't while deployed on azure. I haven't changed any code so I don't know why it stopped working on azure when it was. It still allows for reading of data, it displays items in a browse page. I saw this error when I disabled custom errors:

A network-related or instance-specific error occurred while 
establishing a connection to SQL Server. The server was not found 
or was not accessible. Verify that the instance name is correct and 
that SQL Server is configured to allow remote connections. (provider: 
SQL Network Interfaces, error: 26 - Error Locating Server/Instance 
Specified)

I've checked these questions but they didn't seem to make it work:

Azure SQL firewall

Network interface azure

Azure passwords

Hashed passwords

login works locally not on azure

azure debugging

login register locally not on azure

UPDATE I used remote debugging and it's failing in a razor view when I check the roles using this line:

if (User.IsInRole("Administrator")) {

That's kind of weird. It would almost have to be a firewall issue. Are you 100% sure you configured the database server firewall correctly? You could try to add the whole IP range temporarily to see if that fixes the problem. Then you know for sure that's where the problem is. — gerwin, May 05 '19 at 22:12
I've have the "allow access to Azure services" turned on in the firewall settings. I think that is just like putting the whole IP range in. — Heinrich, May 06 '19 at 00:50

score 0 · Answer 1 · answered May 06 '19 at 03:26

I don't know why it was working for a while then stopped but this is what I did to fix it. It was trying to connect to an old membership database connection LocalSqlConnection from a machine.config file. I saw it creating the aspnetdb.mdf in the AppData folder when I would run in visual studio but I am using ASP.NET Identity. I had to modify my web.config like this keep it from using the local db:

Comment out these:

<!--
<membership>
    <providers>
    ...
    </providers>
</membership>

<profile>
    <providers>
     ...
    </providers>
</profile>

<roleManager>
    <providers>
    ..
    </providers>
</roleManager>
-->

Add this:

<modules>
     <remove name="RoleManager"/>
</modules>

Add this in <connectionStrings>

<add name="LocalSqlServer" connectionString="same as application database connectionstring"...

The answer from @clayRay in this question gave me most of the answer:

How do I stop using ASPNETDB.MDF in LocalDB?

ASP.NET MVC membership login works locally, not on azure app service

1 Answers1