So, you’re making a valiant effort to improve your security posture in your SQL Server estate and have decided to move to Group Managed Service Accounts (gMSAs). But, after switching from your local account running SQL, or your domain account, you’re staring at Configuration Manager telling you that “It could not start the service in a timely manner”. After checking the System Event Logs in Event Viewer, you’re now looking at an error message saying “username or password is incorrect”. This error message is incredibly misleading when dealing with gMSAs running your SQL Server, and I’m going to walk you through the nuances I experienced so you can get up and running again.

Quick side note: A lot of this is assuming that your AD Admin has already properly deployed the gMSA account in your environment. If you can, make sure to work with them (or someone who is comfortable functioning in Active Directory) when making these changes.

Why gMSA Accounts Are Just Better

Out in the wild, I’ve seen many setups when it comes to what is running a SQL Server Engine and Agent service. I usually see one of the following:

• Local account: The good news here is it’s simple to set up. However, local accounts lack centralized identity management, are difficult to use securely across networked resources, and require manual password management.
• NT AUTHORITY\SYSTEM: With these, you don’t have password management, but again, excessive local privileges, same network limitations as local accounts and generally speaking, security teams don’t like these.
• NT SERVICE\MSSQLSERVER: Now we’re getting into a bit better territory. You have automatic password management by Windows, has the minimum permissions SQL needs out of the box, and it’s the default for SQL install accounts. However, this authenticates to the network as the machine computer account.
• Domain account: This is usually the best tier and most consistently used. You have full network access and Kerberos support, a named identity, works across multi-server environments and AGs better, but you still have manual password management, and if you need to rotate those passwords it requires updating every service that uses it.

Now, gMSA accounts are what take domain accounts to the next level. These give you all the benefits of domain accounts while removing the headache of password management. Active Directory owns the password portion and rotates the password every 30 days by default. You will never know, or need to know the password of the gMSA account. Another major benefit of gMSAs is automatic SPN management for Kerberos authentication, assuming the account has the appropriate permissions to register SPNs in Active Directory.

Setting Up A gMSA Account

The biggest gotchas lie within the groundwork that is needed when leveraging a gMSA account. This is how my organization has it set up so that we can leverage gMSAs in our environment. First off, we have a security group with the name of the gmsa account (example: gmsa-sqlxxx-x). The actual gMSA object in AD is configured to allow this security group to retrieve its password which means any computer account that lives in this group is authorized to do so. This is the attribute where the security group gets assigned to in the actual gMSA object in AD:

• PrincipalsAllowedToRetrieveManagedPassword

Then, you need to put the computer name account (the server name) in that group.

Note that the gMSA object itself is different and separate than this security group described above. This method is purely organizational for us and just allows us to drop computers into the appropriate security group. When the computer account goes into the security group, that is what grants password retrieval rights.

Next, you’ll need to install RSAT tools on the server you’re trying to leverage the gMSA account on in order to leverage the ActiveDirectory PowerShell module. You can do this by running:

Install-WindowsFeature -Name RSAT-AD-PowerShell

Special note: You might want to remove this windows feature after you get this set up. Some organizations prefer to remove the RSAT AD PowerShell tools after and not keep them on SQL boxes long term because of potential exposure on production servers.

Once that is installed, you’ll then need to restart the server (or flush Kerberos tickets in the context of the server account with a klist purge, but a restart is easier to handle). The reason for this is because the next steps retrieve the gMSA password from AD, and AD checks whether your machine is authorized via Kerberos. Your computer’s Kerberos ticket has a snapshot of group memberships prior to you adding it to the security group that has the proper gMSA related permissions.

Then, install the gMSA account on the server. You do this by running the following:

Import-Module ActiveDirectory
#Note no $ appended when doing this
Install-ADServiceAccount -Identity 'gmsa-account-name'
Test-ADServiceAccount -Identity 'gmsa-account-name'
#This should return "True"

If the above returns “True” after you test it, that lets you know you’ve done things right.

Before You Make The Switch

Before you make the switch in configuration manager to have your SQL Server Engine and or Agent services run under the gMSA account, you need to make sure of the following:

The gMSA account has explicit permissions on your SQL related drive paths (user data files, log files, tempdb, etc.). Without this, SQL won’t be able to start. Some admins temporarily grant local admin to the account running SQL during testing, but least privilege permissions are recommended long term.

Making The Switch

This part is easy. Just go into configuration manager (Always use configuration manager when making these types of changes. Do not use services.msc. This ensures the proper metadata is updated correctly), click on the SQL Server Services tab on the left, right click on the SQL Server Engine service, and hit Properties. This will bring you by default to the Log On tab and you can then replace the existing account currently running SQL with the gMSA (NOTE: you have to enter it as domain\gmsa-account-name$). It will prompt you to restart SQL when done and hit OK.

Repeat the steps above for the SQL Agent service.

The Availability Group Gotcha

One thing that will ruin your day when you are promoting this change through your environment to production, is if you have an AG. The same steps above apply to switch the account running SQL, however, there are some post cleanup tasks that you need to do to make sure that your AG is still synchronizing with its replicas.

Because you just changed the account running SQL to something else, you must now explicitly grant connect permissions to the HADR endpoint to the new account running SQL on ALL replicas (even primary). You’ll see this in the SQL Logs if you don’t:

Database Mirroring login attempt by user ‘domain\gmsa-account-here$.’ failed with error: ‘Connection handshake failed. The login ‘domain\msa-account-here$’ does not have CONNECT permission on the endpoint. State 84.’.

Here’s how to fix this:

1. Add the gMSA as a login on each replica.
2. Grant it sysadmin (most shops do this, but the grant connect is the main thing that resolves the sync issue).
3. Grant connect on the HADR endpoint on each replica

GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [domain\gmsa-account-here$];

The TL;DR For The On-Call DBA

Here is a quick numbered list of what you need to do to get back up and running:

1. Create a security group in AD. This group will be set as the value of “PrincipalsAllowedToRetrieveManagedPassword” on the gMSA object itself which is what authorizes the computer to retrieve the gMSA password from AD.
2. Put the computer account you’re trying to have leverage the gMSA in that group.
3. Install RSAT tools on the impacted server to leverage the ActiveDirectory module (run this from an Admin PowerShell window):
   • Install-WindowsFeature -Name RSAT-AD-PowerShell
4. Restart the server so that new Kerberos tickets can be picked up with the new security group assignment from above.
5. Run the following in an Admin PowerShell window on the server:
   (no appended $ needed for this part)
   Import-Module ActiveDirectory
   Install-ADServiceAccount -Identity ‘gmsa-account-name’
   Test-ADServiceAccount -Identity ‘gmsa-account-name’
   This should return “True”
6. Make sure the gMSA account has explicit permissions on your SQL related drive paths (user data files, log files, tempdb, etc.). Without this, SQL won’t be able to start. Some admins temporarily grant local admin to the account running SQL during testing, but least privilege permissions are recommended long term.
7. Make the switch in configuration manager to your gMSA account under the Log On tab and start / restart SQL.
8. If applying this to an environment with an Availability Group:
   • Add the gMSA account as an explicit login on all replicas.
   • Grant it sysadmin server role (most shops do this, but the grant connect is the main thing that resolves the sync issue).
   • Grant connect to the HADR endpoint on all replicas (including the primary) by running:
     GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [domain\gmsa-account-here$];