September 2, 2005 at 7:35 am
Hi
I'm trying to remove the SQL Server and SQL Agent service accounts from the local administrators group but following removal the SQL Server service fails to start.
The errorlog always contains the following info:
2005-09-02 13:48:12.47 spid5 Clearing tempdb database.
2005-09-02 13:48:12.54 spid5 Encountered an unexpected error while checking the sector size for file 'd:\apps\Microsoft SQL Server\MSSQL\data\tempdb.mdf'. Check the SQL Server error log for more information.
2005-09-02 13:48:14.26 spid5 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
2005-09-02 13:48:14.26 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.
The service account has the logon as a service right and it's been been given full control over the \MSSQL data, log and bin directories and relevant registry keys. It starts when I add the service account back to the local admin group.
I'm perplexed - anyone got any ideas?
September 2, 2005 at 8:09 am
All of the documentation that I have seen on setting Windows Security when the SQL Server and Agent Accounts are not in the local administrator group is incorrect including the pages on the Microsoft site.
I have had success using Enterprise Manager.
From http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.mspx
SQL = Scarcely Qualifies as a Language
September 2, 2005 at 8:12 am
To work arround your problem, I would suggest that you give that account full rights on the folder that SQL Server is install on and the folder for Log, Data and Backups.
Once you set up the file permission, you could remove it from local administrators group. Please be aware that once you remove this account from local administrator, you could no longer log into that server using that account.
mom
September 2, 2005 at 9:16 am
The account has full rights on the SQL folder and subfolders (and tempdb.mdf) and relevant regostry keys so that's not the problem.
The Enterprise Manager advice sounded promising so I changed the SQL Server service account in Enterprise Manager and, sure enough, it spent a few minutes assigning permissions to relevant directories and registry keys - so far, so good. I still could not restart the SQL Server service though - same error message about being unable to find the sector size for tempdb.mdf.
I think I'll park this for now and come back in a week or 2 - thanks for your help.
September 2, 2005 at 10:41 am
Two questions, based on this statement:
WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.
1. were any of the tempdb files deleted?
2. did you try to restart the server with the -f option?
-SQLBill
September 5, 2005 at 7:34 am
I have to log onto the Server desktop as the 'ordinary' (not local admin) user to test this - currently speaking to the the Server admins to get this set up. Will keep you posted but don't hold your breath.
September 5, 2005 at 11:06 am
I've got somewhere with this now....it seems to be a result of inadequate permissions granted to the SYSTEM account, I will speak to someone here tomorrow.
September 5, 2005 at 8:17 pm
Hi all, I totally agree with comments made by Carl above. I have had huge issues with this even after following all MS articles. A support call to MS returned the following advice.
'Yes there are issues with security settings for SQL server interacting with the under Windows security layer and even though you may tighten security and it works today things may fail after applying the next SP.'
Yeh real helpfull 'not'. Even things such as starting the agent and sql services under different accounts does not behave as documented. As much as I would like to follow best practice I recently gave up trying to figure out issues with multiple named instances on a server which refused to work when client connections were via TCP/IP. Solution was to make the sql service account member of local admin. I can sympathise with Peter, even if he has performed a vanilla install, the individual server, domain, Active directory setup can influence SQL server behaviour.
Derek
September 6, 2005 at 1:25 am
So it could all go wrong again when I apply the next SP - great! I'm coming to the conclusion it's just not worth the hassle and the best thing is to leave the SQL Server service account as a local admin.
One irony is the MS Baseline Security tool gives my 'security tightened' machine a worse score than before - all the permissions granted on the SQL Server folders and registry keys have red crosses beside them! Oh well it's been a learning experience.
PS We also have problems communicating with certain SQL Servers via TCP/IP - something to do with Windows security patches apparently. We force the clients to use named pipes using the client connectivity tool.
September 8, 2005 at 1:42 am
I solved the problem by giving the SQL Service account read access to the root of the drive that hosts the TempDB. Now my SQL instance runs under a non admin account.
September 8, 2005 at 2:47 am
Thats great news, why though did the service account not get access to when you changed the service acct via EM. I thought all privileges would have been set by doing this??
September 8, 2005 at 4:05 am
Hi
I'll try granting read-only access to the Service Account from root downwards (That post wasn't mine by the way). EM only grants access to the MSSQL folders themselves.
September 8, 2005 at 5:39 am
Eureka...granting read-only access on the relevant root folder to the SQL Server Service account has fixed it and I can now run SQL Server under a non-local admin account. Good bit of lateral thinking there, ICT.
Thanks for all your help everyone.
September 8, 2005 at 7:24 pm
Thanks for the final follow-up, I have a few I want to try that on to see if I can remove local admin group membership
September 15, 2005 at 10:06 am
You guys solved my problem. Thanks for the help. MS sucks! This isn't documented anywhere!!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply