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.

Abhisek
Posted by in Error and Solution category on for Beginner level | Views : 53948 red flag
Rating: 5 out of 5  
 1 vote(s)

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.

This article tells the step by step procedure to handle this type of error.

Introduction


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.  

N.B:- Here I am using IIS7 as the server, Windows 7 as the OS and MS SQL Server Express 2005. Here I am assuming that you know the procedure how to add an ASP.NET application to the IIS server, change the security property and some basic functionality and features of the IIS server. This article only provide the steps to resolve the stated error. If your application is not configured properly with the IIS server then you might not be able to resolve the issue. So first make sure that you have configured your application correctly in the IIS.

The Procedure

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

Step 1:


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.

Step 2:

     
       Now open the IIS console and locate your web application. Select the Authentication feature in the "Feature View" window.


       Then in the authentication window select "Anonymous User" and click on Edit. Give a valid account information by checking "Specific User" or you can check the "Application Pool identity".  



Step 3:


     Then select "ASP.NET Impersonate" and click Edit. In the dialog box check "Specific User" and click on "Set". Then give a valid windows account user name and password and click on OK. 



       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.

Page copy protected against web site content infringement by Copyscape

About the Author

Abhisek
Full Name: Abhisek Panda
Member Level: Bronze
Member Status: Member
Member Since: 10/11/2009 6:25:59 AM
Country: India
Abhisek Panda

Thanks and Regards Abhisek Panda Go Green And Save Your Future

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)