SPN Registration Of Windows Service Accounts and Permissions

Domain user accounts, local user accounts, managed service accounts, virtual accounts, and built-in system accounts can all be used to start and run SQL Server. Each SQL Server service must have a startup account defined after installation in order to start and run.

This section covers the accounts that can be used to start SQL Server services, the SQL Server Setup default values, the idea of per-service SIDs, startup options, and firewall configuration.

Default Service Accounts :

The default service accounts used by setup when installing all components are listed in the table below. Except when otherwise specified, the default accounts given are the recommended accounts.

Stand-alone Server or Domain Controller

ComponentWindows Server 2008Windows 7 and Windows Server 2008 R2 and higher
Database EngineNETWORK SERVICEVirtual Account*
SQL Server AgentNETWORK SERVICEVirtual Account*
SSASNETWORK SERVICEVirtual Account* **
SSISNETWORK SERVICEVirtual Account*
SSRSNETWORK SERVICEVirtual Account*
SQL Server Distributed Replay ControllerNETWORK SERVICEVirtual Account*
SQL Server Distributed Replay ClientNETWORK SERVICEVirtual Account*
FD Launcher (Full-text Search)LOCAL SERVICEVirtual Account
SQL Server BrowserLOCAL SERVICELOCAL SERVICE
SQL Server VSS WriterLOCAL SYSTEMLOCAL SYSTEM
Advanced Analytics ExtensionsNTSERVICE\
MSSQLLaunchpad
NTSERVICE\
MSSQLLaunchpad
PolyBase EngineNETWORK SERVICENETWORK SERVICE
PolyBase Data Movement ServiceNETWORK SERVICENETWORK SERVICE

Microsoft recommends utilizing a Managed Service Account (MSA) with the bare minimum of privileges when resources from outside the SQL Server workstation are required. A virtual account as the service account is not supported when deployed on a Domain Controller.

SQL Server Failover Cluster Instance

ComponentWindows Server 2008Windows Server 2008 R2
Database EngineNone. Provide a domain user account.Provide a domain user account.
SQL Server AgentNone. Provide a domain user account.Provide a domain user account.
SSASNone. Provide a domain user account.Provide a domain user account.
SSISNETWORK SERVICEVirtual Account
SSRSNETWORK SERVICEVirtual Account
FD Launcher (Full-text Search)LOCAL SERVICEVirtual Account
SQL Server BrowserLOCAL SERVICELOCAL SERVICE
SQL Server VSS WriterLOCAL SYSTEMLOCAL SYSTEM

Managed Service Accounts, Group Managed Service Accounts, and Virtual Accounts are all types of managed service accounts.

Managed service accounts, group managed service accounts, and virtual accounts are designed to give critical programs like SQL Server their own accounts while removing the need for an administrator to manage the Service Principal Name (SPN) and credentials for these accounts manually. This simplifies the long-term maintenance of service account users, passwords, and SPNs..

  • Managed Service Accounts A Managed Service Account (MSA) is a type of domain account that the domain controller creates and manages. It’s tied to a single member computer and is used to run a service. The domain controller manages the password automatically. A MSA cannot be used to log into a computer, but it can be used to start a Windows service by a computer. When given read and write service PrincipalName permissions, an MSA can register a Service Principal Name (SPN) in Active Directory. A MSA is identified by a $ suffix, such as DOMAINACCOUNTNAME$. Leave the password field blank when designating an MSA. A MSA can only be used on one computer, hence it can’t be utilized on several nodes.. Before SQL Server setup may use the MSA for SQL Server services, the domain administrator must create it in Active Directory.
  • Group Managed Service Accounts An MSA for numerous servers is known as a Group Managed Service Account (GMSA). A service account is managed by Windows for services running on a group of servers. Without restarting services, Active Directory automatically refreshes the group managed service account password. You can use a group managed service account principal to configure SQL Server services. SQL Server offers group managed service accounts for standalone instances starting with SQL Server 2014, and SQL Server 2016 and later for failover cluster instances and availability groups. The operating system must be Windows Server 2012 R2 or later to use a group managed service account for SQL Server 2014 or later. KB 2998082 must be installed on Windows Server 2012 R2 servers in order for services to log in without interruption after a password change. See Group Managed Service Accounts for further details. Note: Before SQL Server setup may use the group managed service account for SQL Server services, the domain administrator must create it in Active Directory.
  • Virtual Accounts Virtual accounts (starting with Windows Server 2008 R2 and Windows 7) are managed local accounts that ease service administration by providing the following characteristics. The virtual account is administered automatically, and it can connect to the network in a domain environment. If the default setting for service accounts is chosen during SQL Server setup, a virtual account with the instance name as the service name, in the format NT SERVICE, is created. The credentials of the computer account in the format $ are used by services that run as virtual accounts to access network resources. Leave the password blank when creating a virtual account to start SQL Server. Manually register the Service Principal Name (SPN) if the virtual account fails to do so. See Manual SPN Registration for further information on manually registering an SPN.
  • Note : Because virtual accounts do not have the same SID on each node of the cluster, they cannot be used for SQL Server Failover Cluster Instance.
  • Examples of virtual account names are included in the table below.
ServiceVirtual Account Name
Default instance of the Database Engine serviceNT SERVICE\MSSQLSERVER
Named instance of a Database Engine service named PAYROLLNT SERVICE\MSSQL$PAYROLL
SQL Server Agent service on the default instance of SQL ServerNT SERVICE\SQLSERVERAGENT
SQL Server Agent service on an instance of SQL Server named PAYROLLNT SERVICE\SQLAGENT$PAYROLL

See the Managed Service Accounts and Virtual Accounts ideas part of the Service Accounts Step-by-Step Guide and the Managed Service Accounts Frequently Asked Questions for further information on Managed Service Accounts and Virtual Accounts (FAQ).

Security Note: Always run SQL Server services with the least amount of user rights possible. When feasible, use an MSA or virtual account. Use a specific low-privilege user account or domain account instead of a shared account for SQL Server services when MSA and virtual accounts aren’t available. For different SQL Server services, use distinct accounts. Do not provide the SQL Server service account or the service groups any new permissions. Where a service SID is supported, permissions will be granted either through group membership or directly to a service SID.

More information on SPN Registration can be found at the following link:

https://help.deepsecurity.trendmicro.com/11_2/on-premise/kerberos-mssql.html

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.