ASPNET Identity 2.0 ROLES work in Development but not Production

Question

An application running on my development system (below) successfully implements roles on the SQL Express DB that exists within the application (moved from outside the app db). Pretty straight stuff, fortunately, as I am not a rocket scientist. On the production machine, the app successfully communicates with the user from the DB for log-ins, etc. (so we know that the DB is getting accessed) and of course the standard DB app stuff works well. The problem comes in when a "role" is encountered, but only on the production machine. The App_Data folder exists on both dev and production machines, but no data exists in either other than a log file and a log folder. Again - it works fine this way on the dev machine. I've messed with LoadUser=True on a custom Application Pool but no luck. Read/Write permissions are set on the App_Data folder.

The symptom is the app hangs when encountering a Role test (e.g. User.IsInRole("Role…") and produces the following:

[SqlException (0x80131904): 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)]

What I don’t get is that at the point of this error the application has already successfully communicated with the DB a "ton".

I've been at this all day and just don’t know what I am missing.  I am assuming/hoping that I am able to be able to use the DB Identity files as they currently exist.

I've briefly looked at the SQL-based identity but figured that since this is working with the user login that maybe it's just a setting. I've looked through the web.config and see NOTHING about Roles, which may be my problem - but I just don’t know where to start with that.

I will continue to pursue research and answers but would truly appreciate any direction that someone could lend.

Development system: VS2015 (VB) Win 8.1, DB SQL Express

Production machine: Windows Server 2008R2, DB SQL Server 2008R2

Answer 1

So these are my findings so far to what fixed the problem. Some testing is still required, but I spent long enough on this yesterday that I thought that it may help someone earlier than later.

What ultimately did the trick were a couple of things. First, I did add the following lines to my Web.Release.config file in the location:

<roleManager enabled ="true" defaultProvider ="SqlRoleProvider" dt:Transform="Insert">
   <providers>
    <add name ="SqlRoleProvider"
      type="System.Web.Security.SqlRoleProvider"
      connectionStringName="[add your connection string here to match your SQL db]"
      applicationName="[app name here = necessary??]"/>
  </providers>
</roleManager>

The reason to add it there and not directly to my web.config file was that my development system used SQL Express and the production machine was running SQL 2008R2, so it was only needed on the production machine and caused errors in Debug on the local machine.

The second thing that was needed was to run aspnet_regsql.exe on my production machine only. See below for more details and some further insight into the chronology here. The combination of these two steps enabled the application to function on the production machine.

A little background… aspnet_regsql.exe is located in the %windir%\Microsoft.NET\Framework64\v4.0.30319 directory. It runs interactively. Make sure to use the appropriate version (Framework or Framework64) for your particular needs. There are lots of posts on this. Now I had run this before on the production machine – – the good news is that you can run it to install it and run it again to remove it. When I ran it the first time, it inserted a number of tables as indicated below.

Preliminary research suggested this, but initially I ran it before any insertion into the production machines web.config file and it didn't work. When I checked the aspnet_Users and aspnet_Roles tables they were empty and so I figured that it wouldn't work and then I backed them out by running aspnet_regsql.exe again to remove them. All this time, my development machine ran perfectly with roles enabled, but no insertion into a web.config file which caused some initial confusion. Then I read the following:

The following default roleManager element is not explicitly configured in the Machine.config file or in the root Web.config file. However, it is the default configuration that is returned by the application. The providers are explicitly configured in the Machine.config file. More on that HERE under "remarks: default configuration". Evidently that is why the dev box ran fine with no web.config lines inserted.

I'll add more if it find out something to further clarify this, but I hope that this helps someone. Many thanks to @StuartLC for the nudge in the right direction.

FINAL EDIT FOR FOLLOW-UP OF DELETING aspnet_* Tables Residuals So to be honest, it's taken me THIS long to muster up enough courage to attempt to delete the remaining aspnet_* tables, views, procedures, schemas and roles. THANKFULLY, a fine gentleman had gone through this before HERE and was kind enough to post it. Much to my surprise, I setup the SQL query and, like magic, it worked!! So now I am left with JUST the AspNetRoles...AspNetUsers tables and NO aspnet_* (underscore) tables.