SPN Registration:
SQL Server can be started and operated using domain user accounts, local user accounts, managed service accounts, virtual accounts, and built-in system accounts. After installation, a startup account must be configured for each SQL Server service in order for it to function.
The accounts that can be used to start SQL Server services, the default values in SQL Server Setup, the concept of per-service SIDs, startup options, and firewall configuration are all covered in this section.
Default Service Accounts:
The table below lists the default service accounts that setup uses when installing all components. The default accounts listed are the suggested accounts, unless otherwise stated.
Stand-alone Server or Domain Controller
Component | Windows Server 2008 | Windows 7 and Windows Server 2008 R2 and higher |
Database Engine | NETWORK SERVICE | Virtual Account* |
SQL Server Agent | NETWORK SERVICE | Virtual Account* |
SSAS | NETWORK SERVICE | Virtual Account* ** |
SSIS | NETWORK SERVICE | Virtual Account* |
SSRS | NETWORK SERVICE | Virtual Account* |
SQL Server Distributed Replay Controller | NETWORK SERVICE | Virtual Account* |
SQL Server Distributed Replay Client | NETWORK SERVICE | Virtual Account* |
FD Launcher (Full-text Search) | LOCAL SERVICE | Virtual Account |
SQL Server Browser | LOCAL SERVICE | LOCAL SERVICE |
SQL Server VSS Writer | LOCAL SYSTEM | LOCAL SYSTEM |
Advanced Analytics Extensions | NTSERVICE\ MSSQLLaunchpad | NTSERVICE\ MSSQLLaunchpad |
PolyBase Engine | NETWORK SERVICE | NETWORK SERVICE |
PolyBase Data Movement Service | NETWORK SERVICE | NETWORK SERVICE |
When resources from outside the SQL Server workstation are needed, Microsoft advises using a Managed Service Account (MSA) with the least amount of privileges. When set up on a Domain Controller, a virtual account cannot serve as the service account.
SQL Server Failover Cluster Instance
Component | Windows Server 2008 | Windows Server 2008 R2 |
Database Engine | None. Provide a domain user account. | Provide a domain user account. |
SQL Server Agent | None. Provide a domain user account. | Provide a domain user account. |
SSAS | None. Provide a domain user account. | Provide a domain user account. |
SSIS | NETWORK SERVICE | Virtual Account |
SSRS | NETWORK SERVICE | Virtual Account |
FD Launcher (Full-text Search) | LOCAL SERVICE | Virtual Account |
SQL Server Browser | LOCAL SERVICE | LOCAL SERVICE |
SQL Server VSS Writer | LOCAL SYSTEM | LOCAL SYSTEM |
Managed Service Accounts, Group Managed Service Accounts, and Virtual Accounts are all types of managed service accounts.
In order to give essential programs like SQL Server their own accounts without requiring an administrator to manually manage the Service Principal Name (SPN) and credentials for these accounts, managed service accounts, group managed service accounts, and virtual accounts were created. The long-term maintenance of service account users, passwords, and SPNs is made simpler as a result.
Managed Service Accounts:
The domain controller can establish and administer a specific kind of domain account called a Managed Service Account (MSA). It’s used to execute a service and is connected to a single member computer. The password is automatically managed by the domain controller. A MSA can be used to start a Windows service by a computer, but it cannot be used to log into a computer. When allowed read and write service PrincipalName rights, an MSA can register a Service Principal Name (SPN) in Active Directory. A $ suffix, such as DOMAINACCOUNTNAME$, identifies an MSA. The password box should be left empty when specifying an MSA. A MSA cannot be used on several nodes because it can only be used on one machine. Use the before setting up SQL Server
Group Managed Service Accounts:
Group Managed Service Accounts are MSAs for multiple servers (GMSA). Windows controls a service account for applications running across a number of hosts. Active Directory automatically changes the group managed service account password without having to restart any services. To set up SQL Server services, you can utilise a group managed service account principle. Starting with SQL Server 2014, group managed service accounts are available for standalone instances, and for failover cluster instances and availability groups, group managed service accounts are available starting with SQL Server 2016.To use a group managed service account for SQL Server 2014 or later, the operating system must be Windows Server 2012 R2 or later. KB 2998082 must be deployed on Windows Server 2012 R2 servers in order for services to log in without interruption following a password change. For further information, see Group Managed Service Accounts. Note: The domain administrator must create the group managed service account in Active Directory before SQL Server setup can utilize it for SQL Server services.
Virtual Accounts:
In Windows Server 2008 R2 and Windows 7, virtual accounts are managed local accounts that simplify service administration by offering the following features. The virtual account has automatic administration and a domain environment connection capability. If the default option for service accounts is selected during SQL Server setup, a virtual account in the format NT SERVICE is created with the instance name as the service name.To start SQL Server, while creating a virtual account, leave the password blank. If the virtual account is unable to do so, manually register the Service Principal Name (SPN).For further details on manually registering an SPN, see Manual SPN Registration.
Note:
Virtual accounts cannot be utilized with SQL Server Failover Cluster Instances because they do not share the same SID across all cluster nodes. The table below gives some names of virtual accounts as examples.
Service | Virtual Account Name |
Default instance of the Database Engine service | NT SERVICE\MSSQLSERVER |
Named instance of a Database Engine service named PAYROLL | NT SERVICE\MSSQL$PAYROLL |
SQL Server Agent service on the default instance of SQL Server | NT SERVICE\SQLSERVERAGENT |
SQL Server Agent service on an instance of SQL Server named PAYROLL | NT SERVICE\SQLAGENT$PAYROLL |
For further information on Managed Service Accounts and Virtual Accounts, see the Managed Service Accounts Frequently Asked Questions and the Managed Service Accounts ideas section of the Service Accounts Step-by-Step Guide (FAQ).
Security Note:
Always utilise the fewest user rights possible while running SQL Server services. When feasible, utilise an MSA or virtual account. When MSA and virtual accounts are not available, utilise a specific low-privilege user account or domain account rather than a shared account for SQL Server services. Use different accounts for the various SQL Server services. Don’t grant the service groups or the SQL Server service account any new rights. When a service SID is supported, permissions will be given either directly to a service SID or through group membership.
The following page has further details on SPN Registration:
https://help.deepsecurity.trendmicro.com/11_2/on-premise/kerberos-mssql.html