SQL Server gMSA Setup: Group Managed Service Accounts Step by Step

A SQL Server service needs a domain identity as soon as it has to reach anything outside its own machine: a backup share, the endpoints of an Always On availability group, a linked server that uses Kerberos delegation. The classic answer, a domain user account with a password that never expires, is also the classic weak spot. The password ends up in a document, nobody dares to change it, and the account eventually turns up in a security audit.

A group managed service account (gMSA) removes that weak spot. Active Directory generates a long, random password for the account and changes it automatically, the servers you allow retrieve it themselves, and nobody ever types it. This guide walks through setting up a gMSA for SQL Server from start to finish: the KDS root key, creating the account, assigning it to servers, installing and testing it on each server, and using it in SQL Server setup.

What a gMSA Is and When SQL Server Needs One

A gMSA is a domain account that can’t be used to log on interactively and whose password is managed by the domain controllers. By default the password changes every 30 days, and the services using it keep running without a restart. Unlike a standalone managed service account (sMSA), one gMSA can be used on several servers, which is exactly what availability groups and failover cluster instances need.

According to Microsoft, SQL Server supports gMSAs for standalone instances from SQL Server 2014, and for failover cluster instances and availability groups from SQL Server 2016. The operating system has to be Windows Server 2012 R2 or later.

Prerequisites

• Domain and forest functional levels of Windows Server 2012 or later.
• Permission to create gMSA objects (Domain Admins, or delegated rights to create msDS-GroupManagedServiceAccount objects).
• The Active Directory module for Windows PowerShell on the machine where you create the account and on every SQL Server that will use it.
• A KDS root key in the domain (the next step).

Install the Active Directory module on Windows Server with:

Install-WindowsFeature RSAT-AD-PowerShell
Import-Module ActiveDirectory

Step 1: Create the KDS Root Key (Once per Domain)

Domain controllers use the Key Distribution Service (KDS) root key to generate gMSA passwords. It’s created once per domain. Check whether one already exists first:

# Run on a domain controller or a machine with the AD module, as Domain Admin
Get-KdsRootKey

# If nothing is returned, create one
Add-KdsRootKey -EffectiveImmediately

Step 2: Create the gMSA

Give the account a descriptive name that’s at most 15 characters long (the sAMAccountName limit), and add $ wherever Windows asks for it. The examples below use the domain corp.local and the servers SQL01 and SQL02.

Allowing the servers through a security group, rather than one by one, keeps the setup manageable when a server is added or replaced:

# A security group containing the servers that may use the account
New-ADGroup -Name "gmsa-sqlprod-hosts" -GroupScope Global -GroupCategory Security
Add-ADGroupMember -Identity "gmsa-sqlprod-hosts" -Members "SQL01$", "SQL02$"

# The gMSA for the Database Engine; only members of the group can retrieve its password
New-ADServiceAccount -Name "gmsa-sqlprod" `
    -DNSHostName "gmsa-sqlprod.corp.local" `
    -PrincipalsAllowedToRetrieveManagedPassword "gmsa-sqlprod-hosts"

If the account will only ever run on one server, you can name the computer account directly instead of a group (-PrincipalsAllowedToRetrieveManagedPassword "SQL01$"), or change an existing gMSA with Set-ADServiceAccount. The account is created in the Managed Service Accounts container in Active Directory.

Use a separate gMSA for SQL Server Agent (for example gmsa-sqlagt), created the same way. One account per service keeps permissions small and makes audit logs easier to read.

Step 3: Install and Test the gMSA on Each Server

On every SQL Server that will use the account, install the AD module, then install and test the gMSA in an elevated PowerShell window:

Install-WindowsFeature RSAT-AD-PowerShell

Install-ADServiceAccount -Identity "gmsa-sqlprod"
Test-ADServiceAccount -Identity "gmsa-sqlprod"    # must return True

To see all the properties of the account, including when the password was last set and which principals may retrieve it:

Get-ADServiceAccount -Identity "gmsa-sqlprod" -Properties * |
    Select-Object Name, Enabled, PrincipalsAllowedToRetrieveManagedPassword,
                  ManagedPasswordIntervalInDays, PasswordLastSet, LastLogonDate, ServicePrincipalNames

Step 4: Use the gMSA in SQL Server Setup

On the Server Configuration page of setup, enter the account as CORP\gmsa-sqlprod$ for the Database Engine and CORP\gmsa-sqlagt$ for SQL Server Agent, and leave the password field empty. Setup grants the rights the service needs. In an unattended installation, pass the accounts without a password parameter:

.\setup.exe /Q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER `
    /SQLSVCACCOUNT="CORP\gmsa-sqlprod$" /AGTSVCACCOUNT="CORP\gmsa-sqlagt$" `
    /SQLSYSADMINACCOUNTS="CORP\SQL-DBA" /IACCEPTSQLSERVERLICENSETERMS

On an existing instance, change the account with SQL Server Configuration Manager, not the Windows Services console: Configuration Manager also updates the permissions on the data folders and registry keys. Enable instant file initialization for the new account too; our instant file initialization guide explains how to check it.

After the service restarts, confirm the account from inside SQL Server:

SELECT servicename, service_account, status_desc, instant_file_initialization_enabled
FROM sys.dm_server_services;

SPNs and Kerberos

For Kerberos authentication, the instance’s service principal names (SPNs) must be registered on the gMSA. SQL Server tries to register them itself at startup, but the account needs permission to write its own SPN. Either grant that permission to the gMSA in Active Directory, or register the SPNs once by hand:

setspn -S MSSQLSvc/SQL01.corp.local:1433 CORP\gmsa-sqlprod$
setspn -S MSSQLSvc/SQL01.corp.local CORP\gmsa-sqlprod$
setspn -L CORP\gmsa-sqlprod$

Then check the result from a client connection: SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID; should return KERBEROS, not NTLM.

gMSA in Always On Availability Groups

Running the Database Engine on every replica under the same gMSA simplifies availability groups: the endpoint permission is granted to a single login instead of one login per replica.

CREATE LOGIN [CORP\gmsa-sqlprod$] FROM WINDOWS;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [CORP\gmsa-sqlprod$];

Add every replica’s computer account to the group that may retrieve the password before you install the instance there. For the overall design, see our Always On guide.

Checklist

• A KDS root key exists in the domain and was created at least 10 hours ago.
• Separate gMSAs for the Database Engine and SQL Server Agent, with names of at most 15 characters.
• The servers are allowed through a security group, and their Kerberos tickets were refreshed after they joined it.
• Test-ADServiceAccount returns True on every server.
• Setup used DOMAIN\name$ with an empty password; existing instances were changed through Configuration Manager.
• SPNs are registered and client connections use Kerberos.
• The accounts, groups and SPNs are recorded in the installation document.

For the rest of the installation, see our SQL Server installation checklist. If the servers aren’t members of a domain at all, our guide to installing SQL Server without Active Directory covers the alternatives.