Skip to content

configure-windows-service-accounts-and-permissions.md

Latest commit

 

History

History
622 lines (452 loc) · 62.4 KB

configure-windows-service-accounts-and-permissions.md

File metadata and controls

622 lines (452 loc) · 62.4 KB
title Configure Windows Service Accounts and Permissions | Microsoft Docs
description 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.
ms.custom contperf-fy20q4
ms.date 03/17/2020
ms.prod sql
ms.prod_service high-availability
ms.reviewer
ms.technology configuration
ms.topic reference
helpviewer_keywords
startup service states [SQL Server]
Setup [SQL Server], user accounts
Windows permissions [SQL Server]
modifying user accounts
default accounts
domains [SQL Server], user accounts
startup accounts [SQL Server]
system accounts [SQL Server]
services [SQL Server], permissions
ACL (access control list)
local system accounts [SQL Server]
instance-aware services [SQL Server]
permissions [SQL Server], services
SQL Server Agent service, user accounts
Windows NT permissions [SQL Server]
user accounts [SQL Server]
identifying instance-unaware services [SQL Server]
installing SQL Server, user accounts
disabled startup state [SQL Server]
user accounts [SQL Server], users
Local Service account [SQL Server]
SQL Server Installation Wizard
instance-unaware services [SQL Server]
services [SQL Server], configuring at installation
Windows accounts [SQL Server]
SQL Server services, user accounts
user accounts [SQL Server], services
MSSQLServer
identifying instance-aware services [SQL Server]
services [SQL Server], accounts
access control lists
optional accounts [SQL Server]
service accounts [SQL Server]
accounts [SQL Server], services
built-in system accounts [SQL Server]
automatic startup state
domains [SQL Server]
manual startup state [SQL Server]
accounts [SQL Server], user
ms.assetid 309b9dac-0b3a-4617-85ef-c4519ce9d014
author markingmyname
ms.author maghan

Configure Windows Service Accounts and Permissions

[!INCLUDE SQL Server]

Each service in [!INCLUDEssNoVersion] represents a process or a set of processes to manage authentication of [!INCLUDEssNoVersion] operations with Windows. This topic describes the default configuration of services in this release of [!INCLUDEssNoVersion], and configuration options for [!INCLUDEssNoVersion] services that you can set during and after [!INCLUDEssNoVersion] installation. This topic helps advanced users understand the details of the service accounts.

Most services and their properties can be configured by using [!INCLUDEssNoVersion] Configuration Manager. Here are the paths to the last four versions when Windows is installed on the C drive.

SQL Server version Path
SQL Server 2019 C:\Windows\SysWOW64\SQLServerManager15.msc
SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc
[!INCLUDEssNoVersion] 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
[!INCLUDEssSQL14] C:\Windows\SysWOW64\SQLServerManager12.msc
[!INCLUDEssSQL11] C:\Windows\SysWOW64\SQLServerManager11.msc

Services Installed by [!INCLUDEssNoVersion]

Depending on the components that you decide to install, [!INCLUDEssNoVersion] Setup installs the following services:

  • [!INCLUDEssNoVersion] Database Services - The service for the [!INCLUDEssNoVersion] relational [!INCLUDEssDE]. The executable file is <MSSQLPATH>\MSSQL\Binn\sqlservr.exe.

  • [!INCLUDEssNoVersion] Agent - Executes jobs, monitors [!INCLUDEssNoVersion], fires alerts, and enables automation of some administrative tasks. The [!INCLUDEssNoVersion] Agent service is present but disabled on instances of [!INCLUDEssExpress]. The executable file is <MSSQLPATH>\MSSQL\Binn\sqlagent.exe.

  • [!INCLUDEssASnoversion] - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications. The executable file is <MSSQLPATH>\OLAP\Bin\msmdsrv.exe.

  • [!INCLUDEssRSnoversion] - Manages, executes, creates, schedules, and delivers reports. The executable file is <MSSQLPATH>\Reporting Services\ReportServer\Bin\ReportingServicesService.exe.

  • [!INCLUDEssISnoversion] - Provides management support for [!INCLUDEssISnoversion] package storage and execution. The executable path is <MSSQLPATH>\130\DTS\Binn\MsDtsSrvr.exe

    [!INCLUDEssISnoversion] may include additional services for scale out deployments. For more information, see Walkthrough: Set up Integration Services (SSIS) Scale Out.

  • [!INCLUDEssNoVersion] Browser - The name resolution service that provides [!INCLUDEssNoVersion] connection information for client computers. The executable path is c:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.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 [!INCLUDEssNoVersion].

  • SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

  • [!INCLUDEssNoVersion] Distributed Replay Controller - Provides trace replay orchestration across multiple Distributed Replay client computers.

  • [!INCLUDEssNoVersion] 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 will be 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 will inherit the user account of the Launchpad. Satellite processes are created and destroyed on demand during execution time.

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

