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
| 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 |
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
| 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.
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.
| 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 |
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