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

Comments (4)

  1. Kevin says:

    "Note: The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true)."

    See: msdn.microsoft.com/…/x28wfk74.aspx

  2. Dan Deb says:

    Hi Xinyang.

    I've read your post, and it is the only one talkin about an issue similar to mine.. so I try to ask help to you..

    I am running a web app (virtual under default: http://my.domain.name/myApp) with ASP.NET auth, so using the  aspnetdb.mdf.

    Everything worked fine, till yesterday: i tried to publish a new version of my app, but now i get error on login page (you'll find error message below).

    My app works fine in debug under VS2010, but got error after deployment..

    So I tried to build from scratch a new web app, without any custom code!!

    While in debug in Visual Studio 2010 SP1 everything works fine.. (i can create account, logon, logoff..)

    but after deploy (I deploy the aspnetdb.mdf too) i get error on logon (well, every time I try to access db, I suppose..)

    I really do not understand.. it worked fine till some days ago… I did not change any IIS param or Application Pool..

    p.s. I tried to give Full Access to folder and files.. but still error… I tried Network Service identity for Application Pool.. no success!

    Any idea ??

    Thanks in advance

    danilode@gmail.com

    ERROR:

    Server Error in '/my_app' Application.

    ——————————————————————————–

    User does not have permission to perform this action.

    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: User does not have permission to perform this action.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

    Stack Trace:

    [SqlException (0x80131904): User does not have permission to perform this action.]

      System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064458

      System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234

      System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275

      System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +35

      System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) +183

      System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +239

      System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +195

      System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +232

      System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +5078107

      System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +33

      System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +524

      System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66

      System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +479

      System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +108

      System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126

      System.Data.SqlClient.SqlConnection.Open() +125

      System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +95

      System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +206

      System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +827

      System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105

      System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +106

      System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +60

      System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +129

      System.Web.UI.WebControls.Login.AttemptLogin() +127

      System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101

      System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37

      System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +125

      System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +167

      System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10

      System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13

      System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36

      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

    ——————————————————————————–

    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

  3. johnv says:

    Solution: Try UNINSTALLING any updates before you started experiencing the issue.  I spent countless hours – wasted hours that I will never get back in my life – reading and following every solution possible without success.  I uninstalled all SQL Server updates and now everything works fine.  !@##$$!!!!

  4. Hi, Dan Deb,

    Sorry I didn't see your comment until now.  If your problem has not been solved, please check if social.msdn.microsoft.com/…/41b25c58-c530-4bb4-8a81-b26eaa292a5e helps or not.  If still not, please post the question to the SQL Server Forums (access from above link).

    Thanks!

    Xinyang

Skip to main content