Service Properties and Configuration

Startup accounts used to start and run [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] must have a startup account configured during installation.

This section describes the accounts that can be configured to start [!INCLUDEssNoVersion] services, the default values used by [!INCLUDEssNoVersion] Setup, the concept of per-service SID's, the startup options, and configuring the firewall.

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 [!INCLUDEnextref_longhorn] Windows 7 and [!INCLUDEnextref_longhorn] R2 and higher
[!INCLUDEssDE] NETWORK SERVICE Virtual Account*
[!INCLUDEssNoVersion] Agent NETWORK SERVICE Virtual Account*
[!INCLUDEssAS] NETWORK SERVICE Virtual Account* **
[!INCLUDEssIS] NETWORK SERVICE Virtual Account*
[!INCLUDEssRS] NETWORK SERVICE Virtual Account*
[!INCLUDEssNoVersion] Distributed Replay Controller NETWORK SERVICE Virtual Account*
[!INCLUDEssNoVersion] Distributed Replay Client NETWORK SERVICE Virtual Account*
FD Launcher (Full-text Search) LOCAL SERVICE Virtual Account
[!INCLUDEssNoVersion] Browser LOCAL SERVICE LOCAL SERVICE
[!INCLUDEssNoVersion] VSS Writer LOCAL SYSTEM LOCAL SYSTEM
[!INCLUDErsql_extensions] NTSERVICE\MSSQLLaunchpad NTSERVICE\MSSQLLaunchpad
PolyBase Engine NETWORK SERVICE NETWORK SERVICE
PolyBase Data Movement Service NETWORK SERVICE NETWORK SERVICE

*When resources external to the [!INCLUDEssNoVersion] computer are needed, [!INCLUDEmsCoName] 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.

SQL Server Failover Cluster Instance

Component [!INCLUDEnextref_longhorn] [!INCLUDEnextref_longhorn] R2
[!INCLUDEssDE] None. Provide a domain user account. Provide a domain user account.
[!INCLUDEssNoVersion] 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
[!INCLUDEssNoVersion] Browser LOCAL SERVICE LOCAL SERVICE
[!INCLUDEssNoVersion] VSS Writer LOCAL SYSTEM LOCAL SYSTEM

Changing Account Properties

