February 22, 2023 at 5:17 pm
I have a system with two disks in the C: drive and four disks in a RAID 5 setup for the D: drive. A few weeks back, one of the D: disks failed. No sweat, ordered another, plugged it in and let the RAID rebuild overnight. All seemed to be fine, I can log in no problem, and external shares work, but SQL Server won't start. It throws system error 5, Access denied on any attempt to touch the existing .mdf files.
I have permissions set up as before, and when logged in, I can see and browse all pertinent folders. No read-only flags or anything like that I can see. Tried repair from the installation app, no improvement.
I can start it from the command line with some start-up trace flag enabled (I forget which one now), but when I connect to the instance started that way with SSMS, none of the databases are visible.
I had some problems with attempts to set up FileStream just before the disk failed, and it's possible that some dregs of that are still around, causing trouble. But I can't clear those out until I can start the service.
My suspicion is that SQL Server is maybe suddenly trying to start under a different account. Is that possible? How would I find out what account it is using, and how do I make it behave if that is the problem? Or if not that, what else might be the problem?
February 22, 2023 at 9:20 pm
I don't have an answer. "Bumping" this bad boy for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2023 at 9:57 pm
It’s been a while since I last did sql admin. SQL server configuration manager will tell you what account it is using. If it is a domain account ie AD, make sure it is not locked out, a sure way to stop the server running. Check the server log it should give you the reason.
Far away is close at hand in the images of elsewhere.
Anon.
February 22, 2023 at 10:36 pm
It does. As I wrote in the original post, "It throws system error 5, Access denied on any attempt to touch the existing .mdf files." That's right from the log.
What I don't know is why that is happening.
February 23, 2023 at 8:08 am
Have you verified that “NT Service\MSSQLSERVER” has full control to the root folder of the mdf files with the needed inheritance enabled downstream?
February 23, 2023 at 1:32 pm
There was no NT Service, but there is a Service. I gave that full access to the entire D: drive and the system recovered.
But I'm a little fuzzed on such a sledge-hammer approach. Was this really the right solution? Would it have been better to just give this account permissions on the folders containing the databases, instead of the entire drive (which contains tens of thousands of other files)? Is this the account the SQL Server service uses for its work? If so, what would have made all the permissions go away over a simple thing like replacing a RAID member? Was it using a different acount before the failure? If so, how would I discover what that account was? And why did it suddenly switch to using this account? Can I make it go back to the former account and rescind this new blanket set of permissions?
I'm very confused...
February 23, 2023 at 4:08 pm
"NT Service\MSSQLSERVER" or "NT Service\MSSQL$<instancename>" if using a named instance, should exist as it's what is used to ensure that if you change the actual service account then you don't need to remember to go an change all the permissions to the new service account etc.
It needs the permissions at whatever is the root level to the database files
So if you have D:\SomeExcelStuff\1.xlsx, D:\SQLData\DB1.mdf, you would just want to grant it full control to D:\SQLData, it doesn't need D:\SomeExcelStuff unless you ever needed to read the excel stuff into SQL etc.
February 23, 2023 at 6:13 pm
Again, NT Service was not an option.It was not offered, and when I typed it in manually, I got an error message. The account named only Service worked.
So you say I should allow this Service account access ONLY to the actual folder holding the .mdf and .ldf files, and nothing else?
It's an older server, if that makes any difference - 2008 vintage, with corresponding OS.
February 24, 2023 at 2:45 pm
Ah OK, 2008 that may explain it then.
Yeah the account should just need access to the folder(s) holding your data files and nothing else.
February 24, 2023 at 5:19 pm
This was removed by the editor as SPAM
February 28, 2023 at 10:48 pm
Okay, thank you. I'll try messing with the permissions until I get it trimmed down to what looks sensible,
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply