Group-managed service accounts and SQL Server

One of the best things we've done over the past couple of years with respect to Windows Server administration is replace most of our service accounts with group-managed service accounts (GMSAs), the passwords for which are handled by the domain controller's key distribution service (KDS). This means we no longer need to have a separate SQL Server and SQL Server Agent service account pair for every data server (which necessitated a lot more password maintenance for us) but rather can use the same GMSA pair for every data server in a particular domain.

Here are the basic steps we use to set up GMSAs:

1. In PowerShell, add the KDS root key to Active Directory (AD), which may not be necessary as this task only needs to be done once for each domain:
   
   PS C:\Windows\system32>Add-KDSRootKey –EffectiveImmediately
   
   
2. Have a system administrator create a new AD security group in [domain.int] | [Resources] | [Groups] | [User Groups], for example, retrieveMSASQLServer. Computer accounts in this group are granted the ability to retrieve GMSA passwords from AD. All AD computer objects that will be using a GMSA must be included in this group.
3. Have a system administrator create another new AD security group in [domain.int] | [Resources] | [Groups] | [User Groups], for example, ADSValidatedWriteToServicePrincipalName. Service accounts in this group are granted the “Validated write to service principal name” permission. Only the GMSA that runs the SQL Server engine must be included in this group.
   
   
4. Add permissions for the GMSA that runs the SQL Server engine to “Write all properties” on “This object only” for itself.
   
   
5. In PowerShell, create the GMSAs:
   
   PS C:\Windows\system32> New-ADServiceAccount -Name "svc_gmsa_sql1" -DNSHostName "svc-gmsa-sql1.domain.int"
   -PrincipalsAllowedToRetrieveManagedPassword "retrieveMSASQLServer" -Enabled $true
   
   The [DNSHostName] parameter is not a valid DNS host and cannot include underscore characters. However, it is required and must be unique, so use the GMSA name (and use dashes instead of underscores).
   
   
6. Remember to remove any existing SPNs assigned to each SQL instance, for example:
   
   setspn -D MSSQLSvc/aSqlServerExample.domain.int domain\an-old-service-account
   
   
7. Replace the SQL Server service account logins with GMSAs using the SQL configuration tool, and then restart the services.
8. Check that the physical server hosting SQL Server automatically creates the necessary SPNs when the engine service is restarted. You can find this information in the SQL error logs or the Windows Event logs.
   
   
9. Configure an SPN for each Availability Group listener:
   
   setspn –S MSSQLSvc/egVNN1 domain\svc_gmsa_sql1
   setspn –S MSSQLSvc/egVNN1:1433 domain\svc_gmsa_sql1
   setspn –S MSSQLSvc/egVNN1.domain.int domain\svc_gmsa_sql1
   setspn –S MSSQLSvc/egVNN1.domain.int:1433 dems\svc_gmsa_sql1
   
   Here, egVNNn is the virtual network name (VNN) for the AG listener, domain is the domain name (e.g., "corporate.int"), and svc_gmsa_sql1 is a GMSA name. As mentioned previously, we use two GMSAs: one for the SQL Server instance and one for the SQL Server Agent.
   
   
10. Configure computer objects in AD to support Kerberos delegation. This includes physical nodes and AG listener objects (indicated by the VNN).
    
    
11. Reboot servers.