Important

  • Always use [!INCLUDEssNoVersion] tools such as [!INCLUDEssNoVersion] Configuration Manager to change the account used by the [!INCLUDEssDEnoversion] or [!INCLUDEssNoVersion] Agent services, or to change the password for the account. In addition to changing the account name, [!INCLUDEssNoVersion] 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 do not change all the required settings.
  • For [!INCLUDEssASnoversion] instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for [!INCLUDEssGeminiMTS] applications and the [!INCLUDEssGeminiSrv]. 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 [!INCLUDEssNoVersion] 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 DOMAIN\ACCOUNTNAME$. 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.

    [!NOTE] The MSA must be created in the Active Directory by the domain administrator before [!INCLUDEssNoVersion] setup can use it for [!INCLUDEssNoVersion] 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 log in without disruption immediately after a password change.

    For more information, see Group Managed Service Accounts

    [!NOTE] The gMSA must be created in the Active Directory by the domain administrator before [!INCLUDEssNoVersion] setup can use it for [!INCLUDEssNoVersion] services.

  • 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion], 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.

    [!NOTE] Virtual accounts cannot be used for [!INCLUDEssNoVersion] 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 SERVICE\MSSQLSERVER
    Named instance of a [!INCLUDEssDE] service named PAYROLL NT SERVICE\MSSQL$PAYROLL
    [!INCLUDEssNoVersion] Agent service on the default instance of [!INCLUDEssNoVersion] NT SERVICE\SQLSERVERAGENT
    [!INCLUDEssNoVersion] Agent service on an instance of [!INCLUDEssNoVersion] named PAYROLL NT SERVICE\SQLAGENT$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).

Security Note: [!INCLUDEssNoteLowRights] 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 [!INCLUDEssNoVersion] services. Use separate accounts for different [!INCLUDEssNoVersion] services. Do not grant additional permissions to the [!INCLUDEssNoVersion] 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.

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 will start 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 [!INCLUDEssNoVersion] Browser service should be set to start automatically.

Configuring services during unattended installation

The following table shows the [!INCLUDEssNoVersion] 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*
MSSQLSERVER SQLSVCACCOUNT, SQLSVCPASSWORD, SQLSVCSTARTUPTYPE
SQLServerAgent** AGTSVCACCOUNT, AGTSVCPASSWORD, AGTSVCSTARTUPTYPE
MSSQLServerOLAPService ASSVCACCOUNT, ASSVCPASSWORD, ASSVCSTARTUPTYPE
ReportServer RSSVCACCOUNT, RSSVCPASSWORD, RSSVCSTARTUPTYPE
[!INCLUDEssISnoversion] ISSVCACCOUNT, ISSVCPASSWORD, ISSVCSTARTUPTYPE
[!INCLUDEssNoVersion] Distributed Replay Controller DRU_CTLR, CTLRSVCACCOUNT,CTLRSVCPASSWORD, CTLRSTARTUPTYPE, CTLRUSERS
[!INCLUDEssNoVersion] Distributed Replay Client DRU_CLT, CLTSVCACCOUNT, CLTSVCPASSWORD, CLTSTARTUPTYPE, CLTCTLRNAME, CLTWORKINGDIR, CLTRESULTDIR
R Services or Machine Learning Services EXTSVCACCOUNT, EXTSVCPASSWORD, ADVANCEDANALYTICS***
PolyBase Engine PBENGSVCACCOUNT, PBENGSVCPASSWORD, PBENGSVCSTARTUPTYPE, PBDMSSVCACCOUNT,PBDMSSVCPASSWORD, PBDMSSVCSTARTUPTYPE, PBSCALEOUT, PBPORTRANGE

*For more information and sample syntax for unattended installations, see Install SQL Server 2016 from the Command Prompt.

**The [!INCLUDEssNoVersion] Agent service is disabled on instances of [!INCLUDEssExpress] and [!INCLUDEssExpress] with Advanced Services.

***Setting the account for Launchpad through the switches alone is not 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 [!INCLUDEssNoVersion]. [!INCLUDEssNoVersion] Setup does not 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 [!INCLUDEssNoVersion] Setup configures for the per-service SID's of the [!INCLUDEssNoVersion] services.

Service Configuration and Access Control

[!INCLUDEssCurrent] 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 Service\MSSQL$<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 [!INCLUDEssNoVersion] service can restrict access to its resources.

Note

On Windows 7 and [!INCLUDEnextref_longhorn] R2 (and later) the per-service SID can be the virtual account used by the service.

For most components [!INCLUDEssNoVersion] 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 SERVICE\MSSQLServerOLAPService 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, [!INCLUDEssNoVersion] 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 will still be available without any updating, because the per-service SID has not changed. This method allows the [!INCLUDEssASnoversion] service to be renamed during upgrades.

