December 31, 2008 at 11:53 am
OK I have an odd one that I came across this morning and I have the answer to the "HOW" but I don't have the answer to the "WHY"
I had a request to FTP a user DB to a vendor for analysis. They requested the .mdf and .ldf files, so I did a backup and restore to the test server so I could then detach the DB and zip up and encrypt the files.
Initially I was getting winzip errors on the specific files, saying that winzip couldn't access the files...
Confused, I attempted to moved the files but got an access denied message...which struck me as odd as I had JUST detached them so the files should have been closed (which they were)...
Thinking something was randomly holding the files, I rebooted the test server cause I didn't want to screw around with it anymore...
After a reboot, the same symptoms...
Now here is the odd part...
1. I connected to the server using SSMS which was installed locally on my laptop and I am logged in with my domain account (DOMAIN\ACCT_A)
2. I was also remoted into the server (via dameware) and logged in with the server with a different server domain account (DOMAIN\ACCT_B) which is a local admin on the server.
3. Prior to detaching the database, I did a right-click and properties on one of the files (.mdf). I went to security and it showed all the security for the file which includes the "administrators" and the Server Domain account (both of which have full rights to the specific file & drive)
4. I detach the database (right-click -->detach) from my laptop and the db detaches just fine.
5. However, now I can't do anything via the remote connection when trying to manipulate the detached files ( get access denied)
6. I do a right-click --> properties --> security and boom...the security has been changed (FOR THIS ONE FILE) to only allow my domain account rights on this file (DOMAIN\ACCT_A)
SO...as a result of #6, now when I am connecting to the server through a remote tool (dameware) and am logged in with the server domain account DOMAIN\ACCT_B and attempt to zip the files that I just detached I get a "Access Denied" error message...because the security has somehow been stripped out of the .mdf and .ldf files and replaced with just my domain account DOMAIN\ACCT_A which was what I was connected to the server as when I did the detach.
My domain account is in the local admin's group on the server, it is windows 2003 sp2, the SQL server is SP2
This is the first time I have actually had to "detach" a db in 2005 so it is the first time I have come across this issue...and I am curious if I am screwing something up or have an incorrect setting somewhere because this seems totally wrong to me.
As an additional test, I did the "detach" process also while remoted in on the server, and being logged in with the server service account DOMAIN\ACCT_B I did a detach of the specific database in question and it did the same exact thing, except it stripped out all the security on the two files and replace that with only rights for DOMAIN\ACCT_B
The fact that it replaced the security with my domain account (DOMAIN\ACCT_A) leads me to believe that the SQL server decided that it needed to change the file security for some reason...
Sorry for the book...just thought I would try to explain this as I don't know why this is happening.
Thanks in advance,
Lee
December 31, 2008 at 3:08 pm
Actually, SQL Server 2005 handles permissions differently than SQL 2000 when detaching dbs. Under SQL 2005, the file owner *changes* when the db is detached. This was not the case under SQL 2000.
[Then, when the file is (re)attached, the file reverts back to the original owner/permissions.]
SQL Server 2005 does a number of things with file permissions, particularly on detach/attach that are not necessarily well know or documented:
1. SQL Server sets file permissions individually vs. inheriting from the folder
2. SQL Server 2005 changes the file permissions for an MDF file on detach and then reverts on attach.
For a remote user (e.g. using SQL Server Management Studio on machine X to connect to a SQL Server instance on another machine - machine Y) who is not a Domain Admin nor is an Admin on machine Y, you need someone with Admin privileges for machine Y to change the file permissions on the .MDF (and presumably LDF) in order to allow the MDF/LDF to be moved to another machine.
This is not necessarily foolproof as there may be other machine to machine differences that cause the moved file to possibly have trouble being attached on the destination machine (X).
HTH,
MJ
January 6, 2009 at 9:40 am
Thanks for verifying this MANU. I would assume there is some sort of logic behind this behavior and I am just not seeing the "Advantage" of stripping out the file level security when the database is detached.
Lee
January 6, 2009 at 10:32 am
This may sound like a weird question, but why didn't you simply take a full backup of the database, zip up the backup, and ship the (encrypted) zipped file with instructions for decryption and restore? In that way you would circumvent the SQL Server security issues entirely and would, I believe, be a much simpler process to providing the database.
Once restored by the vendor, then they could perform their analysis. IF they couldn't find anything, then perhaps the problems you're experiencing with the product may not lie within the product, but the interface between product and environment?
Of course, then we wouldn't have learned about the not-well-known security behavior of SS2005, so thank you for posting!
January 6, 2009 at 10:53 am
steve smith (1/6/2009)
This may sound like a weird question, but why didn't you simply take a full backup of the database, zip up the backup, and ship the (encrypted) zipped file with instructions for decryption and restore? In that way you would circumvent the SQL Server security issues entirely and would, I believe, be a much simpler process to providing the database.Once restored by the vendor, then they could perform their analysis. IF they couldn't find anything, then perhaps the problems you're experiencing with the product may not lie within the product, but the interface between product and environment?
Of course, then we wouldn't have learned about the not-well-known security behavior of SS2005, so thank you for posting!
You and I think alike...I had inquired about that the second they "requested the .mdf and .ldf" vs. just sending them a zipped & encrypted backup.
After waiting 2 days for a reply from the company on "My question" I said enough already and did what they originally requested and sent the files via FTP.
It was a couple more steps, and I didn't mind because the DB in question was only like 400 mbs and most important I wanted the task off my plate...
It was a learning experience while hopefully others can learn from and I will look out for in the future...
Lee
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply