Group Managed Service Accounts

Choosing the service accounts to run the SQL Server services under is often something to consider when doing install. Of late I've taken to using domain accounts called Group Managed Service Accounts. These combine the security advantages of the local virtual accounts, with the resource and management advantages of regular Active Directory accounts, in that they:

•     have a system managed password
•     undergo regular password changes with no downtime
•     can be granted access to network resources, e.g file shares, other SQL Servers etc.
•     can be added to domain level group policy objects

There are some disadvantages, though these are only really encountered in the setup phase, with some particular requirements and extra steps needed to get them up and running. 

Through the rest of this post I'll outline how to create these accounts, and the specific requirements and actions needed to get them up and running, including a few gotchas that have caught me out in the early stages of using these.

Step 1 - Creating the AD account

First step is to create the account in Active Directory, which would usually be done by one of the AD Admins, but there are some specific requirements for the setup which they'll need to consider.

The basic steps are:

1.     Create a group that will have permissions to use the service account
2.     Create a new account as a group managed service account with:
1. the name of the AD group containing the names of computers that are allowed to use the service account
2. the accepted Kerberos encryption types. This is very important for environments where the default allowed ciphers have been changed, usually removing the RC4 cipher (which is a good idea)
3.     Grant permission for the service account to self-register SPNs

This can all be done in one PowerShell command for the account creation, and a second one to grant SPN registration. To create the account, use the format:

New-ADServiceAccount -Name <service account name> -DNSHostName <fully qualified service account name> -KerberosEncryptionType AES128, AES256 -PrincipalsAllowedToRetrieveManagedPassword <AD group name>


 

Note that if the KerberosEncrytionTypes don't match what's configured in your environment then the account will still create properly but will cause issues when trying to configure on the server. If you know that your environment is using all cipher types then this parameter can be left out, but the majority of environments I've worked in lately are disabling the RC4 cipher.
 
Service Principal Name (SPN) registration of the service and account is vital if you want to have kerberos authentication ... and you do want kerberos working. SPN registration can be done when the account is created, but it's better to give the account permission to self-register SPNs, particularly if the account will be used across multiple SQL Servers. SQL Server will then register the correct SPNs on service start. Note that this is only needed for the service account that runs the SQL Server service, not for the agent or other services.

The command to allow the account to self-register SPNs is (use the correct CN name for the service):



dsacls "CN=<service account cn>" /G SELF:RPWP; "servicePrincipalName"


E.g.



dsacls "CN=SQLgMSAtestSQLServerService,CN=Managed Service Accounts,DC=example,DC=com" /G SELF:RPWP; "servicePrincipalName"




For more information on the New-ADServiceAccount cmdlet, check out https://learn.microsoft.com/en-us/powershell/module/activedirectory/new-adserviceaccount

 

Step 2 - Configuring the Server

Once the account is created there's a bit of configuration required on the SQL Server.

Prerequisites

Prior to starting configuration we need to have the PowerShell Active Directory cmdlets installed. This can be done through Server Manager, or from an elevated PowerShell session (right click, Run as Administrator) with the command

Install-WindowsFeature RSAT-AD-PowerShell

We also need the outbound firewall/security group port open to the Active Directory Web Services from the SQL Server. This is 9389 unless specifically changed.

And lastly, the server needs to be a member of the group that has permission to retrieve the managed password. Before the group membership can take effect either the server should be rebooted, or the system Kerberos tickets cleared using the command
klist -li 0x3e7 purge

Installing the Service Accounts

Next, we need to install the service accounts on the server. To do this we'll need to log onto the server as an administrator, then run the following PowerShell command in an elevated PowerShell session: 

Install-ADServiceAccount <accountname> 

We can test the service account install by running the following

Test-ADServiceAccount <accountname>

Configure the Services

Once the service account is installed the SQL Server service can be configured to use this as the log on account. This is done by adding the account with a $ sign on the end and a blank password in the service properties in SQL Server Configuration Manager. Note always use the SQL Server Configuration Manager to do this rather than the standard Windows Services msc, as the SQL Server manager will do some extra bits in the background that the standard Windows one doesn't.

 

 Click Ok and we're done. The service will restart if already running and we'll see the password fields automatically filled in.

Extra Configuration

Using the SQL Server Configuration Manager to do the services change means that the service accounts will automatically get added into the correct groups and SQL Server roles, but if there's any custom permissions that had been granted to the previous service accounts, such as file system permissions, then these will need to be updated as well. 

As a side note, granting Windows file system permissions is better done by granting access to the local NT Service\SQLSERVERAGENT and NT Service\MSSQLSERVER virtual accounts as the new service accounts automatically get included in these so future changes mean not having to worry about these permissions

 Troubleshooting

• Error trying to contact the server when trying to install the service account. This is likely due to the AD Web Service port being blocked. Try running a simple command to get your AD user details
  Get-ADUser <your username>
  
  
• Test-ADServiceAccount cmdlet returns false, and/or trying to start the service with the gmsa account gives a password error. This is because the computer isn’t part of the group that has access to retrieve the password for the specified gmsa, or the computer hasn’t picked up the membership.