A gMSA as the SQL Server service account: step-by-step deployment

The SQL Server service account was created at the first installation, several years ago, with the “Password never expires” option ticked. The password is known to the administrator who installed the server, two other people in IT and at least one former employee, and it is also written down in the deployment documentation. Nobody changes it, because a change means a new password in Active Directory, typing it manually into every SQL service on every server, and restarting every instance. A service missed during the change will not start after the next server restart and reports error 1069 (“The service did not start due to a logon failure”).

A group managed service account (gMSA) removes this problem at the source. The domain controller generates and changes the password, the servers retrieve it themselves, and no person knows it or types it anywhere. This post walks through the deployment: from the KDS root key, through creating the account and installing it on the servers, to changing the instance account in SQL Server Configuration Manager.

How a gMSA works

A gMSA is an Active Directory object built much like a computer account. The domain controller computes the password from the root key of the Key Distribution Service (KDS) and the account’s attributes, and changes it every 30 days by default. The interval can only be set when the account is created, with the -ManagedPasswordIntervalInDays parameter. After that, the msDS-ManagedPasswordInterval attribute is read-only, and a different interval requires a new account.

Only principals listed in the msDS-GroupMSAMembership attribute, which the -PrincipalsAllowedToRetrieveManagedPassword parameter sets, can retrieve the password. The simplest option is a group of computers, here GG-SQL-Hosts with the servers srv-sql-01 and srv-sql-02. A server in this group retrieves the current and the previous password from the domain controller, which is why a password change does not require a service restart.

SQL Server supports a gMSA as the service account from SQL Server 2014, on Windows Server 2012 R2 or later.

Virtual account, domain account or gMSA

SQL Server Setup runs the engine as a virtual account by default, for example NT SERVICE\MSSQL$CRM. This is a good setting for a single server that does not reach network resources. On the network, a virtual account appears as the computer account, so permission on the backup share is granted to the whole server with all its services, and SPNs for the instances’ own names have to be registered on the computer account and moved together with the instance.

Aspect	Virtual account	Regular domain account	gMSA
Who knows the password	nobody, the account has no password	the administrator who set it, and everyone it was passed to	nobody, the domain controller computes it
Password change	not applicable	manually in AD and in every service, with a restart	automatic, every 30 days by default, no restart
Access to network resources	as the computer account, e.g. CORP\srv-sql-01$	as that account	as CORP\svc-sql$
SPN	on the computer account	on the service account	on the gMSA
Use on several servers	no, an identity local to the server	yes	yes, on servers in the GG-SQL-Hosts group
Interactive sign-in	impossible	possible for anyone who knows the password	impossible

The KDS root key

Domain controllers need a KDS root key before they start computing gMSA passwords. The key is created once per forest, on a domain controller, with Domain Admins or Enterprise Admins rights. It is worth checking first whether the key already exists, because it may have been created when another service was deployed.

# Check whether a key already exists
Get-KdsRootKey

# Only when the previous command returned nothing
Add-KdsRootKey -EffectiveImmediately

Despite the name of the -EffectiveImmediately switch, Microsoft recommends waiting 10 hours before the first account goes into use. The key has to replicate to all domain controllers, and a server that asks a domain controller which does not have the key yet will not get the password. Event 4004 in the KdsSvc operational log confirms that the key was created. In a lab with a single domain controller, the effective time can be set 10 hours in the past (-EffectiveTime ((Get-Date).AddHours(-10))); do not do this in production.

The server group and the gMSA

The account is created with New-ADServiceAccount, which places it in the Managed Service Accounts container by default. The -DNSHostName parameter is mandatory for a group account and usually holds the account name in the domain. The -KerberosEncryptionType parameter restricts ticket encryption to AES, in line with Microsoft’s recommendation for managed accounts.

# Group of computers allowed to retrieve the account password
New-ADGroup -Name GG-SQL-Hosts -GroupScope Global -GroupCategory Security
Add-ADGroupMember -Identity GG-SQL-Hosts -Members 'srv-sql-01$', 'srv-sql-02$'

