Tip #106 Did you know … How to solve the errors when aspnetdb.mdf is created dynamically with IIS?

In VS2010 all editions, the default .NET Framework 4.0 ASP.NET Web Site and Web Application template create default website with following connection string in the web.config:

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

To get the aspnetdb.mdf created on the fly, one can simply Ctrl-F5 to run the web site or web application.  Enter a username and password and click “Log In” button to let the ASP.NET Development Server or IIS server process create the aspnetdb.mdf file for you. 

image

Problem #1

If you created the website as a http web, such as http://localhost/test1/ , or you created the web application and  make it use local IIS web server, you may encounter the following error message on win7 and win2008 R2 when trying to let IIS server process create the aspnetdb.mdf file dynamically:

Server Error in '/test1' Application.

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
SQLExpress database file auto-creation error:

The connection string specifies a local Sql Server Express instance using a database location within the application's App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

  1. If the application is running on either Windows 7 or Windows Server 2008R2, special configuration steps are necessary to enable automatic creation of the provider database. Additional information is available at: http://go.microsoft.com/fwlink/?LinkId=160102 . If the application's App_Data directory does not already exist, the web server account must have read and write access to the application's directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
  2. If the application's App_Data directory already exists, the web server account only requires read and write access to the application's App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the application's App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server account's credentials are used when creating the new database.
  3. Sql Server Express must be installed on the machine.
  4. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.

The above error is most likely due to IIS7 ASP.NET v4.0 application pool is running under ApplicationPoolIdentity.  Change it to NetworkService will solve the issue.  Details is in http://go.microsoft.com/fwlink/?LinkId=160102 .  The following picture shows how to do it in IIS Manager:

image

Problem #2

If created web application is in default user project directory, you may also get the following error message: (Note, aspnetdb.mdf is created under app_data folder successfully, but just cannot get access to it)

Server Error in '/WebApplication1' Application.

An attempt to attach an auto-named database for file D:\Users\FWTLABA\Documents\Visual Studio 2010\Projects\test1\WebApplication1\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file D:\Users\FWTLABA\Documents\Visual Studio 2010\Projects\test1\WebApplication1\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

This problem and workaround is explained in detail in http://support.microsoft.com/?kbid=2002980 . It’s a SQL express limitation if the project is under the default project location, such as c:\users\[ACCOUNTNAME]\Documents\Visual Studio 20XX .  If this is a personal computer, it might be a better idea to create the web application project in a non-default folder to avoid the workaround steps.

 

Also, VS2005, VS2008, VS2010 all supports the following ways to create aspnetdb database:

1. After create Visual Studio website or web application, Click “ASP.NET Configuration” menu item inside Website menu (for website project) or Project menu (for web project).

2. Commandline aspnet_regsql method to create the aspnetdb schema and basic data in any SQL database

 

- Xinyang Qiu |Visual Web Developer Team

No Comments