How do I stop using ASPNETDB.MDF in LocalDB?

Question

I implemented ASP.NET Identity and it automatically created ASPNETDB.MDF and aspnetdb_log.ldf in my App_Data folder. I already have the AspNet tables (i.e., AspNetRoles, AspNetUsers, etc) in my SQL Express instance (which is where all my other tables are sitting). As far as I can see, my application is reading and writing membership and role data from the SQL Express database and not ASPNETDB.MDF.

I have set my connectionString in web.config to:

<add name="DefaultConnection" connectionString="Data Source=MyComputerName\SQLEXPRESS;Initial Catalog=MyDatabaseName;Integrated Security=True" providerName="System.Data.SqlClient" />

However, if I remove ASPNETDB.MDF from App_Data, I get the following error when I login:

Exception Details: System.Data.SqlClient.SqlException: One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'MyComputerName\MyUserName'. Log file 'C:\Users\MyProjectName\App_Data\aspnetdb_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously

The error goes away once I add ASPNETDB.MDF back to App_Data.

I have searched all the code in my solution and it makes no reference to ASPNETDB. So why is it still trying to read from it?

I am developing ASP.NET web forms on .Net 4.5.

Answer 1

I was getting exactly the same problem. I discovered that VS annoyingly pulls in config settings from machine.config, which lives outside of the project, in my case in...

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

Identity 2.0 had used the following connection string inside machine.config...

<connectionStrings>
    <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

to set up a connection for...

       <membership>
        <providers>
            <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, ........" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>
        </providers>
    </membership>

.. (which was also set in machine.config). If you haven't been using Membership then it's fine to do as Lord nick suggests (except just the clear/ will do the job) and simply do the following in web.config...

<connectionStrings>
   <clear/>
   <add name="DefaultConnection" connectionString="whatever" providerName="System.Data.SqlClient" />

However, if you, like me, previously had Membership running (https://msdn.microsoft.com/en-us/library/6e9y4s5t(v=vs.100).aspx), you will need to comment out or delete the following sections from machine.config...

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

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

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

... since all this stuff is no longer needed for AspNet Identity 2.

I also had to add the following into in my web.config:

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

... as per this answer: Default Role Provider could not be found on IIS 7 running .NET 4

I hope I've saved someone some time. This took me hours and hours to work out.

Answer 2

I had the same Problem where the ASPNETDB.MDF was automatically created, even if I use Asp.Net Identity as the main user management.

I solved it by removing the following line from web.config:

<roleManager enabled="true" />

This one tells ASP.NET to use the older ASP.NET Membership user management, which is not supported by ASP.NET Identity.

Answer 3

It seems you have something like in your web.config

<add name="DefaultConnectionForLocalDb" connectionString="Data Source=(LocalDb)\v11.0;..."; />

In your AccountModels.cs file, you have:

public class UsersContext : DbContext
{
    public UsersContext()
        : base("DefaultConnectionForLocalDb")
    {
    }
}

However it should be this way:

<add name="DefaultConnectionForSQLEXPRESS" connectionString="data source=.\SQLEXPRESS;...;" />

public class UsersContext : DbContext
{
    public UsersContext()
        : base("DefaultConnectionForSQLEXPRESS")
    {
    }
}

Then you can remove safely DefaultConnectionForLocalDb entry from your web.config and ASPNETDB.MDF from to App_Data.

Answer 4

I don't know if you've figured this out or not but one of the things you can try is: in web.config, connections section, add <Clear/> and then <Remove Name=LocalSqlServer/>

Apparently if you don't change/remove the LocalSqlServe will still try to connect to the aspnetdb.mdf.

You might also think about adding back in the LocalSqlServer and having it point to your SqlExpress or SqlServer.