SQL Server Service Account and Per-Service SID

Starting from Windows Vista and Windows Server 2008, some enhancements have been implemented to  tighten ('harden') system service resource accessibility and privilege isolation. Per-service SID are introduced to enable service to run without a high privilege service account, and isolate service resource access from other service.

Previously there are five different types of accounts could be used to run service; domain user account (DOMAIN\USERACCOUNT), local user account (COMPUTER\USERACCOUNT), local service account, network service account  and local system account.

Local Service (NT AUTHORITY\LOCAL SERVICE) - built-in account. It has the same level access with to a user group of the authenticated user. It accesses network resource without credential.
Network Service (NT AUTHORITY\NETWORK SERVICE) - built-in account similar to local service. It accesses network resource with the computer credential.
Local System (NT AUTHORITY\SYSTEM) - powerful built-in account that has full access to the computer. It accesses network resource with the computer credential.

As local service, network service and high privileged local system are shared service account that could be used by many services, compromised of this service may result access to resources that is not related to the corresponding service. Per-Service SID is introduced to separate resource access by each separate service. Per-Service SID is derived from the service name and is unique to the service. Resource access could be directly modified at the object Access Control Lists (ACL) pertaining to the per-service SID instead of the service account. This allows service to run with a low privilege service account.

By default, SQL Server services enable per-service SID and are running under unrestricted per-service SID. That means that both service account and per-service SID are added to service process token. The service has the access to resources granted to both service account and the per-service SID.

** When granting additional permission, do not grant to service account. Instead, permission should be granted through security group or directly to per-service SID.

Using command prompt to identify SID type,
sc qsidtype MSSQLSERVER

Or identify through registry at, HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER

To display the per-service SID of default SQL server instance,
sc showsid MSSQLSERVER

In Windows 7 and Windows Server 2008 R2, there are two new service accounts types were introduced to simplify SPN administration and credential (password) management,

Managed Service Account (MSA) - domain created account to run service on a single computer. Password is managed automatically by domain controller. Can't use to login to computer, but could be use to start service. Named in format of DOMAIN\ACCOUNTNAME$. Automatically manage credential (password) and Service Principal Name (SPN) with Active Directory. This account is preferred for network resource access compare to virtual account.

Virtual Account - managed local account with password auto-managed. Service running with virtual account can access network resource with its computer credential DOMAIN\COMPUTERNAME$. The virtual account is named in the format of NT SERVICE\SERVICENAME (e.g. NT SERVICE\MSSQLSERVER)

From Windows 7, Windows Server 2008 R2 and later, per-service SID can be the virtual account used for service. (As per-service SID is derived from the service's name, the name format is similar with virtual account)

Below are list of SQL Server 2012 Services default service account and per-service SID name (on Windows Vista, Windows Server 2008 and above)

SQL Server Database Engine
Per-Service SID name
Default Instance - NT Service\MSSQLSERVER
Named Instance - NT Service\MSSQL$InstanceName

SID - Windows Privilege and Right
SeServiceLogonRight
SeAssignPrimaryTokenPrivilege
SeChangeNotifyPrivilege
SeIncreaseQuotaPrivilege

** For instant file initialization, include this permission to the SID
SeManageVolumePrivilege

Default Service Account
Standalone (Windows Vista and Windows Server 2008) Network Service
Standalone (Windows 7/8 and Windows Server 2008 R2/2012) Virtual Account / MSA
Failover Cluster Instance (FCI) (Windows Server 2008) Domain User
Failover Cluster Instance (FCI) (Windows Server 2008 R2/2012) Domain User

SQL Server Agent
Per-Service SID name
Default Instance - NT Service\SQLSERVERAGENT
Name Instance - NT Service\SQLAGENT$InstanceName

SID - Windows Privilege and Right
SeServiceLoginRight
SeAssignPrimaryTokenPrivilege
SeChangeNotifyPrivilege
SeIncreaseQuotaPrivilege

Default Service Account
Standalone (Windows Vista and Windows Server 2008) Network Service
Standalone (Windows 7/8 and Windows Server 2008 R2/2012) Virtual Account / MSA
FCI (Windows Server 2008) Domain User
FCI (Windows Server 2008 R2/2012) Domain User

SSRS
Per-Service SID name
Default Instance - NT SERVICE\ReportServer
Name Instance - NT SERVICE\$InstanceName

SID - Windows Privilege and Right
SeServiceLogonRight

Default Service Account
Standalone (Windows Vista and Windows Server 2008) Network Service
Standalone (Windows 7/8 and Windows Server 2008 R2/2012) Virtual Account / MSA
FCI (Windows Server 2008) Network Service
FCI (Windows Server 2008 R2/2012) Virtual Account

SSIS
Per-Service SID name
Default/Named Instance - NT SERVICE\MsDtsServer110

SID - Windows Privilege and Right
SeServiceLoginRight
SeChangeNotifyPrivilege
SeImpersonatePrivilege

Default Service Account
Standalone (Windows Vista and Windows Server 2008) Network Service
Standalone (Windows 7 and Windows Server 2008 R2) Virtual Account / MSA
Failover Cluster Instance (FCI) (Windows Server 2008) Network Service
Failover Cluster Instance (FCI) (Windows Server 2008 R2) Virtual Account

Full-Text Search
Per-Service SID name
Default Instance - NT SERVICE\MSSQLFDLauncher
Named Instance - NT SERVICE\MSSQLFDLauncher$InstanceName

SID - Windows Privilege and Right
SeServiceLogonRight
SeIncreaseQuotaPrivilege
SeChangeNotifyPrivilege

Default Service Account
Standalone (Windows Vista and Windows Server 2008) Local Service
Standalone (Windows 7/8 and Windows Server 2008 R2/2012) Virtual Account
FCI (Windows Server 2008) Local Service
FCI (Windows Server 2008 R2/2012) Virtual Account

SQL Server VSS Writer
Default/Named Instance - NT SERVICE\SQLWriter

Default Service account
NT AUTHORITY\SYSTEM

SSAS
Per-Service SID name
NT SERVICE\MSSQLServerOLAPService

Windows Security Group
Default Instance - SQLServerMSASUser$ComputerName$MSSQLSERVER
Name Instance - SQLServerMSASUser$ComputerName$InstanceName
PowerPivot Sharepoint Instance - SQLServerMSASUser$ComputerName$PowerPivot

Windows Security Group - Windows Privilege and Right
SeServiceLogonRight

Default Service Account
Standalone (Windows Vista and Windows Server 2008) Network Service
Standalone (Windows 7/8, Windows Server 2008 R2/2012) Virtual Account / MSA
FCI (Windows Server 2008) Domain User
FCI (Windows Server 2008 R2/2012) Domain User

SQL Server Browser
Per-Service SID name
NT SERVICE\SQLBrowser

Windows Security Group
Default/Named Instance - SQLServer2005SQLBrowserUser$ComputerName

Windows Security Group - Windows Privilege and Right
SeServiceLogonrRight

Default Service Account
NT AUTHORITY\LOCAL SERVICE

SQL Windows Management Instrument (WMI)
Per-Service SID name
NT SERVICE\winmgmt

More details can be found on this MSDN article on Configure Windows Service Accounts and Permissions and System Service,