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:
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 !
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...
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 ;)
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
Saved my bacon. Thanks dude.
Thanks a lot Tony Halperin for your article, Which gives me solution after i searched so many articles.
Thanks sir,its really work fine...
Mehboob Yousafzai
I am having a same issue. But for me it's not working at the moment. any solution u guys
Very good solution, is it also neccessary and working in an SQL 2008 enviroment ?
thank you for your information, it worked both version 2005 and 2008.
Thanks a lot, it worked for me
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?
Thanks. Whenever I search for things I cannot find the accurate answer but this time it was.
Thanks for the info. Much appreciated.
Post a Comment