Windows – How to manage logon as service right for virtual account in face of domain group policy

group-policyservice-accountssql serverwindows

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?

Best Answer

If you add the Group Policy Management Console to the SQL server in question, you can modify the Group Policy setting you're talking about to include "NT Service\MSSQLSERVER" or any of the other "NT Service\xxxx" account names. If you open the GPO on another server, you'll see a big long SID instead of the pretty "NT Service\xxxx" alias. You can add gpmc.msc from within an admin powershell session with:

Install-WindowsFeature GPMC

A solution that works even better for adding these "NT SERVICE\xxxx" accounts is to add the built-in "NT SERVICE\ALL SERVICES" group instead - then you don't have to add them individually, and it will show up in a human readable format on other systems as well when looking in GPMC. I ran into this when working with an Azure AD Sync service, and then again with the Windows Internal Database that gets installed for RDS deployments. I could only add the "NT SERVICES\ALL SERVICES" group on a machine that had some of these "NT SERVICE\xxxx" accounts already. A couple of articles that were helpful in explaining how this works are here:

Does Install-ADSericeAccount modify "NT SERVICE\ALL SERVICES" https://docs.microsoft.com/en-US/windows/security/identity-protection/access-control/security-identifiers

As Aaron D said, it's probably best to create a service account in Active Directory though instead of using the built-in, local NT Service accounts. Here are a couple of additional articles about using Active Directory managed service accounts if you want to go that route:

Using Managed Service Accounts With SQL Server
Configure Managed Service Accounts For SQL Server

Related Topic