I would like to use the default SQL Server setup that runs SQL Server service with virtual account NT SERVICE\MSSQLSERVER. That ensures my SQL Server has limited access on its own machine and no access to network resources if it should be hacked. However, our domain also has a group policy that sets Logon As A Service rights for several other (domain).
You have probably already guessed, or maybe even experienced, my problem:
When the domain GPO is applied to the server (at least daily), it undoes the Logon As A Service rights assignment that was done by SQL Server setup (or SQL Server Config Mgr, or other local machine policy config). The stage is then set for error "The service did not start" error the next time SQL Server service is restarted, which might be hours or months later.
Here's what I know so far:
- I cannot add the virtual account to the domain GPO because it's a machine local SID.
- Domain Group Policy overrides local machine group policy, so I'm going to have to do something about the domain group policy.
It is a thousand pities the User Rights Assignment element is under Computer Settings\Windows Settings\Security Settings\Local Policies Settings, because it cannot reference local machine accounts. Maybe it's even a bug.
Here are approaches I'm considering:
- I could eliminate the domain GPO element that applies Logon As A Service right, and do this operation via local group policy (on a bunch of servers).
- I could move the server into an OU that does not have this GPO element applied. Currently the offending GPO is my default domain policy, so I'd have to refactor things.
- I could run some tool on each SQL Server machine to reestablish the Logon As A Service right either after domain group policy runs or frequently enough that I'm rarely caught out.
Does anyone have other suggestions or working solutions to offer me?