August 16, 2016 at 3:58 pm
We had a Windows 2008R2 server with SQL 2012 that crapped out during a hardware upgrade.
We've attached the same SAN drives to a new Windows 2012 R2 server and installed SQL2014. The old SQL instance was shut down cleanly before the instance died so the MDF & LDF files should be intact.
I'm trying to attach them to the new 2014 instance but I'm getting the error "Unable to open the physical file "E:\SQLData\User\dbname.mdf". Operating system error 5: "5(Access is denied.)".
As far as I can tell, the SQL Service Account has full control on the files and the folders. Any ideas?
August 16, 2016 at 4:29 pm
A couple of things to try, check would be:
Double check the permissions and make sure the effective permissions have the service account has full control.
Make sure the file attributes didn't change and that they are not marked as read only
And then do Run As Administrator when you open SSMS.
Sue
August 16, 2016 at 4:51 pm
Check, Check, Check and no joy.
The unusual thing in this circumstance is that the network drives were previously attach to an OS that no longer exists. I'm wondering if there is some sort of signature an OS puts into a file that is not visible on the permissions tab.
I've created a couple new databases in the same folders and made sure the old MDFs have all the same permissions as the new ones, but SQL still can't access them.
Edit: Sometimes when I edit permissions on these files, I will get an "Access is denied" error, when I exit & go back in, my changes appear to take. SQL still gets an OS 5 error when running Create Database FOR ATTACH command. Something is really squirrely here.
August 16, 2016 at 5:15 pm
The only thing I've ever seen like that is the file attributes getting marked as read only. But I've often seen the security changes, ownership changes on files when moved around.
And are you using Domain accounts or one of the local accounts for the service?
Did you check the files specifically or just the folder that files are in?
Are you using different directories for the log and data files?
You could also try using xp_cmdshell on a few of the good data files and some of the problematic files in the directory just to see if you are getting the same errors. Just something like -
sp_configure 'xp_cmdshell' , 1
go
reconfigure
go
xp_cmdshell 'dir "E:\SQLData\User\dbname.mdf"'
go
sp_configure 'xp_cmdshell', 0
go
reconfigure
go
Sue
August 16, 2016 at 5:29 pm
Sorry...I missed your last edit. What permissions are you changing?
And I forgot to ask, are you also looking at who is the owner of the files?
Sue
August 17, 2016 at 10:24 am
And are you using Domain accounts or one of the local accounts for the service?
Domain service account
Did you check the files specifically or just the folder that files are in?
Yes. BTW if I put a random character into the path or the file name of the CREATE DATABSE WITH ATTACH command I get a can't find path or file message instead of access denied, so I know the path & filename is correct
Are you using different directories for the log and data files?
Yes. According to the documentation, secondary data files & log files don't need to be specified unless they are in a different path than they were before. This server & instance was built with the same paths. I would also expect a different error if another data file or log file couldn't be located.
I've created two new databases in the same folders (performed as expected), then set the permissions of the old files to be the same as the new ones. When I was changing the permissions on the old files, I got an "access is denied" error but the changes appeared to take and now appear correct (same as the files for the new databases). I also set the owner to be the same (Domain service account).
The SQL service on the old server was not running when the server died, but no detach db command was run. We also did not have TDE on the old server either, just to eliminate that possibility (thought I know that would produce a different error message).
August 17, 2016 at 10:29 am
right click each of the database files and go to the security tab, what accounts are listed and what permissions?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2016 at 10:55 am
On the newly created database files, which are currently online:
Owner is SQL domain service account.
MSSQLSERVER
OWNER RIGHTS
Local Administrator Group
All have FULL control.
On the old files (in the same folders):
Same as above plus
SYSTEM
SQL domain service account
August 17, 2016 at 11:30 am
It sounds like it's the issue listed here even though it's only been reported through version 2012:
Read through all the comments though as I am not convinced it's just an inherited permissions issue as the title states. The comments indicate otherwise as well. That article also refers to another pretty good discussion on this on stackoverflow at:
http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database/5589045
It's probably worth trying some of those workarounds for now.
Sue
August 17, 2016 at 1:29 pm
logon as a windows admin and take ownership of the files. Once done, remove all ACLs and add back the sql server service account with full control
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 22, 2016 at 12:04 am
This was removed by the editor as SPAM
August 23, 2016 at 12:52 pm
I have a similar situation to the OP ... has there been a resolution?
In my case, trying to recover what I believe were healthy MDF and LDF files from a HDD that was pulled from an old Windows 7 box that has since been upgraded to Windows 10. Unfortunately, the tech that did this did not know it was necessary to detach first (or didn't realize the user even had local SQL databases running for some local apps). Since this was a workstation and not a server, I'm not feeling optimistic that I'll find any recent backups.
I hooked the old HDD to the new Windows 10 box running SQL 2014 and I see all of the MDF and LDF files on the old drive:
- Tried to copy the MDF and LDF files to any HDD (portable USB, new C-drive, etc) - access denied.
- Took ownership of the entire DATA folder and all subfolders/files on the old HDD and tried to copy - access denied.
- Tried to attach to the MDF through the new SQL 2014 instance - access denied.
I've been able to copy other random files from the old HDD to the local drives (C, USB, etc.) without problem so I'm not sure why the old SQL folders are acting different.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply