Configure Windows Service Accounts and Permissions exemplary on SQL Server 2019
Current windows operating systems provide a wide choice of accounts you can use to run local services. Today I want to pay attention to the individual accounts you can use as service accounts to run the different SQL Server 2019 services.
Detailed documentation about local user accounts in Windows you will also find in my post below.
Configure Windows Service Accounts and Permissions
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions
You can choose between the following accounts that can be configured to start SQL Server services:
- domain user accounts
- local user accounts
- managed service accounts (group managed service accounts)
- virtual accounts
- built-in system accounts
Under https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Default_Accts you will see the recommended accounts by Microsoft to use for.
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.
Changing Account Properties
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.
For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for Power Pivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.
To change Reporting Services options, use the Reporting Services Configuration Tool.
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 Accounts
A 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 DOMAINACCOUNTNAME$. 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. - Group Managed Service Accounts
A Group Managed Service Account (gMSA) 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 gMSA 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. - Virtual Accounts
Virtual 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.
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.
Security Note: Always run SQL Server services by using the lowest possible user rights. Use a MSA, gMSA or virtual account when possible. When MSA, gMSA 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.
A detailed guide about setting up Managed Service Accounts (MSA), you will find in my following post.
Set up SQL Server 2019 and configure the service accounts
So now we will run the SQL Server 2019 setup and go through the steps with the service accounts configuration.
First we install a default instance, later we also add a named instance to see the difference regarding naming of the service accounts.
Here you can see as mentioned above with
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 SERVICESERVICENAME.
Here you can see very nice, that the virtual account is using the instance name as the service name NT ServiceMSSQLSERVER
Important note
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.f
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#Changing_Accounts
Service Configuration and Access Control
SQL Server 2019 (15.x) enables per-service SID for each of its services to provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for a named instance of the Database Engine service might be NT ServiceMSSQL$<InstanceName>. Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object. By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources.
On Windows 7 and Windows Server 2008 R2 (and later) the per-service SID can be the virtual account used by the service.
For most components SQL Server configures the ACL for the per-service account directly, so changing the service account can be done without having to repeat the resource ACL process.
For most components SQL Server configures the ACL for the per-service account directly, so changing the service account can be done without having to repeat the resource ACL process.
Windows Privileges and Rights
The account assigned to start a service needs the Start, stop and pause permission for the service. The SQL Server Setup program automatically assigns this. First install Remote Server Administration Tools (RSAT). See Remote Server Administration Tools for Windows 10.
File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups
SQL Server service accounts must have access to resources. Access control lists are set for the per-service SID or the local Windows group.
For failover cluster installations, resources on shared disks must be set to an ACL for a local account.
Now as mentioned above, we will also install a named instance, to see the default naming of the virtual accounts, SQL Server Setup will create for us.
Here you can see as mentioned above
The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for a named instance of the Database Engine service might be NT ServiceMSSQL$<InstanceName>.
