Fixing SQL error 5170: Cannot create file ‘C:\[…]\APP_DATA\ASPNETDB_TMP.MDF’ because it already exists.in ASP.NET

The problem

I recently had, for the first time, to create a brand-new ASP.NET web application that made use of the built-in Membership/Roles functionality, using the default of SQL Server Express, NOT a full installation of SQL Server. I had previously wrestled with issues when upgrading an older application using Membership/Roles to ASP.NET 4.0, and blogged about it . However, this issue was specific to a brand-new ASP.NET 4.0 application, albeit with VS 2010 SP1 and SQL Server 2008 SP1 installed.

All was well until I fired up the Web Site Administration tool and clicked the Security tab. I was suddenly greeted with the following error message:



There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: An error occurred during the execution of the SQL file ‘InstallCommon.sql’. The SQL error number is 5170 and the SqlException message is: Cannot create file ‘C:\[Application_PATH\APP_DATA\ASPNETDB_TMP.MDF’ because it already exists. Change the file path or the file name, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Creating the ASPNETDB_[app GUID] database…


I checked the App_Data directory, and, sure enough, the ASPNETDB_TMP.MDF file was there.

Googling didn’t turn up much that was specific to my issue.

The fix

The answer turned out to be simple, and hinted at in the error message: in the Web.config file in the root of your web app, find the roleManager element, and set it to true:

<roleManager enabled="true">

But—and this is what threw me—you must first delete the ASPNETDB_TMP.MDF file, and its companion log file with the .ldf extension, from the App_Data directory, before re-running the Web Site Administration tool . This can be done within Visual Studio. Once this is done, make sure you close the ASP.NET Development server, which will be in the system tray, aka the notification area. Then go back into Visual Studio and launch the Web Site Administration tool. You should now be able to click the Security tab and configure your Roles, etc.

Advertisements
  1. #1 by Bjarke Ørrild on October 28, 2013 - 4:07 pm

    Holyshit, can’t belive that fixed it! Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: