Getting "SQLExpress database file auto-creation error" for ASP.NET site that uses AspNetSqlMembershipProvider, but connection string is to SQL Server 2005

Question

Folks,

I have an ASP.NET v2.0 website (not web application) where the root directory is public, but the "Admin" subdirectory requires authentication. Everything about the setup is very generic - I used a connection string defined in the root web.config, and the standard AspNetSqlMembershipProvider provider, and I'm using the ASP.NET Login control. The web.config in the Admin directory specifies that the user must have the "Admin" role.

I'm using SQL Server 2008, and my connection string is good; every root level page is data driven and they all work fine. And the provider configuration seems good: when I log in, the Login control's "OnLoggedIn" event fires. The last line in that event code redirects me down to my Admin/Default.aspx page. My breakpoint in "OnLoggedIn" shows me that all's well until the redirect down into my Admin directory...and then...

...and then I wait...and wait...

And then I get an error telling me it's experienced a "SQLExpress database file auto-creation error."

Why in the world is it suddenly trying to create a SQL Server Express file? Why is it suddenly ignoring my connection string?

One odd clue: Just before the last line of the "OnLoggedIn" event I put in this: bool blnTest = User.IsInRole("Admin");

I wanted to see if blnTest = true. What happens is the process hits this line...and waits...and eventually tells me it can't access the SQL Server Express database. It seems that any reference (either in my code, or behind the scenes) to determine the User's Role, calls the wrong database.

Thoughts?

EDIT: Argh, sometimes it waits when I test blnTest. Other times it immediately reports the value as "false."

Answer 1

When you specify the AttachDBFilename option in the connection string you are in fact asking for your-very-own-just-in-time-provisioned SQL Server instance, aka the 'user instance'. This instance is created by asking the 'master' instance (the .\SQLEXPRESS instance) to provision a child instance, which implies copying the master/model/msdb into your profile, starting a new SQL Server process under your account configured to use the freshly copied master/model/msdb, then asking this 'child' instance to attach the specified 'file' as a new database. The details are explained in SQL Server 2005 Express Edition User Instances.

The process of creating a child instance is extremly fragile and when it breaks the DB call results eventually in a time out error when opening the connection. In your case it seems that the process breaks in some cases (when you reach the protected part of the site). Why it breaks, is very hard to guess without proper information. Look at the Common Issues in the linked article and see if any applies to you. Also check the system event log for any message why the child instances cannot start or it cannot open the MDF file. Note that a common mistake is to ask for the same physical file with AttachDBFilename under different credentials: each credential will start its own 'child' instance and only the first one will succeed in attaching the desired database.

Answer 2

According to your comment, it looks like you have haven't explicitly configured a role provider for you site.

If all that's in your web.config is:

<roleManager enabled="true" />

Then you are relying on the default providers declared further up the configuration hieracrchy (machine.config, global web.config, etc)

In machine.config you've probably got something like:

<roleManager>
  <providers>
    <add name="AspNetSqlRoleProvider" 
      connectionStringName="LocalSqlServer" 
      applicationName="/" 
      type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    <add name="AspNetWindowsTokenRoleProvider" 
      applicationName="/" 
      type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</roleManager>

As you can see, the first provider is configured to use a connectionString called LocalSqlServer - which is also usually declared in the machine.config:

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

And this is designed to use a local file based database that will be created if it doesn't already exist.

So to get roles working on your site, you should ammend your root web.config to something like:

<roleManager enabled="true">
  <providers>
    <clear />
    <add name="AspNetSqlRoleProvider" 
      connectionStringName="YourConnectionStringName" 
      applicationName="/" 
      type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</roleManager>

Using the <clear /> element will remove all previously defined providers for that type.