Skip to content

Managed Service Accounts

Service accounts for SQL Server services come in three flavors. Regular domain accounts work, but they require manual password management and always carry the risk of someone logging in as that user.

Overview

Type Password management Multiple servers Requires
Regular domain account Manual Yes Always available
sMSA (Managed Service Account) Automatic (AD) No — 1 computer Server 2008 R2+
gMSA (Group Managed Service Account) Automatic (AD) Yes Server 2012+ DC + KDS

For SQL Server in a lab or production environment: gMSA is preferred. No password to maintain, no expiration risk, automatic SPN registration.

sMSA — Managed Service Account

Automatic password management by Active Directory, but tied to a single computer.

# Create on the Domain Controller
New-ADServiceAccount -Name "svc-sqlengine" `
                     -DNSHostName "sql01.lab.local" `
                     -Enabled $true

# Install on the target server (run on sql01)
Install-ADServiceAccount -Identity "svc-sqlengine"

# Verify
Test-ADServiceAccount -Identity "svc-sqlengine"

In SQL Server Configuration Manager: select the service → Properties → Log On → choose NT SERVICE\... or the sMSA account (name ends with $, leave the password field empty).

gMSA — Group Managed Service Account

Same benefits as sMSA but usable across multiple servers — ideal for clusters, AlwaysOn AG, replication.

Requirements

  • Domain Functional Level: Windows Server 2012 or higher
  • A KDS Root Key created on the DC

Step 1 — Create the KDS Root Key (once per domain)

# In production: a 10-hour delay (replication buffer)
Add-KdsRootKey -EffectiveImmediately

# In a lab: active immediately (not for production)
Add-KdsRootKey -EffectiveTime (Get-Date).AddHours(-10)

Step 2 — Create the gMSA

New-ADServiceAccount -Name "svc-sqlengine" `
                     -DNSHostName "sql01.lab.local" `
                     -PrincipalsAllowedToRetrieveManagedPassword "sql01$", "sql02$" `
                     -Enabled $true

PrincipalsAllowedToRetrieveManagedPassword — list of computer accounts (with $) allowed to retrieve the password.

Step 3 — Install on the target server

# Run on sql01 (and on every server in the list)
Install-ADServiceAccount -Identity "svc-sqlengine"
Test-ADServiceAccount    -Identity "svc-sqlengine"

Step 4 — Configure the SQL Server services

Via SQL Server Configuration Manager:

  1. Right-click the service → Properties → Log On tab
  2. Account: lab\svc-sqlengine$ (with the trailing $)
  3. Password: leave empty
  4. Restart the service

Which services should get their own account?

  • SQL Server (MSSQLSERVER) — its own gMSA
  • SQL Server Agent — its own gMSA (or share the engine's, though a separate one is preferred)
  • SQL Server Browser — NT AUTHORITY\LOCAL SERVICE is sufficient here
  • SSRS — its own gMSA if SSRS is installed

Permissions SQL Server Handles Itself

When configuring through SQL Server Configuration Manager, SQL Server automatically adds the necessary local permissions to the service account:

  • Log on as a service
  • Access to the SQL Server directories
  • Membership in the local group SQLServerMSSQLUser$...$MSSQLSERVER

You do not need to do this manually — SQL Server Configuration Manager handles it, just like the SQL Server installer. Always use Configuration Manager, and never Services.msc, to change service accounts.

Avoid Services.msc

If you change the account via Services.msc, the ACLs and local permissions are not updated. Always use SQL Server Configuration Manager.

Quickly Checking Which Account Is Active

SELECT servicename, service_account
FROM   sys.dm_server_services;