During [!INCLUDEssNoVersion] installation, [!INCLUDEssNoVersion] Setup creates a local Windows groups for [!INCLUDEssAS] and the [!INCLUDEssNoVersion] Browser service. For these services, [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Setup requests for the per-service SIDs or local Windows groups used by [!INCLUDEssNoVersion] components.

[!INCLUDEssNoVersion] Service Permissions granted by [!INCLUDEssNoVersion] Setup
[!INCLUDEssDEnoversion]:

(All rights are granted to the per-service SID. Default instance: NT SERVICE\MSSQLSERVER. Named instance: NT Service\MSSQLServer$InstanceName.)		 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
[!INCLUDEssNoVersion] Agent: *

(All rights are granted to the per-service SID. Default instance: NT Service\SQLSERVERAGENT. Named instance: NT Service\SQLAGENT$InstanceName.)		 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: SQLServerMSASUser$ComputerName$MSSQLSERVER. Named instance: SQLServerMSASUser$ComputerName$InstanceName. [!INCLUDEssGeminiShort] instance: SQLServerMSASUser$ComputerName$PowerPivot.)		 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: NT SERVICE\ReportServer. Named instance: NT SERVICE\ReportServer$InstanceName.)		 Log on as a service (SeServiceLogonRight)
[!INCLUDEssIS]:

(All rights are granted to the per-service SID. Default instance and named instance: NT SERVICE\MsDtsServer130. [!INCLUDEssISnoversion] does not have a separate process for a 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: NT Service\MSSQLFDLauncher. Named instance: NT Service\ MSSQLFDLauncher$InstanceName.)		 Log on as a service (SeServiceLogonRight)

Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

Bypass traverse checking (SeChangeNotifyPrivilege)
[!INCLUDEssNoVersion] Browser:

(All rights are granted to a local Windows group. Default or named instance: SQLServer2005SQLBrowserUser$ComputerName. [!INCLUDEssNoVersion] Browser does not have a separate process for a named instance.)		 Log on as a service (SeServiceLogonRight)
[!INCLUDEssNoVersion] VSS Writer:

(All rights are granted to the per-service SID. Default or named instance: NT Service\SQLWriter. [!INCLUDEssNoVersion] VSS Writer does not have a separate process for a named instance.)		 The SQLWriter service runs under the LOCAL SYSTEM account which has all the required permissions. [!INCLUDEssNoVersion] setup does not check or grant permissions for this service.
[!INCLUDEssNoVersion] Distributed Replay Controller: Log on as a service (SeServiceLogonRight)
[!INCLUDEssNoVersion] 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 (SQL 2016 and 2017) Does not have the Allow Log on locally permission by default
Machine Learning Services 'All Application Packages' [AppContainer] (SQL 2019) Read and execute permissions to the SQL Server 'Binn', R_Services, and PYTHON_Services directories

*The [!INCLUDEssNoVersion] Agent service is disabled on instances of [!INCLUDEssExpress].

File System Permissions Granted to SQL Server Per-service SIDs or Local Windows Groups

