Unable to Attach an Offline Database - by Jean-Nicolas BERGER

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 800222 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/11/2013 2:03:22 AM
Access Restriction Public

Description

After having detached an Offline Database, I get an error 5120 (Unable to open the physical file ...) when trying to re-attach it.
Sign in to post a comment.
Posted by Jean-Nicolas BERGER on 11/12/2013 at 11:57 PM
Hi,
Sorry for my late reply.
As described in the documentation http://msdn.microsoft.com/en-us/library/ms189128.aspx/html, you’re right, detaching an offline database will cause the database files permissions being unchanged (SQL Server service + Local Admin), while detaching an online database will cause the files permissions being replaced by only a full control to the current user account.
So it’s well documented (SQL Server 2008R2 and previous only, this documentation doesn’t seem to exist for SQL Server 2012), and it may be considered “By design”.
Please also notice that when running SSMS “As an administrator” and attaching a database that was offline when previously detached, the newly attached database is now Online. This point is not clearly reported in the BOLs, but it might be understood as the database might have to be modified (Log rebuild, Version upgrade …).
Regards.
JN BERGER
Posted by Microsoft on 11/12/2013 at 3:58 PM
Thanks for reporting this. As reported earlier, SQL Server cannot set ACLs on offline files during detach, hence the subsequent attach fails. Please grant explicit permissions to address your issue if you are detaching offline files.

Thanks,
Ajay Jagannathan
Senior Program Manager
Posted by Microsoft on 9/12/2013 at 2:57 PM
Please see the section on detaching and attaching files here.

http://msdn.microsoft.com/en-us/library/ms189128(v=sql.105).aspx/html

For OFFLINE databases the files are not open so they cannot have their permissions changed so the system administrator will have to explicitly grant the same permissions that would have been given had the database been ONLINE when attached.

Thank you.
Posted by Microsoft on 9/12/2013 at 2:19 PM
The user who is connected and doing the attach must also have permissions on the file. Attach will impersonate them and make sure they are attaching files they have permissions to. You should compare the permissions of a detached ONLINE database to those of a detached OFFLINE database to see.

Thank you.
Posted by Jean-Nicolas BERGER on 9/11/2013 at 10:07 AM
Hi,

As the database was created in the first steps of the process, the account of the Database Engine has the required rights on the files.

Please also notice that the same "Steps to reproduce" but without the "ALTER DATABASE ... SET Offline" works fine, with the same filesystem rights ...

Regards.
JN BERGER
Posted by Microsoft on 9/11/2013 at 7:43 AM
Hi Jean-Nicolas,

The OS error 5 is usually a permissions error. The system administrator needs to make sure that the user accound issuing the attach command has permissions on the files being attached. This is done by SQL Server if you detach, but not for taking a database OFFLINE.

Operating system error 5: "5(Accès refusé.)".

Thank you.
Posted by Jean-Nicolas BERGER on 9/11/2013 at 2:05 AM
Restarting the Database Engine before attaching doesn't solve the issue.