Troubleshoot Error: An attempt to attach an auto-named database for file <C:\database file path> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

This article tries to troubleshoot the error: 

An attempt to attach an auto-named database for file <C:\database file path> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

You may get the error message similar to the following picture,

I have seen many articles stating the steps to resolve this issue. But when I face the problem for the first time, I get confused. Finally I resolve the problem after visiting one of such articles. But that article just gives the instructions, not the steps. So I decided to write this article with the step by step procedure to resolve the error.

There are several reasons for the above error. The last part of the error message - "A database with the same name exists, or specified file cannot be Opened, or it is located on UNC share" has nothing to do with. For the beginners it is a confusing error.

The problem manly occurs when we use the auto attach feature in Express editions of SQL Server. It is due to the fact that by default ASP.NET uses the default ASPNET account and the SQL Server does not allow it to access the database file.  

Please follow the following 3 simple steps to resolve the above issue correctly.

First check your database connection and the connection string in the web.config file. The web.config file may look like the following,

<connectionStrings>
   <add name="ConnectionString" connectionString="Data                 Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Retinaa.mdf;Persist Security   Info=True;Integrated Security=SSPI;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

If your connection string is different then change it accordingly. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode authentication. 

       To change the mode select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select 'Security' node to view/change the authentication mode.

       Now you are almost done. Before browsing your application make sure that your web.config file contains the "identity impersonate" tag. You can also write the code directly in the web.config file in the <system.web> section. 

<identity impersonate="true" password="ap()@ru" userName="abhisek" />

        Identity Impersonate property is used to control the identity under which code will be executed. By default, ASP.NET does not use impersonation and instead executes all code using the same user account as the ASP.NET process, which is typically the ASPNET account. So the server is unable to find your database file. Hence you have to make use of Identity Impersonate property and give a valid account information as above. 

I hope this article will help you resolving the issue correctly. You can also search for other reasons in the web.