# The gMSA; the password change interval can only be set now
New-ADServiceAccount -Name svc-sql -DNSHostName svc-sql.corp.example.com `
    -PrincipalsAllowedToRetrieveManagedPassword GG-SQL-Hosts `
    -ManagedPasswordIntervalInDays 30 `
    -KerberosEncryptionType AES128, AES256

Another SQL server is added to the GG-SQL-Hosts group, not to the account itself. Set-ADServiceAccount with the -PrincipalsAllowedToRetrieveManagedPassword parameter sets the list of allowed principals from scratch, so it is easy to remove access from running servers by accident.

Installing and testing on the server

Every SQL server needs the ActiveDirectory module for PowerShell. Install-ADServiceAccount checks whether the server is allowed to use the account and prepares it locally; for a gMSA it caches the account on the server. It requires a PowerShell session run as administrator.

# ActiveDirectory module for PowerShell
Install-WindowsFeature RSAT-AD-PowerShell

Install-ADServiceAccount -Identity svc-sql
Test-ADServiceAccount -Identity svc-sql

Test-ADServiceAccount returns True when the server can retrieve the password and authenticate with the account. A False result usually has one of three causes: the server is not a member of GG-SQL-Hosts or does not know it yet, the KDS key has not reached the domain controller the server talks to, or the server does not support the encryption types the account requires.

Changing the account in SQL Server Configuration Manager

The instance account is changed in SQL Server Configuration Manager. In SQL Server 2025 it opens with SQLServerManager17.msc; older versions use a file with a correspondingly lower number. Under SQL Server Services, open the properties of the SQL Server (CRM) service, go to the Log On tab and enter the account with the trailing dollar sign, leaving the password fields empty:

SQL Server Services > SQL Server (CRM) > Properties > Log On
  This account
    Account Name:      CORP\svc-sql$
    Password:          (empty)
    Confirm password:  (empty)

After confirmation, Configuration Manager offers to restart the service. The new account takes effect only after the restart, and the instance’s databases are unavailable while it runs. This is the only downtime in the whole deployment, which is why the change is planned for a maintenance window; later password changes happen without restarts. The SQL Server Agent service account is changed the same way, although Microsoft recommends separate accounts for different SQL Server services, so Agent can get its own account or stay on its virtual account.

Do not use the Services snap-in (services.msc) for this: Microsoft recommends changing the account and password of SQL Server services only with SQL Server tools. Besides the account name, Configuration Manager updates the Windows local security store that protects the service master key of the Database Engine. The Services snap-in changes only the account name, and the consequences show up later, for example as the error “A required privilege is not held by the client” in Agent job steps. SQL Server Setup grants permissions on the instance files and registry to the per-service SID NT SERVICE\MSSQL$CRM, not to the account, so they need no changes after the switch.

Before switching, everything granted to the old account has to be moved to the new one: permissions on network shares, such as the backup share (backups without SQL Agent are covered in the sixth part of this guide), logins of the old account on other instances, and delegation settings. The SPNs have to be removed from the old account and registered on CORP\svc-sql$; the commands and their order are described in the second part of this guide. The old account is disabled only after checking that no service uses it, and deleted after an observation period.

One account for all instances or several

A shared svc-sql account for the CRM, ERP and HR instances on both servers is the simplest option. The SPNs of all instances sit on one account, delegation is configured once, and moving an instance from srv-sql-01 to srv-sql-02 comes down to changing a DNS record, with no changes to SPNs or permissions.

The price is the reach of a compromise. Any process running as SYSTEM on any server in the GG-SQL-Hosts group can retrieve the account password, because this is exactly how the system itself retrieves it. An attacker who takes over one instance, for example through xp_cmdshell, acts on the network as CORP\svc-sql$, that is, with access to the shares of all instances, with their delegation and with the logins granted to this account on other servers.

A practical rule: one account per group of instances with similar data sensitivity and the same administrators. The instance holding HR data should get a separate gMSA. A separate account only partly limits the consequences of another instance on the same server being compromised. An instance process does not run as SYSTEM, so it cannot retrieve another account’s password directly, but SQL Server service accounts hold the impersonation privilege (SeImpersonatePrivilege), which often allows escalation to system level. Full isolation requires a separate server and a separate group of allowed computers.

Check your own environment

KDS root keys in the forest; run this on a domain controller. A good result is at least one key whose EffectiveTime is at least 10 hours in the past. An empty result means there are no gMSAs in the forest yet, and a date in the future means the key is not in effect yet.

Get-KdsRootKey | Select-Object KeyId, CreationTime, EffectiveTime

Who can retrieve the account password, how often it changes, which encryption the account supports and when the password last changed. A good result is only the GG-SQL-Hosts group in PrincipalsAllowedToRetrieveManagedPassword, the value 30 (or a deliberately chosen interval), 24 in msDS-SupportedEncryptionTypes (AES128 and AES256), and a PasswordLastSet no older than roughly one interval. User accounts or the Domain Computers group in the list of allowed principals is a bad result: someone outside the SQL servers can then retrieve the password.

Get-ADServiceAccount -Identity svc-sql -Properties PrincipalsAllowedToRetrieveManagedPassword, 'msDS-ManagedPasswordInterval', 'msDS-SupportedEncryptionTypes', PasswordLastSet |
    Select-Object Name, PrincipalsAllowedToRetrieveManagedPassword, 'msDS-ManagedPasswordInterval', 'msDS-SupportedEncryptionTypes', PasswordLastSet

Server access to the account, on each SQL server separately. A good result is True and the GG-SQL-Hosts group in the computer’s list of groups. False while the server is in the group usually means an old Kerberos ticket, as described in the pitfall box on group membership, and a missing group in the list means nobody has added the server to it.

Test-ADServiceAccount -Identity svc-sql

# Groups the computer account belongs to
Get-ADComputer -Identity $env:COMPUTERNAME | Get-ADPrincipalGroupMembership | Select-Object Name

The accounts SQL Server services run as on the server. A good result is CORP\svc-sql$ in the StartName of the MSSQL$… services and Running in State. A personal account, an old service account or LocalSystem points to a service that has not been switched yet.

Get-CimInstance -ClassName Win32_Service -Filter "Name LIKE 'MSSQL%' OR Name LIKE 'SQLAgent%'" |
    Select-Object Name, StartName, State