[!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Setup:

Service account for Files and folders Access
MSSQLServer Instid\MSSQL\backup Full control
Instid\MSSQL\binn Read, Execute
Instid\MSSQL\data Full control
Instid\MSSQL\FTData Full control
Instid\MSSQL\Install Read, Execute
Instid\MSSQL\Log Full control
Instid\MSSQL\Repldata Full control
130\shared Read, Execute
Instid\MSSQL\Template Data ([!INCLUDEssExpress] only) Read
SQLServerAgent* Instid\MSSQL\binn Full control
Instid\MSSQL\Log Read, Write, Delete, Execute
130\com Read, Execute
130\shared Read, Execute
130\shared\Errordumps Read, Write
ServerName\EventLog Full control
FTS Instid\MSSQL\FTData Full control
Instid\MSSQL\FTRef Read, Execute
130\shared Read, Execute
130\shared\Errordumps Read, Write
Instid\MSSQL\Install Read, Execute
Instid\MSSQL\jobs Read, Write
MSSQLServerOLAPservice 130\shared\ASConfig Full control
Instid\OLAP Read, Execute
Instid\Olap\Data Full control
Instid\Olap\Log Read, Write
Instid\OLAP\Backup Read, Write
Instid\OLAP\Temp Read, Write
130\shared\Errordumps Read, Write
ReportServer Instid\Reporting Services\Log Files Read, Write, Delete
Instid\Reporting Services\ReportServer Read, Execute
Instid\Reporting Services\ReportServer\global.asax Full control
Instid\Reporting Services\ReportServer\rsreportserver.config Read
Instid\Reporting Services\RSTempfiles Read, Write, Execute, Delete
Instid\Reporting Services\RSWebApp Read, Execute
130\shared Read, Execute
130\shared\Errordumps Read, Write
MSDTSServer100 130\dts\binn\MsDtsSrvr.ini.xml Read
130\dts\binn Read, Execute
130\shared Read, Execute
130\shared\Errordumps Read, Write
[!INCLUDEssNoVersion] Browser 130\shared\ASConfig Read
130\shared Read, Execute
130\shared\Errordumps Read, Write
SQLWriter N/A (Runs as local system)
User Instid\MSSQL\binn Read, Execute
Instid\Reporting Services\ReportServer Read, Execute, List Folder Contents
Instid\Reporting Services\ReportServer\global.asax Read
Instid\Reporting Services\RSWebApp Read, Execute, List Folder Contents
130\dts Read, Execute
130\tools Read, Execute
100\tools Read, Execute
90\tools Read, Execute
80\tools Read, Execute
130\sdk Read
Microsoft SQL Server\130\Setup Bootstrap Read, Execute
[!INCLUDEssNoVersion] Distributed Replay Controller <ToolsDir>\DReplayController\Log\ (empty directory) Read, Execute, List Folder Contents
<ToolsDir>\DReplayController\DReplayController.exe Read, Execute, List Folder Contents
<ToolsDir>\DReplayController\resources|Read, Execute, List Folder Contents
<ToolsDir>\DReplayController\{all dlls} Read, Execute, List Folder Contents
<ToolsDir>\DReplayController\DReplayController.config Read, Execute, List Folder Contents
<ToolsDir>\DReplayController\IRTemplate.tdf Read, Execute, List Folder Contents
<ToolsDir>\DReplayController\IRDefinition.xml Read, Execute, List Folder Contents
[!INCLUDEssNoVersion] Distributed Replay Client <ToolsDir>\DReplayClient\Log|Read, Execute, List Folder Contents
<ToolsDir>\DReplayClient\DReplayClient.exe Read, Execute, List Folder Contents
<ToolsDir>\DReplayClient\resources|Read, Execute, List Folder Contents
<ToolsDir>\DReplayClient\ (all dlls) Read, Execute, List Folder Contents
<ToolsDir>\DReplayClient\DReplayClient.config Read, Execute, List Folder Contents
<ToolsDir>\DReplayClient\IRTemplate.tdf Read, Execute, List Folder Contents
<ToolsDir>\DReplayClient\IRDefinition.xml Read, Execute, List Folder Contents
Launchpad %binn Read, Execute
ExtensiblilityData Full control
Log\ExtensibilityLog Full control

*The [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] service accounts. The following table lists additional ACLs that are set by [!INCLUDEssNoVersion] Setup.

Requesting component Account Resource Permissions
MSSQLServer Performance Log Users Instid\MSSQL\binn List folder contents
Performance Monitor Users Instid\MSSQL\binn List folder contents
Performance Log Users, Performance Monitor Users \WINNT\system32\sqlctr130.dll Read, Execute
Administrator only \\.\root\Microsoft\SqlServer\ServerEvents\<sql_instance_name>* Full control
Administrators, System \tools\binn\schemas\sqlserver\2004\07\showplan Full control
Users \tools\binn\schemas\sqlserver\2004\07\showplan Read, Execute
[!INCLUDEssRSnoversion] Report Server Windows Service Account <install>\Reporting Services\LogFiles 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 Services\ReportServer Read
Report Server Windows Service Account <install>\Reporting Services\ReportServer\global.asax Full
Report Server Windows Service Account <install>\Reporting Services\RSWebApp Read, Execute
Everyone <install>\Reporting Services\ReportServer\global.asax READ_CONTROL

FILE_READ_DATA

FILE_READ_EA

FILE_READ_ATTRIBUTES
ReportServer Windows Services Account <install>\Reporting Services\ReportServer\rsreportserver.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

*This is the WMI provider namespace.

File System Permissions Related to Unusual Disk Locations

The default drive for locations for installation is systemdrive, 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 is not the default drive, the per-service SID must have access to the file location. [!INCLUDEssNoVersion] Setup will provision 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. [!INCLUDEssNoVersion] Setup cannot 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 cannot 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>$.

Reviewing Additional Considerations

The following table shows the permissions that are required for [!INCLUDEssNoVersion] services to provide additional functionality.

Service/Application Functionality Required permission
[!INCLUDEssNoVersion] (MSSQLSERVER) Write to a mail slot using xp_sendmail. Network write permissions.
[!INCLUDEssNoVersion] (MSSQLSERVER) Run xp_cmdshell for a user other than a [!INCLUDEssNoVersion] administrator. Act as part of operating system and replace a process-level token.
[!INCLUDEssNoVersion] Agent (MSSQLSERVER) Use the autorestart 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 "Initializing [!INCLUDEssDE] Tuning Advisor on First Use" in [!INCLUDEssNoVersion] Books Online.

Important

Before you upgrade [!INCLUDEssNoVersion], enable SQL Server Agent and verify the required default configuration: that the [!INCLUDEssNoVersion] Agent service account is a member of the [!INCLUDEssNoVersion] sysadmin fixed server role.

Registry Permissions

The registry hive is created under HKLM\Software\Microsoft\Microsoft SQL Server\<Instance_ID> for instance-aware components. For example

  • HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL13.MyInstance
  • HKLM\Software\Microsoft\Microsoft SQL Server\MSASSQL13.MyInstance
  • HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.130

The registry also maintains a mapping of instance ID to instance name. Instance ID to instance name mapping is maintained as follows:

  • [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL] "InstanceName"="MSSQL13"
  • [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP] "InstanceName"="MSASSQL13"
  • [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\RS] "InstanceName"="MSRSSQL13"

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 SERVICE\winmgmt) 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 msdb database.

  • CREATE DDL EVENT NOTIFICATION permission in the server.

  • CREATE TRACE EVENT NOTIFICATION permission in the [!INCLUDEssDE].

  • VIEW ANY DATABASE server-level permission.

    [!INCLUDEssNoVersion] setup creates a SQL WMI namespace and grants read permission to the [!INCLUDEssNoVersion] Agent service-SID.

