4

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?

Bob Hyman
  • 51
  • 2
  • 6

4 Answers4

1

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

Johnathan May
  • 11
  • 2
0

I see that nobody has answered yet, so I'll give you my 2 cents.

Here's what I think I would try:

  1. Create a new OU called SQL Servers
  2. Move the SQL computer object to that OU
  3. Create a new GPO called SQL Logon As A Service
  4. Add everything from the Default Domain Policy
  5. Create a managed service account in Active Directory
  6. Add the managed service account to the Logon As A Service list

Here are a couple links I found that might also help you out:

Configure Windows Service Accounts and Permissions
SQL Server service account Windows privileges and rights

Aaron D
  • 303
  • 3
  • 12
0

What I finally ended up doing was: remove the GPO that applied logon as service right, and rely on individual machines to grant the right as needed when installing services on those machines.

This is pretty self-regulating solution, since services.msc will grant the right from the properties page when you set the service account password. And something equivalent apparently happens when you install applications that need a service.

Bob Hyman
  • 51
  • 2
  • 6
-1

Go to your GPO and add "NT SERVICE\ALL SERVICES". This will allow all virtual service accounts to run.

Ray Wilkerson
  • 1