Tuesday, April 12, 2022

SQL Server and Windows Service Accounts

 Good article:


Most services and their properties can be configured by using SQL Server Configuration Manager. 


Startup accounts used to start and run SQL Server can be 

domain user accounts

If the service must interact with network services, access domain resources like file shares or if it uses linked server connections to other computers running SQL Server, you might use a minimally-privileged domain account. Many server-to-server activities can be performed only with a domain user account. This account should be pre-created by domain administration in your environment.

If you configure the SQL Server to use a domain account, you can isolate the privileges for the Service, but must manually manage passwords or create a custom solution for managing these passwords. Many server applications use this strategy to enhance security, but this strategy requires additional administration and complexity. In these deployments, service administrators spend a considerable amount of time on maintenance tasks such as managing service passwords and service principal names (SPNs), which are required for Kerberos authentication. In addition, these maintenance tasks can disrupt service.

local user accounts

You'd only use this if the computer is not part of a domain

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 can't use an MSA to sign into a computer, but a computer can use an 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. 

An MSA is named with a $ suffix, for example DOMAIN\ACCOUNTNAME$

When specifying an MSA, leave the password blank

Because an MSA is assigned to a single computer, it can't be used on different nodes of a Windows cluster.

GROUP-managed service account variant: an MSA for multiple servers.

Beginning with SQL Server 2014 (Windows Server 2012 R2), SQL Server supports group-managed service accounts for standalone instances, and SQL Server 2016 and later for failover cluster instances, and availability groups

virtual accounts 

Virtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are managed local accounts 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. 

Service Accounts Step-by-Step Guide

Virtual accounts can't 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 DevNT SERVICE\MSSQL$Dev
SQL Server Agent service on the default instance of SQL ServerNT SERVICE\SQLSERVERAGENT
SQL Server Agent service on an instance of SQL Server named DevNT SERVICE\SQLAGENT$Dev

built-in system accounts:

local SERVICE account (NT AUTHORITY\LOCAL SERVICE): a single account that is built-in, has same access as Users.

The Local Service account IS NOT supported for the SQL Server or SQL Server Agent services. Local Service isn't supported as the account running those services because it is a shared service and any other services running under local service would have system administrator access to SQL Server. 

Network Service Account ( NT AUTHORITY\NETWORK SERVICE): has access to more than just Users. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$

Local System Account (NT AUTHORITY\SYSTEM ):  a very high-privileged built-in account.

  • 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 Don't change all the required settings.

No comments: