Thursday, November 20, 2008

Unable to open the physical file "C:\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)"

One of the applications I test frequently detaches and re-attaches SQL Server databases. The app manages hundreds of databases all with identical schema, detachable for portability and ease of copying.

Originally the app only worked with SQL Server 2000 (or MSDE), but after SQL Server 2005 (SQL Express) had been available for a while, we decided to support it too. It turned out to be harder than we expected, because every time we detached a database, we were unable to attach it later.

The errors looked like this:

Unable to open the physical file "C:\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)"

Turns out that SQL Server 2005, in order to better secure the data inside an MDF file, automatically resets file permissions whenever the MDF is detached. I got no problem with that -- it makes sense, when you think about how before, your data was always securable as long as you were accessing it through the SQL Server engine, but if you forgot to secure your MDFs and LDFs, anyone who got their hands on those could probably get whatever information they wanted out of them.

Just, for our app, it made life crazy. Permissions would get reset so that only the person who did the detach could re-attach. Or sometimes, a process running as a service under the Local System account would detach, and then nobody could manually re-attach.

Luckily, after a lot of research that took way too long, we discovered trace flag 1802. Trace flag 1802 turns off the permission-resetting behavior. This particular trace flag needs to be set in the startup properties for the instance, so that it is always in effect. If you set it dynamically using DBCC TRACEON, it won't stick through the next time you restart your instance.

To enable trace flag 1802:
1. Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager

2. In the tree list on the left, click on SQL Server 2005 Services

3. On the right, right-click your SQL Server instance name, and choose Properties

4. In the Properties dialog, click on the Advanced tab

5. Find the property called Startup Parameters. There is a long string of parameters in this field. Click the dropdown arrow to view and edit the string.

6. At the end of the string, add ;-T1802



14 comments:

Ulises said...

Hey thanks for sharing, do you think there is a way to to with code? I would like to reset SQL Server and/or set this flag during the installation process of a windows form application (C#)

Thanks !

ss02 said...

information is very helpful.

but attaching this database in sql
now gives error in win-form execution.

error is same but error no. is 32.

how can we handle this so that we can use the mdf database in vb.net as well in sql without attaching and dettaching...

chris said...

Wow!
I've bumped into this one before especially when working with SQL db migrations and back then I ended up doing a backup/restore to get around this.
Now when setting up a new standalone machine I bumped into the same issue when moving the report instance db log to a mount point and your trick did it!
Very much appreciated ;)

Anonymous said...

Thanks, after reading many posts about permission issues with the SQL Service account (reqiring Modify permission on the File System) I came across your article. As I only needed one attached DB sent from another location, I executed
DBCC TRACEON (1802, -1)
in the Management Studio query window. I then manually attached the MDF file that was sent to me (I deleted the LDF as it recreates one).
Regards
Ertan

Anonymous said...

Saved my bacon. Thanks dude.

Naidu said...

Thanks a lot Tony Halperin for your article, Which gives me solution after i searched so many articles.

Mehboob said...

Thanks sir,its really work fine...

Mehboob Yousafzai

Rohit Arora said...

I am having a same issue. But for me it's not working at the moment. any solution u guys

Bernard said...

Very good solution, is it also neccessary and working in an SQL 2008 enviroment ?

irecep said...

thank you for your information, it worked both version 2005 and 2008.

aravin said...

Thanks a lot, it worked for me

Anonymous said...

I am having exactly same issue. But for me it's not working at the moment. i am using sql 2008. any solution u guys?

Bilge said...

Thanks. Whenever I search for things I cannot find the accurate answer but this time it was.

Pete said...

Thanks for the info. Much appreciated.