You have installed SQL Server and it is up and running without any issue. Later, you need to change some permission given to either SQL Server engine or agent. You look for accounts, searched in local users, searched in local groups but you cannot find them.
Are you experiencing above issues? If yes, first understand the what are these accounts. These are called Virtual Accounts that are created during the installation of SQL Server. These accounts are managed by the Operating System itself, hence they are not visible when you browse Local Users and Groups window. Similarly, there is another type of accounts called Managed Service Accounts that are created at domain level and assigned to SQL Server services.
Now, for some reason, if you changed the service account of your SQL Server service to another account, and later you want to use the same Virtual Account, this is what you have to do.
1. Get the properties of the services.
2. Easiest way is, just type the account and leave the password blank. If the instance is default, type it as NT ServiceMSSQLSERVER or if it is a named instance, type NT ServiceMSSQL$.
3. Click on to get the service restarted. It will work as you expected.
4. Or, if you want to search the account, click on Browse to open Select User or Group window. Type nt servicems in Enter the object name to select input box and click on Check Names. If you are setting the Agent Service, look for nt servicesql word.
5. You get Multiple Names Found window opened. Select the account from the list and continue. Do not enter a password, click on OK and get the service restarted.
Just like this, if you need to add these accounts to some other groups for granting more permissions, example, adding Agent Service Account to Administrators Group (not recommended), follow the same steps.
| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic |
|---|---|---|---|---|---|---|---|
|
Configure Windows service accounts and permissions |
Get acquainted with the service accounts that are used to start and run services in SQL Server. See how to configure them and assign appropriate permissions. |
rwestMSFT |
randolphwest |
08/10/2022 |
sql |
configuration |
reference |
Configure Windows service accounts and permissions
[!INCLUDE SQL Server]
Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This article describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during and after SQL Server installation. This article helps advanced users understand the details of the service accounts.
Most services and their properties can be configured by using SQL Server Configuration Manager. Here are the paths to recent versions when Windows is installed on the C drive.
| SQL Server version | Path |
|---|---|
| [!INCLUDE sssql22-md] | C:WindowsSysWOW64SQLServerManager16.msc |
| [!INCLUDE sssql19-md] | C:WindowsSysWOW64SQLServerManager15.msc |
| [!INCLUDE sssql17-md] | C:WindowsSysWOW64SQLServerManager14.msc |
| [!INCLUDE sssql16-md] | C:WindowsSysWOW64SQLServerManager13.msc |
| SQL Server 2014 | C:WindowsSysWOW64SQLServerManager12.msc |
| SQL Server 2012 | C:WindowsSysWOW64SQLServerManager11.msc |
Services installed by SQL Server
Depending on the components that you decide to install, SQL Server Setup installs the following services:
| Service | Description |
|---|---|
| SQL Server Database Services | The service for the SQL Server relational [!INCLUDEssDE]. The executable file is <MSSQLPATH>MSSQLBinnsqlservr.exe. |
| SQL Server Agent | Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks. The SQL Server Agent service is present but disabled on instances of [!INCLUDEssExpress]. The executable file is <MSSQLPATH>MSSQLBinnsqlagent.exe. |
| [!INCLUDEssASnoversion] | Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications. The executable file is <MSSQLPATH>OLAPBinmsmdsrv.exe. |
| [!INCLUDEssRSnoversion] | Manages, executes, creates, schedules, and delivers reports. The executable file is <MSSQLPATH>Reporting ServicesReportServerBinReportingServicesService.exe. |
| [!INCLUDEssISnoversion] | Provides management support for [!INCLUDEssISnoversion] package storage and execution. The executable path is <MSSQLPATH>150DTSBinnMsDtsSrvr.exe. |
[!INCLUDEssISnoversion] may include additional services for scale-out deployments. For more information, see Walkthrough: Set up Integration Services (SSIS) Scale Out.
| Service | Description |
|---|---|
| SQL Server Browser | The name resolution service that provides SQL Server connection information for client computers. The executable path is C:Program Files (x86)Microsoft SQL Server90Sharedsqlbrowser.exe |
| Full-text search | Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server. |
| SQL Writer | Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework. |
| SQL Server Distributed Replay Controller | Provides trace replay orchestration across multiple Distributed Replay client computers. |
| SQL Server Distributed Replay Client | One or more Distributed Replay client computers that work together with a Distributed Replay controller to simulate concurrent workloads against an instance of the [!INCLUDEssDEnoversion]. |
| [!INCLUDErsql_launchpad] | A trusted service that hosts external executables that are provided by Microsoft, such as the R or Python runtimes installed as part of R Services or Machine Learning Services. Satellite processes can be launched by the Launchpad process but is resource governed based on the configuration of the individual instance. The Launchpad service runs under its own user account, and each satellite process for a specific, registered runtime inherits the user account of the Launchpad. Satellite processes are created and destroyed on demand during execution time.
Launchpad can’t create the accounts it uses if you install SQL Server on a computer that is also used as a domain controller. Hence, setup of R Services (In-Database) or Machine Learning Services (In-Database) fails on a domain controller. |
| SQL Server PolyBase Engine | Provides distributed query capabilities to external data sources. |
| SQL Server PolyBase Data Movement Service | Enables data movement between SQL Server and External Data Sources and between SQL nodes in PolyBase Scaleout Groups. |
CEIP services installed by SQL Server
The Customer Experience Improvement Program (CEIP) service sends telemetry data back to Microsoft.
Depending on the components that you decide to install, SQL Server setup installs the following CEIP services.
| Service | Description |
|---|---|
| SQLTELEMETRY | The Customer Experience Improvement Program that sends database engine telemetry data back to Microsoft. |
| SSASTELEMETRY | The Customer Experience Improvement Program that sends SSAS telemetry data back to Microsoft. |
| SSISTELEMETRY | The Customer Experience Improvement Program that sends SSIS telemetry data back to Microsoft. |
Service properties and configuration
Startup accounts used to start and run SQL Server can be domain user accounts, local user accounts, managed service accounts, virtual accounts, or built-in system accounts. To start and run, each service in SQL Server must have a startup account configured during installation.
[!NOTE]
For SQL Server failover cluster instance for [!INCLUDE sssql16-md] and later, domain user accounts or group-managed service accounts can be used as startup accounts for SQL Server.
This section describes the accounts that can be configured to start SQL Server services, the default values used by SQL Server Setup, the concept of per-service SIDs, the startup options, and configuring the firewall.
- Default service accounts
- Automatic startup
- Configuring service StartupType
- Firewall port
Default service accounts
The following table lists the default service accounts used by setup when installing all components. The default accounts listed are the recommended accounts, except as noted.
Stand-alone server or domain controller
| Component | [!INCLUDEwinserver2008] | [!INCLUDE win7-md], [!INCLUDEwinserver2008] R2 and higher |
|---|---|---|
| [!INCLUDEssDE] | NETWORK SERVICE | Virtual account 1 |
| SQL Server Agent | NETWORK SERVICE | Virtual account 1 |
| [!INCLUDEssAS] | NETWORK SERVICE | Virtual account 1 2 |
| [!INCLUDEssIS] | NETWORK SERVICE | Virtual account 1 |
| [!INCLUDEssRS] | NETWORK SERVICE | Virtual account 1 |
| SQL Server Distributed Replay Controller | NETWORK SERVICE | Virtual account 1 |
| SQL Server Distributed Replay Client | NETWORK SERVICE | Virtual account 1 |
| FD Launcher (Full-text Search) | LOCAL SERVICE | Virtual account |
| SQL Server Browser | LOCAL SERVICE | LOCAL SERVICE |
| SQL Server VSS Writer | LOCAL SYSTEM | LOCAL SYSTEM |
| [!INCLUDErsql_extensions] | NTSERVICEMSSQLLaunchpad | NTSERVICEMSSQLLaunchpad |
| PolyBase Engine | NETWORK SERVICE | NETWORK SERVICE |
| PolyBase Data Movement Service | NETWORK SERVICE | NETWORK SERVICE |
1 When resources external to the SQL Server computer are needed, [!INCLUDEmsCoName] recommends using a managed service account (MSA), configured with the minimum privileges necessary.
2 When installed on a domain controller, a virtual account as the service account isn’t supported.
SQL Server failover cluster instance
| Component | [!INCLUDEwinserver2008] | [!INCLUDEwinserver2008] R2 |
|---|---|---|
| [!INCLUDEssDE] | 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. |
| [!INCLUDEssAS] | None. Provide a domain user account. | Provide a domain user account. |
| [!INCLUDEssIS] | NETWORK SERVICE | Virtual account |
| [!INCLUDEssRS] | 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 |
Change account properties
[!IMPORTANT]
Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the [!INCLUDEssDEnoversion] 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 [!INCLUDEssDE]. Other tools such as the Windows Services Control Manager can change the account name but doesn’t change all the required settings.
If you change service accounts for any SQL service by using other means, it can lead to unexpected behavior or errors. For example, if you change SQL Agent service account to a domain account using Windows services applet, you may notice that SQL agent jobs that use Operating System (Cmdexec), Replication or SSIS job steps may fail with an error like the following:
Executed as user : DomainAccount. The process could not be created for step Step Number of job Unique Job ID (reason: A required privilege is not held by the client). The step failed.To resolve this error, you should do the following using SQL Server Configuration Manager:
- Temporarily change the SQL Agent service account back to default virtual account (Default instance: NT ServiceSQLSERVERAGENT. Named instance: NT ServiceSQLAGENT$<instance_name>.)
- Restart SQL Server Agent Service
- Change the service account back to the desired domain account
- Restart SQL Server Agent service
For [!INCLUDEssASnoversion] instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for [!INCLUDEpower-pivot-service-md] applications and the [!INCLUDEanalysis-services-service-md]. Associated settings and permissions are updated to use the new account information when you use Central Administration.
To change [!INCLUDEssRSnoversion] 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 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 exampleDOMAINACCOUNTNAME$. 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.[!NOTE]
The MSA must be created in the Active Directory by the domain administrator before SQL Server setup can use it for SQL Server services. -
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 sign in without disruption immediately after a password change.
For more information, see Group Managed Service Accounts for Windows Server 2016 and later. For previous versions of Windows Server, see Group Managed Service Accounts.
[!NOTE]
The gMSA must be created in the Active Directory by the domain administrator before SQL Server setup can use it for SQL Server services. -
Virtual accounts
Virtual accounts (beginning with [!INCLUDE winserver2008r2-md] and [!INCLUDE win7-md]) 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 an SPN manually, see Manual SPN Registration.[!NOTE]
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.
Service Virtual account name Default instance of the [!INCLUDEssDE] service NT SERVICEMSSQLSERVERNamed instance of a [!INCLUDEssDE] service named PAYROLLNT SERVICEMSSQL$PAYROLLSQL Server Agent service on the default instance of SQL Server NT ServiceSQLSERVERAGENTSQL Server Agent service on an instance of SQL Server named PAYROLLNT SERVICESQLAGENT$PAYROLL
For more information on managed service accounts and virtual accounts, see the Managed service account and virtual account concepts section of Service Accounts Step-by-Step Guide and Managed Service Accounts Frequently Asked Questions (FAQ).
[!NOTE]
[!INCLUDEssNoteLowRights] Use a MSA, gMSA or virtual account when possible. When MSA, gMSA and virtual accounts aren’t 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. Don’t grant additional permissions to the SQL Server service account or the service groups. Permissions are granted through group membership or granted directly to a service SID, where a service SID is supported.
Automatic startup
In addition to having user accounts, every service has three possible startup states that users can control:
- Disabled. The service is installed but not currently running.
- Manual. The service is installed, but starts only when another service or application needs its functionality.
- Automatic. The service is automatically started by the operating system.
The startup state is selected during setup. When installing a named instance, the SQL Server Browser service should be set to start automatically.
Configure services during unattended installation
The following table shows the SQL Server services that can be configured during installation. For unattended installations, you can use the switches in a configuration file or at a command prompt.
| SQL Server service name | Switches for unattended installations 1 |
|---|---|
| MSSQLSERVER | SQLSVCACCOUNT, SQLSVCPASSWORD, SQLSVCSTARTUPTYPE |
| SQLServerAgent 2 | AGTSVCACCOUNT, AGTSVCPASSWORD, AGTSVCSTARTUPTYPE |
| MSSQLServerOLAPService | ASSVCACCOUNT, ASSVCPASSWORD, ASSVCSTARTUPTYPE |
| ReportServer | RSSVCACCOUNT, RSSVCPASSWORD, RSSVCSTARTUPTYPE |
| [!INCLUDEssISnoversion] | ISSVCACCOUNT, ISSVCPASSWORD, ISSVCSTARTUPTYPE |
| SQL Server Distributed Replay Controller | DRU_CTLR, CTLRSVCACCOUNT, CTLRSVCPASSWORD, CTLRSTARTUPTYPE, CTLRUSERS |
| SQL Server Distributed Replay Client | DRU_CLT, CLTSVCACCOUNT, CLTSVCPASSWORD, CLTSTARTUPTYPE, CLTCTLRNAME, CLTWORKINGDIR, CLTRESULTDIR |
| R Services or Machine Learning Services | EXTSVCACCOUNT, EXTSVCPASSWORD, ADVANCEDANALYTICS 3 |
| PolyBase Engine | PBENGSVCACCOUNT, PBENGSVCPASSWORD, PBENGSVCSTARTUPTYPE, PBDMSSVCACCOUNT, PBDMSSVCPASSWORD, PBDMSSVCSTARTUPTYPE, PBSCALEOUT, PBPORTRANGE |
1 For more information and sample syntax for unattended installations, see Install SQL Server from the Command Prompt.
2 The SQL Server Agent service is disabled on instances of [!INCLUDEssExpress] and [!INCLUDEssExpress] with Advanced Services.
3 Setting the account for Launchpad through the switches alone isn’t currently supported. Use SQL Server Configuration Manager to change the account and other service settings.
Firewall port
In most cases, when initially installed, the [!INCLUDEssDE] can be connected to by tools such as [!INCLUDEssManStudioFull] installed on the same computer as SQL Server. SQL Server Setup doesn’t open ports in the Windows firewall. Connections from other computers may not be possible until the [!INCLUDEssDE] is configured to listen on a TCP port, and the appropriate port is opened for connections in the Windows firewall. For more information, see Configure the Windows Firewall to Allow SQL Server Access.
Service permissions
This section describes the permissions that SQL Server Setup configures for the per-service SIDs of the SQL Server services.
- Service configuration and access control
- Windows privileges and rights
- File system permissions granted to SQL Server per-service SIDs or SQL Server local Windows groups
- File system permissions granted to other Windows user accounts or groups
- File system permissions related to unusual disk locations
- Reviewing additional considerations
- Registry permissions
- WMI
- Named pipes
Service configuration and access control
SQL Server 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 [!INCLUDEssDE] service might be NT ServiceMSSQL$<instance_name>. 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.
[!NOTE]
On [!INCLUDE win7-md] and [!INCLUDE winserver2008r2-md] (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.
When installing [!INCLUDEssAS], a per-service SID for the [!INCLUDEssASnoversion] service is created. A local Windows group is created, named in the format SQLServerMSASUser$<computer_name>$<instance_name>. The per-service SID NT SERVICEMSSQLServerOLAPService is granted membership in the local Windows group, and the local Windows group is granted the appropriate permissions in the ACL. If the account used to start the [!INCLUDEssASnoversion] service is changed, SQL Server Configuration Manager must change some Windows permissions (such as the right to log on as a service), but the permissions assigned to the local Windows group is still available without any updating, because the per-service SID hasn’t changed. This method allows the [!INCLUDEssASnoversion] service to be renamed during upgrades.
During SQL Server installation, SQL Server Setup creates a local Windows group for [!INCLUDEssAS] and the SQL Server Browser service. For these services, SQL Server configures the ACL for the local Windows groups.
Depending on the service configuration, the service account for a service or service SID is added as a member of the service group during install or upgrade.
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.
The following table shows permissions that SQL Server Setup requests for the per-service SIDs or local Windows groups used by SQL Server components.
| SQL Server Service | Permissions granted by SQL Server Setup |
|---|---|
| [!INCLUDEssDEnoversion]:
(All rights are granted to the per-service SID. Default instance: |
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege) Bypass traverse checking (SeChangeNotifyPrivilege) Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) Permission to start SQL Writer Permission to read the Event Log service Permission to read the Remote Procedure Call service |
| SQL Server Agent: 1
(All rights are granted to the per-service SID. Default instance: |
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege) Bypass traverse checking (SeChangeNotifyPrivilege) Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) |
| [!INCLUDEssAS]:
(All rights are granted to a local Windows group. Default instance: |
Log on as a service (SeServiceLogonRight)
For tabular only: Increase a process working set (SeIncreaseWorkingSetPrivilege) Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) Lock pages in memory (SeLockMemoryPrivilege) — this is needed only when paging is turned off entirely. For failover cluster installations only: Increase scheduling priority (SeIncreaseBasePriorityPrivilege) |
| [!INCLUDEssRS]:
(All rights are granted to the per-service SID. Default instance: |
Log on as a service (SeServiceLogonRight) |
| [!INCLUDEssIS]:
(All rights are granted to the per-service SID. Default instance and named instance: |
Log on as a service (SeServiceLogonRight)
Permission to write to application event log. Bypass traverse checking (SeChangeNotifyPrivilege) Impersonate a client after authentication (SeImpersonatePrivilege) |
| Full-text search:
(All rights are granted to the per-service SID. Default instance: |
Log on as a service (SeServiceLogonRight)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) Bypass traverse checking (SeChangeNotifyPrivilege) |
| SQL Server Browser:
(All rights are granted to a local Windows group. Default or named instance: |
Log on as a service (SeServiceLogonRight) |
| SQL Server VSS Writer:
(All rights are granted to the per-service SID. Default or named instance: |
The SQLWriter service runs under the LOCAL SYSTEM account that has all the required permissions. SQL Server setup doesn’t check or grant permissions for this service. |
| SQL Server Distributed Replay Controller: | Log on as a service (SeServiceLogonRight) |
| SQL Server Distributed Replay Client: | Log on as a service (SeServiceLogonRight) |
| PolyBase Engine and DMS: | Log on as a service (SeServiceLogonRight) |
| Launchpad: | Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege) Bypass traverse checking (SeChangeNotifyPrivilege) Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) |
| R Services/Machine Learning Services: SQLRUserGroup ([!INCLUDE sssql16-md] and [!INCLUDE sssql17-md]) | doesn’t have the Allow Log on locally permission by default |
| Machine Learning Services: ‘All Application Packages’ [AppContainer] ([!INCLUDE sssql19-md]) | Read and execute permissions to the SQL Server ‘Binn’, R_Services, and PYTHON_Services directories |
1 The SQL Server Agent service is disabled on instances of [!INCLUDEssExpress].
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.
[!IMPORTANT]
For failover cluster installations, resources on shared disks must be set to an ACL for a local account.
The following table shows the ACLs that are set by SQL Server Setup:
| Service account for | Files and folders | Access |
|---|---|---|
| MSSQLServer | InstidMSSQLbackup | Full control |
| InstidMSSQLbinn | Read, Execute | |
| InstidMSSQLdata | Full control | |
| InstidMSSQLFTData | Full control | |
| InstidMSSQLInstall | Read, Execute | |
| InstidMSSQLLog | Full control | |
| InstidMSSQLRepldata | Full control | |
| 150shared | Read, Execute | |
| InstidMSSQLTemplate Data ([!INCLUDEssExpress] only) | Read | |
| SQLServerAgent 1 | InstidMSSQLbinn | Full control |
| InstidMSSQLLog | Read, Write, Delete, Execute | |
| 150com | Read, Execute | |
| 150shared | Read, Execute | |
| 150sharedErrordumps | Read, Write | |
| ServerNameEventLog | Full control | |
| FTS | InstidMSSQLFTData | Full control |
| InstidMSSQLFTRef | Read, Execute | |
| 150shared | Read, Execute | |
| 150sharedErrordumps | Read, Write | |
| InstidMSSQLInstall | Read, Execute | |
| InstidMSSQLjobs | Read, Write | |
| MSSQLServerOLAPservice | 150sharedASConfig | Full control |
| InstidOLAP | Read, Execute | |
| InstidOlapData | Full control | |
| InstidOlapLog | Read, Write | |
| InstidOLAPBackup | Read, Write | |
| InstidOLAPTemp | Read, Write | |
| 150sharedErrordumps | Read, Write | |
| ReportServer | InstidReporting ServicesLog Files | Read, Write, Delete |
| InstidReporting ServicesReportServer | Read, Execute | |
| InstidReporting ServicesReportServerglobal.asax | Full control | |
| InstidReporting ServicesReportServerrsreportserver.config | Read | |
| InstidReporting ServicesRSTempfiles | Read, Write, Execute, Delete | |
| InstidReporting ServicesRSWebApp | Read, Execute | |
| 150shared | Read, Execute | |
| 150sharedErrordumps | Read, Write | |
| MSDTSServer100 | 150dtsbinnMsDtsSrvr.ini.xml | Read |
| 150dtsbinn | Read, Execute | |
| 150shared | Read, Execute | |
| 150sharedErrordumps | Read, Write | |
| SQL Server Browser | 150sharedASConfig | Read |
| 150shared | Read, Execute | |
| 150sharedErrordumps | Read, Write | |
| SQLWriter | N/A (Runs as local system) | |
| User | InstidMSSQLbinn | Read, Execute |
| InstidReporting ServicesReportServer | Read, Execute, List Folder Contents | |
| InstidReporting ServicesReportServerglobal.asax | Read | |
| InstidReporting ServicesRSWebApp | Read, Execute, List Folder Contents | |
| 150dts | Read, Execute | |
| 150tools | Read, Execute | |
| 100tools | Read, Execute | |
| 90tools | Read, Execute | |
| 80tools | Read, Execute | |
| 150sdk | Read | |
| Microsoft SQL Server150Setup Bootstrap | Read, Execute | |
| SQL Server Distributed Replay Controller | <ToolsDir>DReplayControllerLog (empty directory) | Read, Execute, List Folder Contents |
| <ToolsDir>DReplayControllerDReplayController.exe | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayControllerresources|Read, Execute, List Folder Contents | ||
| <ToolsDir>DReplayController{all dlls} | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayControllerDReplayController.config | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayControllerIRTemplate.tdf | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayControllerIRDefinition.xml | Read, Execute, List Folder Contents | |
| SQL Server Distributed Replay Client | <ToolsDir>DReplayClientLog|Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayClientDReplayClient.exe | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayClientresources|Read, Execute, List Folder Contents | ||
| <ToolsDir>DReplayClient (all dlls) | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayClientDReplayClient.config | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayClientIRTemplate.tdf | Read, Execute, List Folder Contents | |
| <ToolsDir>DReplayClientIRDefinition.xml | Read, Execute, List Folder Contents | |
| Launchpad | %binn | Read, Execute |
| ExtensiblilityData | Full control | |
| LogExtensibilityLog | Full control |
1 The SQL Server Agent service is disabled on instances of [!INCLUDEssExpress] and [!INCLUDEssExpress] with Advanced Services.
When database files are stored in a user-defined location, you must grant the per-service SID access to that location. For more information about granting file system permissions to a per-service SID, see Configure File System Permissions for Database Engine Access.
File system permissions granted to other Windows user accounts or groups
Some access control permissions might have to be granted to built-in accounts or other SQL Server service accounts. The following table lists additional ACLs that are set by SQL Server Setup.
| Requesting component | Account | Resource | Permissions |
|---|---|---|---|
| MSSQLServer | Performance Log Users | InstidMSSQLbinn | List folder contents |
| Performance Monitor Users | InstidMSSQLbinn | List folder contents | |
| Performance Log Users, Performance Monitor Users | WINNTsystem32sqlctr150.dll | Read, Execute | |
| Administrator only | \.rootMicrosoftSqlServerServerEvents<sql_instance_name> 1 |
Full control | |
| Administrators, System | toolsbinnschemassqlserver20047showplan | Full control | |
| Users | toolsbinnschemassqlserver20047showplan | Read, Execute | |
| [!INCLUDEssRSnoversion] | Report Server Windows Service Account | <install>Reporting ServicesLogFiles | DELETE
READ_CONTROL SYNCHRONIZE FILE_GENERIC_READ FILE_GENERIC_WRITE FILE_READ_DATA FILE_WRITE_DATA FILE_APPEND_DATA FILE_READ_EA FILE_WRITE_EA FILE_READ_ATTRIBUTES FILE_WRITE_ATTRIBUTES |
| Report Server Windows Service Account | <install>Reporting ServicesReportServer | Read | |
| Report Server Windows Service Account | <install>Reporting ServicesReportServerglobal.asax | Full | |
| Report Server Windows Service Account | <install>Reporting ServicesRSWebApp | Read, Execute | |
| Everyone | <install>Reporting ServicesReportServerglobal.asax | READ_CONTROL
FILE_READ_DATA FILE_READ_EA FILE_READ_ATTRIBUTES |
|
| ReportServer Windows Services Account | <install>Reporting ServicesReportServerrsreportserver.config | DELETE
READ_CONTROL SYNCHRONIZE FILE_GENERIC_READ FILE_GENERIC_WRITE FILE_READ_DATA FILE_WRITE_DATA FILE_APPEND_DATA FILE_READ_EA FILE_WRITE_EA FILE_READ_ATTRIBUTES FILE_WRITE_ATTRIBUTES |
|
| Everyone | Report Server keys (Instid hive) | Query Value
Enumerate SubKeys Notify Read Control |
|
| Terminal Services User | Report Server keys (Instid hive) | Query Value
Set Value Create SubKey Enumerate SubKey Notify Delete Read Control |
|
| Power Users | Report Server keys (Instid hive) | Query Value
Set Value Create Subkey Enumerate Subkeys Notify Delete Read Control |
1 This is the WMI provider namespace.
File system permissions related to unusual disk locations
The default drive for locations for installation is system drive, normally drive C. This section describes additional considerations when tempdb or user databases are installed to unusual locations.
Non-default drive
When installed to a local drive that isn’t the default drive, the per-service SID must have access to the file location. SQL Server Setup provisions the required access.
Network share
When databases are installed to a network share, the service account must have access to the file location of the user and tempdb databases. SQL Server Setup can’t provision access to a network share. The user must provision access to a tempdb location for the service account before running setup. The user must provision access to the user database location before creating the database.
[!NOTE]
Virtual accounts can’t be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format<domain_name><computer_name>$.
Review additional considerations
The following table shows the permissions that are required for SQL Server services to provide additional functionality.
| Service/Application | Functionality | Required permission |
|---|---|---|
| SQL Server (MSSQLSERVER) | Write to a mail slot using xp_sendmail. | Network write permissions. |
| SQL Server (MSSQLSERVER) | Run xp_cmdshell for a user other than a SQL Server administrator. | Act as part of operating system and replace a process-level token. |
| SQL Server Agent (MSSQLSERVER) | Use the auto restart feature. | Must be a member of the Administrators local group. |
| [!INCLUDEssDE] Tuning Advisor | Tunes databases for optimal query performance. | On first use, a user who has system administrative credentials must initialize the application. After initialization, dbo users can use the [!INCLUDEssDE] Tuning Advisor to tune only those tables that they own. For more information, see Start and use the Database Engine Tuning Advisor. |
[!IMPORTANT]
Before you upgrade SQL Server, enable SQL Server Agent and verify the required default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin fixed server role.
Registry permissions
The registry hive is created under HKLMSoftwareMicrosoftMicrosoft SQL Server<Instance_ID> for instance-aware components. For example:
HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL15.MyInstanceHKLMSoftwareMicrosoftMicrosoft SQL ServerMSASSQL15.MyInstanceHKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.150
The registry also maintains a mapping of instance ID to instance name. Instance ID to instance name mapping is maintained as follows:
[HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL] "InstanceName"="MSSQL15"[HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL ServerInstance NamesOLAP] "InstanceName"="MSASSQL15"[HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL ServerInstance NamesRS] "InstanceName"="MSRSSQL15"
WMI
Windows Management Instrumentation (WMI) must be able to connect to the [!INCLUDEssDE]. To support this, the per-service SID of the Windows WMI provider (NT SERVICEwinmgmt) is provisioned in the [!INCLUDEssDE].
The SQL WMI provider requires the following minimal permissions:
-
Membership in the db_ddladmin or db_owner fixed database roles in the
msdbdatabase. -
CREATE DDL EVENT NOTIFICATION permission in the server.
-
CREATE TRACE EVENT NOTIFICATION permission in the [!INCLUDEssDE].
-
VIEW ANY DATABASE server-level permission.
SQL Server setup creates a SQL WMI namespace and grants read permission to the SQL Server Agent service-SID.
Named pipes
In all installation, SQL Server Setup provides access to the [!INCLUDEssDEnoversion] through the shared memory protocol, which is a local named pipe.
Provisioning
This section describes how accounts are provisioned inside the various SQL Server components.
-
Database Engine provisioning
- Windows principals
- SA account
- SQL Server per-service SID login and privileges
- SQL Server Agent login and privileges
- HADRON and SQL failover cluster instance and privileges
- SQL Writer and privileges
- SQL WMI and privileges
-
SSAS provisioning
-
SSRS provisioning
Database Engine provisioning
The following accounts are added as logins in the [!INCLUDEssDEnoversion].
Windows principals
During setup, SQL Server Setup requires at least one user account to be named as a member of the sysadmin fixed server role.
SA account
The sa account is always present as a [!INCLUDEssDE] login and is a member of the sysadmin fixed server role. When the [!INCLUDEssDE] is installed using only Windows Authentication (that is when SQL Server Authentication isn’t enabled), the sa login is still present but is disabled and the password is complex and random. For information about enabling the sa account, see Change Server Authentication Mode.
SQL Server per-service SID login and privileges
The per-service SID (sometimes also called service security principal (SID)) of the SQL Server service is provisioned as a [!INCLUDEssDE] login. The per-service SID login is a member of the sysadmin fixed server role. For information about per-service SID, see Using Service SIDs to grant permissions to services in SQL Server.
SQL Server Agent login and privileges
The per-service SID of the SQL Server Agent service is provisioned as a [!INCLUDEssDE] login. The per-service SID login is a member of the sysadmin fixed server role.
[!INCLUDEsshadr] and SQL failover cluster instance and privileges
When installing the [!INCLUDEssDE] as a [!INCLUDEssHADR] or SQL failover cluster instance (SQL FCI), LOCAL SYSTEM is provisioned in the [!INCLUDEssDE]. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for [!INCLUDEssHADR]) and the VIEW SERVER STATE permission (for SQL FCI).
SQL Writer and privileges
The per-service SID of the SQL Server VSS Writer service is provisioned as a [!INCLUDEssDE] login. The per-service SID login is a member of the sysadmin fixed server role.
SQL WMI and privileges
SQL Server Set up provisions the NT SERVICEWinmgmt account as a [!INCLUDEssDE] login and adds it to the sysadmin fixed server role.
SSRS provisioning
The account specified during setup is provisioned as a member of the RSExecRole database role. For more information, see Configure the Report Server Service Account (SSRS Configuration Manager).
SSAS provisioning
[!INCLUDEssAS] service account requirements vary depending on how you deploy the server. If you’re installing [!INCLUDEpower-pivot-sharepoint-md], SQL Server Setup requires that you configure the [!INCLUDEssASnoversion] service to run under a domain account. Domain accounts are required to support the managed account facility that is built into SharePoint. For this reason, SQL Server Setup doesn’t provide a default service account, such as a virtual account, for a [!INCLUDEpower-pivot-sharepoint-md] installation. For more information about provisioning [!INCLUDEpower-pivot-md] for SharePoint, see Configure Power Pivot Service Accounts.
For all other standalone [!INCLUDEssAS] installations, you can provision the service to run under a domain account, built-in system account, managed account, or virtual account. For more information about account provisioning, see Configure Service Accounts (Analysis Services).
For clustered installations, you must specify a domain account or a built-in system account. Neither managed accounts nor virtual accounts are supported for [!INCLUDEssAS] failover clusters.
All [!INCLUDEssAS] installations require that you specify a system administrator of the [!INCLUDEssASnoversion] instance. Administrator privileges are provisioned in the Analysis Services Server role.
SSRS provisioning
The account specified during setup is provisioned in the [!INCLUDEssDE] as a member of the RSExecRole database role. For more information, see Configure the Report Server Service Account (SSRS Configuration Manager).
Upgrade from previous versions
This section describes the changes made during upgrade from a previous version of SQL Server.
-
[!INCLUDE sssql19-md] requires a supported operating system. Any previous version of SQL Server running on a lower operating system version must have the operating system upgraded before upgrading SQL Server.
-
During upgrade of [!INCLUDEssVersion2005] to [!INCLUDE sssql19-md] setup configures the SQL Server instance in the following way:
- The [!INCLUDEssDE] runs with the security context of the per-service SID. The per-service SID is granted access to the file folders of the SQL Server instance (such as DATA), and the SQL Server registry keys.
- The per-service SID of the [!INCLUDEssDE] is provisioned in the [!INCLUDEssDE] as a member of the sysadmin fixed server role.
- The per-service SIDs are added to the local SQL Server Windows groups, unless SQL Server is a failover cluster instance.
- The SQL Server resources remain provisioned to the local SQL Server Windows groups.
- The local Windows group for services is renamed from
SQLServer2005MSSQLUser$<computer_name>$<instance_name>toSQLServerMSSQLUser$<computer_name>$<instance_name>. File locations for migrated databases has Access Control Entries (ACE) for the local Windows groups. The file locations for new databases has ACEs for the per-service SID.
-
During upgrade from [!INCLUDEsql2008-md], SQL Server Setup preserves the ACEs for the [!INCLUDEsql2008-md] per-service SID.
-
For a SQL Server failover cluster instance, the ACE for the domain account configured for the service are retained.
Appendix
This section contains additional information about SQL Server services.
- Description of service Accounts
- Identifying instance-aware and instance-unaware services
- Localized service names
Description of service accounts
The service account is the account used to start a Windows service, such as the [!INCLUDEssDEnoversion]. For running SQL Server, it isn’t required to add the Service Account as a Login to SQL Server in addition to the Service SID, which is always present and a member of the sysamin fixed server role.
Accounts available with any operating system
In addition to the new MSA, gMSA and virtual accounts described earlier, the following accounts can be used.
Domain user account
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
If the computer isn’t part of a domain, a local user account without Windows administrator permissions is recommended.
Local Service account
The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. This limited access helps safeguard the system if individual services or processes are compromised. Services that run as the local service account access network resources as a null session without credentials.
The local service account isn’t 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.
The actual name of the account is NT AUTHORITYLOCAL SERVICE.
Network Service account
The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. 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>$. The actual name of the account is NT AUTHORITYNETWORK SERVICE.
Local System account
Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITYSYSTEM.
Identify instance-aware and instance-unaware services
Instance-aware services are associated with a specific instance of SQL Server, and have their own registry hives. You can install multiple copies of instance-aware services by running SQL Server Setup for each component or service. Instance-unaware services are shared among all installed SQL Server instances. They aren’t associated with a specific instance, are installed only once, and can’t be installed side by side.
Instance-aware services in SQL Server include the following:
-
SQL Server
-
SQL Server Agent
Be aware that the SQL Server Agent service is disabled on instances of [!INCLUDEssExpress] and [!INCLUDEssExpress] with Advanced Services.
-
[!INCLUDEssASnoversion]
Analysis Services in SharePoint integrated mode runs as ‘[!INCLUDEpower-pivot-md]’ as a single, named instance. The instance name is fixed. You can’t specify a different name. You can install only one instance of Analysis Services running as ‘[!INCLUDEpower-pivot-md]’ on each physical server.
-
[!INCLUDEssRSnoversion]
-
Full-text search
Instance-unaware services in SQL Server include the following:
- [!INCLUDEssISnoversion]
- SQL Server Browser
- SQL Writer
Localized service names
The following table shows service names that are displayed by localized versions of Windows.
| Language | Name for Local Service | Name for Network Service | Name for Local System | Name for Admin Group |
|---|---|---|---|---|
| English
Simplified Chinese Traditional Chinese Korean Japanese |
NT AUTHORITYLOCAL SERVICE |
NT AUTHORITYNETWORK SERVICE |
NT AUTHORITYSYSTEM |
BUILTINAdministrators |
| German | NT-AUTORITÄTLOKALER DIENST |
NT-AUTORITÄTNETZWERKDIENST |
NT-AUTORITÄTSYSTEM |
VORDEFINIERTAdministratoren |
| French | AUTORITE NTSERVICE LOCAL |
AUTORITE NTSERVICE RÉSEAU |
AUTORITE NTSYSTEM |
BUILTINAdministrators |
| Italian | NT AUTHORITYSERVIZIO LOCALE |
NT AUTHORITYSERVIZIO DI RETE |
NT AUTHORITYSYSTEM |
BUILTINAdministrators |
| Spanish | NT AUTHORITYSERVICIO LOC |
NT AUTHORITYSERVICIO DE RED |
NT AUTHORITYSYSTEM |
BUILTINAdministradores |
| Russian | NT AUTHORITYLOCAL SERVICE |
NT AUTHORITYNETWORK SERVICE |
NT AUTHORITYСИСТЕМА |
BUILTINАдминистраторы |
Next steps
- Security Considerations for a SQL Server Installation
- File Locations for Default and Named Instances of SQL Server
- Install Master Data Services
- Remove From My Forums
-
Question
-
Hi,
For SQL Server 2012, I have a SQL Server Service running using the NT ServiceMSSQLSERVER, as shown below:
From looking around at other posts, it looks like this is not a «real» windows account that I could login as, but it’s a service account.
I’d like to try running the default SQL Server service as a Windows domain account. However, I notice in the screenshot above, the NT ServiceMSSQLSERVER user has a «password» in the password field, and I don’t know what that value is.
If I try using a Windows domain account to run this service, and then decide I want to go back to it using NT ServiceMSSQLSERVER, am I going to be able to do that without knowing the password to that user? Does it even *have* a password? I’m
concerned, because if I type in NT ServiceMSSQLSERVER in the Account Name: box, the password field becomes blank.In other words, everything works right now. But I’m afraid if I change the user for the SQL Service, I’m afraid I won’t be able to change it back.
-
Edited by
Tuesday, December 27, 2016 4:24 PM
-
Edited by
Answers
-
Hello huskar13,
NT ServiceMSSQLSERVER is a «virtual account», so it doesn’t have a password. If you change the account details for another user and then if you want to be using NT ServerMSSQLSERVER virtual account again, you just need to type the account name
and leave the password blank and hit Apply.For more information about the virtual accounts pay a visit to:
https://msdn.microsoft.com/en-us/library/ms143504.aspx
http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy
-
Proposed as answer by
Lin LengMicrosoft contingent staff
Wednesday, December 28, 2016 2:11 AM -
Marked as answer by
huskar13
Thursday, December 29, 2016 11:52 PM
-
Proposed as answer by
- Remove From My Forums
-
Question
-
Hi Guys,
I’ve done a fair amount of research for this question but just cannot seem to find the answer to my question in simple, non-DBA, terms.
Server 2008 R2
SQL 2008 R2
There are 2 users in the system database logins (NT SERVICEMSSQLSERVER, NT SERVICESQLSERVERAGENT) … what are they for? It appears that they
are accounts to run the corresponding Windows services but yet they cannot be selected from the list of available built-in accounts, local accounts or domain accounts.Also, I am using a couple of domain user accounts to run the services, do I need to add them to the database? I changed the service accounts from NETWORK
SERVICE to the domain user accounts using the SQL Configuration Manger which is supposed to take care of managing the user group membership and registry changes but the domain accounts are not in the database …. The services appear to be running fine.Thanks
Answers
-
In basic terms:
As you say, in the SQL Server Database Engine there are two logins;
NT SERVICEMSSQLSERVER and NT SERVICESQLSERVERAGENT. The Database Engine runs in Windows as a Windows service named
MSSQLSERVER. The NT SERVICEMSSQLSERVER login is used by the service to connect to the Database Engine. Basically, this is how it connects to itself. The SQL Server Agent runs as a Windows service named
NT SERVICESQLSERVERAGENT. The NT SERVICESQLSERVERAGENT
login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the
msdb database to find out what it should do; and then do it. Both of these logins are members of the
sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.No, they can’t be selected in the list of available built-in accounts, local accounts or domain accounts. This is because they are services, not accounts. They have a security identifier (SID) in Windows,
but Windows knows they aren’t real users. Windows can authenticate them, but they don’t have passwords that any human can use. If you run
lusrmgr.msc and look at the groups, you will see groups like
SQLServerMSSQLUser$computername$MSSQLSERVER and NT SERVICEMSSQLSERVER
is a member of the group.As for the account that you used to run the services, this is complicated and has changed from SQL Server 2005 to SQL Server 2008 and now again in SQL Server Code Named ‘Denali’. The short answer is that
the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group
SQLServerMSSQLUser$computername$MSSQLSERVER.The good news is that SQL Server Configuration Manager figures out all the stuff you need when you change the accounts. If you are a glutton for punishment, you can get an idea for how complicated this
is by looking at the Denali documentation where I have tried to provide more specific information. (Note this is not the same as SQL Server 2008.) You can see it at:
Configure Windows Service Accounts and Permissions
http://msdn.microsoft.com/en-us/library/ms143504(SQL.110).aspx
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
-
Proposed as answer by
Sunday, July 17, 2011 2:36 AM
-
Marked as answer by
Stephanie Lv
Tuesday, July 26, 2011 8:52 AM
-
Proposed as answer by
Loading