Named Pipes

In all installation, [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] components.

Database Engine Provisioning

The following accounts are added as logins in the [!INCLUDEssDEnoversion].

Windows Principals

During setup, [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Authentication is not 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 (somethimes also called service security principal (SID)) of the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] Agent service is provisioned as a [!INCLUDEssDE] login. The per-service SID login is a member of the sysadmin fixed server role.

[!INCLUDEssHADRc] 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 [!INCLUDEssNoVersion] 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

[!INCLUDEssNoVersion] Setup provisions the NT SERVICE\Winmgmt 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 are installing [!INCLUDEssGeminiShort], [!INCLUDEssNoVersion] 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, [!INCLUDEssNoVersion] Setup does not provide a default service account, such as a virtual account, for a [!INCLUDEssGeminiShort] installation. For more information about provisioning [!INCLUDEssGemini] 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).

Upgrading From Previous Versions

This section describes the changes made during upgrade from a previous version of [!INCLUDEssNoVersion].

  • [!INCLUDEssCurrent] requires [!INCLUDEnextref_longhorn] R2 SP1, Windows Server 2012, Windows 8.0, Windows Server 2012 R2, or Windows 8.1, . Any previous version of [!INCLUDEssNoVersion] running on a lower operating system version must have the operating system upgraded before upgrading [!INCLUDEssNoVersion].

  • During upgrade of [!INCLUDEssVersion2005] to [!INCLUDEssCurrent], [!INCLUDEssNoVersion] Setup will configure [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] instance (such as DATA), and the [!INCLUDEssNoVersion] 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 SID's are added to the local [!INCLUDEssNoVersion] Windows groups, unless [!INCLUDEssNoVersion] is a Failover Cluster Instance.
    • The [!INCLUDEssNoVersion] resources remain provisioned to the local [!INCLUDEssNoVersion] Windows groups.
    • The local Windows group for services is renamed from SQLServer2005MSSQLUser$<computer_name>$<instance_name> to SQLServerMSSQLUser$<computer_name>$<instance_name>. File locations for migrated databases will have Access Control Entries (ACE) for the local Windows groups. The file locations for new databases will have ACE's for the per-service SID.

  • During upgrade from [!INCLUDEssKatmai], [!INCLUDEssNoVersion] Setup will be preserve the ACE's for the [!INCLUDEssKatmai] per-service SID.

  • For a [!INCLUDEssNoVersion] Failover Cluster Instance, the ACE for the domain account configured for the service will be retained.

