SPN Registration Of Windows Service Accounts and Permissions

Startup accounts used to start and run SQL Server can be domain user accounts, local user accounts, managed service accounts, virtual accounts, or built-in system accounts. To start and run, each service in SQL Server must have a startup account configured during installation.

This section describes the accounts that can be configured to start SQL Server services, the default values used by SQL Server Setup, the concept of per-service SID’s, the startup options, and configuring the firewall.

Default Service Accounts :

The following table lists the default service accounts used by setup when installing all components. The default accounts listed are the recommended accounts, except as noted.

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

*When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary. ** When installed on a Domain Controller, a virtual account as the service account is not supported.

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

Managed service accounts, group managed service accounts, and virtual accounts are designed to provide crucial applications such as SQL Server with the isolation of their own accounts, while eliminating the need for an administrator to manually administer the Service Principal Name (SPN) and credentials for these accounts. These make long term management of service account users, passwords and SPNs much easier.

  • Managed Service AccountsA Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service. The password is managed automatically by the domain controller. You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service. An MSA has the ability to register a Service Principal Name (SPN) within Active Directory when given read and write servicePrincipalName permissions. A MSA is named with a $ suffix, for example DOMAIN\ACCOUNTNAME$. When specifying a MSA, leave the password blank. Because a MSA is assigned to a single computer, it cannot be used on different nodes of a Windows cluster. NoteThe MSA must be created in the Active Directory by the domain administrator before SQL Server setup can use it for SQL Server services.
  • Group Managed Service AccountsA Group Managed Service Account is an MSA for multiple servers. Windows manages a service account for services running on a group of servers. Active Directory automatically updates the group managed service account password without restarting services. You can configure SQL Server services to use a group managed service account principal. Beginning with SQL Server 2014, SQL Server supports group managed service accounts for standalone instances, and SQL Server 2016 and later for failover cluster instances, and availability groups.To use a group managed service account for SQL Server 2014 or later, the operating system must be Windows Server 2012 R2 or later. Servers with Windows Server 2012 R2 require KB 2998082 applied so that the services can log in without disruption immediately after a password change.For more information, see Group Managed Service Accounts NoteThe group managed service account must be created in the Active Directory by the domain administrator before SQL Server setup can use it for SQL Server services.
  • Virtual AccountsVirtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. When specifying a virtual account to start SQL Server, leave the password blank. If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually. For more information on registering a SPN manually, see Manual SPN Registration
  • Note : Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.
  • The following table lists examples of virtual account names.
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

For more information on Managed Service Accounts and Virtual Accounts, see the Managed service account and virtual account concepts section of Service Accounts Step-by-Step Guide and Managed Service Accounts Frequently Asked Questions (FAQ).

Security Note: Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.

The below link provides more Information on SPN Registration :

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.