problem : I had a perfectly running ASP.Net 2.0 website with SQL Express on a Virtual PC (Windows 2k3 Server). I copied the Website to a different machine (Vista) and copied the database (MDF and LDF files) to App_Data folder of the website in the new machine.
I am using the following connectionstring to generate a user instance of the database.
<add name=”LocalSqlServer” connectionString=”Data Source=.\SQLExpress;Integrated Security=True;User Instance=False;AttachDBFilename=|DataDirectory|aspnetdb.mdf”/>
CREATE DATABASE permission denied in database ‘master’. An attempt to attach an auto-named database file XXX.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Also, I wanted to mention that I tried attaching the .mdf file directly to SQL Server using the Attach option and that failed with “operating system error (5) access denied“, even though I made SQL Server service run under an Admin account.
The Solution : The reason why the user instance was not getting generated was because my app pool for the website (in this case it was Default App Pool) was running under Network Service. If I change the account to Local System, everything starts working. I did give Network Service access to the Website, but that didn’t work.
I still haven’t found a solution for the access denied while trying to attach the mdf file to SQL server. If anyone finds a solution kindly post a solution in the comments. If I find it myself, I will update the blog entry with the solution.
UPDATE : 6/4/2008 – If you are running Windows Vista, make sure you right click on SQL Management studio and choose “Run as administrator”. That normally solves half of the problems.
Nishant Pant's Techie Blog 
Hi dude..
did you detach the data files before copying ?
— not that you are still looking for an answer after two months 🙂
– Hemanth (remember !!!)
Man I have the same problem what is the solution??
I have been struggling to get this right for the past 2 days with my new sharepoint installation – finally got it working, thanks to you. I see loads of valuable stuff on your blogs.
oh! i’m have the same problem with you :((
i’m finding it on google, … and hoping it will be clear 😀
Could you tell me, what security permission to be granted by administrator in order to tackle this problem.
I had same problem!
CREATE DATABASE permission denied in database ‘master’.
An attempt to attach an auto-named database for file C:\inetpub\wwwroot\WebSite2\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.
1. I did as administrator, delete c:/user/john/appdata/local/microsoft/microsoft sql server data/sqlexpress
restart my pc.
One application, which with app_data/aspnetdb works
But when I create other application with app_data/aspnetdb. The same problem happen again!
my all applications, which has app_data/aspnetdb do not work!
Thanks everyone post your ideas!
I was experiencing a similar problem when trying to attach the northwind.mdf file to my SQLEXPRESS server (.\SQLEXPRESS)
I got round it in the end by clicking the “Advanced…” button bottom right of the Add Connection window and changing the User Instance setting to “True”.
Hope this helps someone!
I had some similar issues, which I’ve documented in my blog. (Hopefully clicking my name should take you to the relevant entry.) However, I don’t recommend running your application pool as LocalSystem, because that gives IIS full control over your machine, i.e. if anyone can exploit a bug in IIS then they have a lot of power. Two key points:
a) To create a database, make sure that “NETWORK SERVICE” has a SQL login with the appropriate server roles (either dbcreator or sysadmin).
b) If it can’t open the file, make sure that the relevant account has permission to access it. In particular, it needs to work its way down the tree, so it should at least be able to read each parent folder, up to and including the root of the drive.
Thanks John for your solution. Just to clarify, I had mentioned this keeping in mind a developer machine which would never have any exposure to the outside world. However, your solution is great if you are dealing with a prod machine.
Easily, the post is really the freshest on this worthy topic. I agree with your conclusions and will eagerly look forward to your forthcoming updates. Saying thanks will not just be adequate, for the extraordinary lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates. Admirable work and much success in your business endeavors!
Great article! I bet a lot of work went into this post.
Great job man, I was looking for the solution for hours
Hi,
I had the same problem on my web server while trying to use the application. Can anyone help me with this.
Thanks.