Appendix

This section contains additional information about [!INCLUDEssNoVersion] services.

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 is not 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 [!INCLUDEssNoVersion], 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.

Note

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

Note

The Local Service account is not supported for the [!INCLUDEssNoVersion] or [!INCLUDEssNoVersion] Agent services. Local Service is not 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 [!INCLUDEssNoVersion]. The actual name of the account is NT AUTHORITY\LOCAL 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 AUTHORITY\NETWORK 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 AUTHORITY\SYSTEM.

Identifying Instance-Aware and Instance-Unaware Services

Instance-aware services are associated with a specific instance of [!INCLUDEssNoVersion], and have their own registry hives. You can install multiple copies of instance-aware services by running [!INCLUDEssNoVersion] Setup for each component or service. Instance-unaware services are shared among all installed [!INCLUDEssNoVersion] instances. They are not associated with a specific instance, are installed only once, and cannot be installed side-by-side.

Instance-aware services in [!INCLUDEssNoVersion] include the following:

*Analysis Services in SharePoint integrated mode runs as '[!INCLUDEssGemini]' as a single, named instance. The instance name is fixed. You cannot specify a different name. You can install only one instance of Analysis Services running as '[!INCLUDEssGemini]' on each physical server.

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 AUTHORITY\LOCAL SERVICE NT AUTHORITY\NETWORK SERVICE NT AUTHORITY\SYSTEM BUILTIN\Administrators
German NT-AUTORITÄT\LOKALER DIENST NT-AUTORITÄT\NETZWERKDIENST NT-AUTORITÄT\SYSTEM VORDEFINIERT\Administratoren
French AUTORITE NT\SERVICE LOCAL AUTORITE NT\SERVICE RÉSEAU AUTORITE NT\SYSTEM BUILTIN\Administrators
Italian NT AUTHORITY\SERVIZIO LOCALE NT AUTHORITY\SERVIZIO DI RETE NT AUTHORITY\SYSTEM BUILTIN\Administrators
Spanish NT AUTHORITY\SERVICIO LOC NT AUTHORITY\SERVICIO DE RED NT AUTHORITY\SYSTEM BUILTIN\Administradores
Russian NT AUTHORITY\LOCAL SERVICE NT AUTHORITY\NETWORK SERVICE NT AUTHORITY\СИСТЕМА BUILTIN\Администраторы

Related Content

Security Considerations for a SQL Server Installation

File Locations for Default and Named Instances of SQL Server

Install Master Data Services