Share via


Configure Windows Service Accounts and Permissions

Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic 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.

Contents

This topic is divided into the following sections:

  • Services Installed by SQL Server

  • Service Properties and Configuration

    • Default Service Accounts

      • Changing Account Properties
    • New Account Types Available with Windows 7 and Windows Server 2008 R2

    • Automatic Startup

    • Configuring Services During Unattended Installation

    • Firewall Port

  • Service Permissions

    • Service Configuration and Access Control

    • Windows Privileges and Rights

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

    • File System Permission Granted to Other Windows User Accounts or Groups

    • File System Permissions Related to Unusual Disk Locations

    • Reviewing Additional Considerations

    • Registry Permissions

    • WMI

    • Named Pipes

  • Provisioning

    • 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

  • Upgrading From Previous Versions

  • Appendix

    • Description of Service Accounts

    • Identifying Instance-Aware and Instance-Unaware Services

    • Localized Service Names

Services Installed by SQL Server

Depending on the components that you decide to install, SQL Server Setup installs the following services:

  • SQL Server Database Services - The service for the SQL Server relational Database Engine. The executable file is <MSSQLPATH>\MSSQL\Binn\sqlservr.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 SQL Server Express. The executable file is <MSSQLPATH>\MSSQL\Binn\sqlagent.exe.

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

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

  • Integration Services - Provides management support for Integration Services package storage and execution. The executable path is <MSSQLPATH>\110\DTS\Binn\MsDtsSrvr.exe

  • 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 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 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 SQL Server Database Engine.

Top

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.

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 SID’s, 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

Windows Vista and Windows Server 2008

Windows 7 and Windows Server 2008 R2

Database Engine

NETWORK SERVICE

Virtual Account*

SQL Server Agent

NETWORK SERVICE

Virtual Account*

SSAS

NETWORK SERVICE

Virtual Account*

SSIS

NETWORK SERVICE

Virtual Account*

SSRS

NETWORK SERVICE

Virtual Account*

SQL Server Distributed Replay Controller

NETWORK SERVICE

Virtual Account*

SQL Server Distributed Replay Client

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

* When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary.

SQL Server Failover Cluster Instance

Component

Windows Server 2008

Windows Server 2008 R2

Database Engine

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.

SSAS

None. Provide a domain user account.

Provide a domain user account.

SSIS

NETWORK SERVICE

Virtual Account

SSRS

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

Top

Changing Account Properties

Important

  • Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.

  • For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for PowerPivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.

  • To change Reporting Services options, use the Reporting Services Configuration Tool.

Top

New Account Types Available with Windows 7 and Windows Server 2008 R2

Windows 7 and Windows Server 2008 R2 have two new types of service accounts called managed service accounts (MSA) and virtual accounts. Managed service accounts and virtual accounts are designed to provide crucial applications such as SQL Server with the isolation of their own accounts, while eliminating the need for an administrator to manually administer the Service Principal Name (SPN) and credentials for these accounts. These make long term management of service account users, passwords and SPNs much easier.

  • Managed Service Accounts

    A Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service. The password is managed automatically by the domain controller. You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service. An MSA has the ability to register Service Principal Name (SPN) with the Active Directory. 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 SQL Server setup can use it for SQL Server services.

    Note

    Group Managed Service Accounts are not currently supported.

  • Virtual Accounts

    Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup on Windows Server 2008 R2 or Windows 7, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. When specifying a virtual account to start SQL Server, leave the password blank. If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually. For more information on registering a SPN manually, see Register a Service Principal Name for Kerberos Connections.

    Note

    Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.

    The following table lists examples of virtual account names.

    Service

    Virtual Account Name

    Default instance of the Database Engine service

    NT SERVICE\MSSQLSERVER

    Named instance of a Database Engine service named PAYROLL

    NT SERVICE\MSSQL$PAYROLL

    SQL Server Agent service on the default instance of SQL Server

    NT SERVICE\SQLSERVERAGENT

    SQL Server Agent service on an instance of SQL Server 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:  Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.

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 SQL Server Browser service should be set to start automatically.

Configuring 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 installations1

MSSQLSERVER

SQLSVCACCOUNT, SQLSVCPASSWORD, SQLSVCSTARTUPTYPE

SQLServerAgent2

AGTSVCACCOUNT, AGTSVCPASSWORD, AGTSVCSTARTUPTYPE

MSSQLServerOLAPService

ASSVCACCOUNT, ASSVCPASSWORD, ASSVCSTARTUPTYPE

ReportServer

RSSVCACCOUNT, RSSVCPASSWORD, RSSVCSTARTUPTYPE

Integration Services

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

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

2The SQL Server Agent service is disabled on instances of SQL Server Express and SQL Server Express with Advanced Services.

Firewall Port

In most cases, when initially installed, the Database Engine can be connected to by tools such as SQL Server Management Studio installed on the same computer as SQL Server. SQL Server Setup does not open ports in the Windows firewall. Connections from other computers may not be possible until the Database Engine 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.

Top

Service Permissions

This section describes the permissions that SQL Server Setup configures for the per-service SID’s 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 2012 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 the Database Engine 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 SQL Server service can restrict access to its resources.

Note

On Windows 7 and Windows Server 2008 R2 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 SSAS, a per-service SID for the Analysis Services 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 Analysis Services 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 will still be available without any updating, because the per-service SID has not changed. This method allows the Analysis Services service to be renamed during upgrades.

During SQL Server installation, SQL Server Setup creates a local Windows groups for SSAS 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 7.

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

SQL Server Database Engine:

(All rights are granted to the per-service SID. Default instance: NT SERVICE\MSSQLSERVER. Named instance: NT SERVICE\MSSQL$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

SQL Server Agent:1

(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)

SSAS:

(All rights are granted to a local Windows group. Default instance: SQLServerMSASUser$ComputerName$MSSQLSERVER. Named instance: SQLServerMSASUser$ComputerName$InstanceName. PowerPivot for SharePoint 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 (SeIncreaseQuotaSizePrivilege)

Lock pages in memory (SeLockMemoryPrivilege) – this is needed only when paging is turned off entirely.

For failover cluster installations only:

Increase scheduling priority (SeIncreaseBasePriorityPrivilege)

SSRS:

(All rights are granted to the per-service SID. Default instance: NT SERVICE\ReportServer. Named instance: NT SERVICE\$InstanceName.)

Log on as a service (SeServiceLogonRight)

SSIS:

(All rights are granted to the per-service SID. Default instance and named instance: NT SERVICE\MsDtsServer110. Integration Services 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)

SQL Server Browser:

(All rights are granted to a local Windows group. Default or named instance: SQLServer2005SQLBrowserUser$ComputerName. SQL Server Browser does not have a separate process for a 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: NT Service\SQLWriter. SQL Server 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. SQL Server setup does not 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)

1The SQL Server Agent service is disabled on instances of SQL Server Express.

Top

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

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

 

110\shared

Read, Execute

 

Instid\MSSQL\Template Data (SQL Server Express only)

Read

SQLServerAgent1

Instid\MSSQL\binn

Full control

 

Instid\MSSQL\binn

Full control

 

Instid\MSSQL\Log

Read, Write, Delete, Execute

 

110\com

Read, Execute

 

110\shared

Read, Execute

 

110\shared\Errordumps

Read, Write

ServerName\EventLog

Full control

FTS

Instid\MSSQL\FTData

Full control

 

Instid\MSSQL\FTRef

Read, Execute

 

110\shared

Read, Execute

 

110\shared\Errordumps

Read, Write

 

Instid\MSSQL\Install

Read, Execute

Instid\MSSQL\jobs

Read, Write

MSSQLServerOLAPservice

110\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

 

110\shared\Errordumps

Read, Write

SQLServerReportServerUser

Instid\Reporting Services\Log Files

Read, Write, Delete

 

Instid\Reporting Services\ReportServer

Read, Execute

 

Instid\Reportingservices\Reportserver\global.asax

Full control

 

Instid\Reportingservices\Reportserver\Reportserver.config

Read

 

Instid\Reporting Services\reportManager

Read, Execute

 

Instid\Reporting Services\RSTempfiles

Read, Write, Execute, Delete

 

110\shared

Read, Execute

 

110\shared\Errordumps

Read, Write

MSDTSServer100

110\dts\binn\MsDtsSrvr.ini.xml

Read

 

110\dts\binn

Read, Execute

 

110\shared

Read, Execute

 

110\shared\Errordumps

Read, Write

SQL Server Browser

110\shared\ASConfig

Read

 

110\shared

Read, Execute

 

110\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\Reportingservices\Reportserver\global.asax

Read

 

Instid\Reporting Services\ReportManager

Read, Execute

 

Instid\Reporting Services\ReportManager\pages

Read

 

Instid\Reporting Services\ReportManager\Styles

Read

 

110\dts

Read, Execute

 

110\tools

Read, Execute

100\tools

Read, Execute

 

90\tools

Read, Execute

 

80\tools

Read, Execute

 

110\sdk

Read

 

Microsoft SQL Server\110\Setup Bootstrap

Read, Execute

SQL Server 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

SQL Server 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

1The SQL Server Agent service is disabled on instances of SQL Server Express and SQL Server Express 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.

Top

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

Instid\MSSQL\binn

List folder contents

 

Performance Monitor Users

Instid\MSSQL\binn

List folder contents

 

Performance Log Users, Performance Monitor Users

\WINNT\system32\sqlctr110.dll

Read, Execute

 

Administrator only

\\.\root\Microsoft\SqlServer\ServerEvents\<sql_instance_name>1

Full control

 

Administrators, System

\tools\binn\schemas\sqlserver\2004\07\showplan

Full control

 

Users

\tools\binn\schemas\sqlserver\2004\07\showplan

Read, Execute

Reporting Services

<Report Server Web 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 Manager Application pool identity, ASP.NET account, Everyone

<install>\Reporting Services\ReportManager, <install>\Reporting Services\ReportManager\Pages\*.*, <install>\Reporting Services\ReportManager\Styles\*.*, <install>\Reporting Services\ReportManager\webctrl_client\1_0\*.*

Read

 

Report Manager Application pool identity

<install>\Reporting Services\ReportManager\Pages\*.*

Read

 

<Report Server Web Service Account>

<install>\Reporting Services\ReportServer

Read

 

<Report Server Web Service Account>

<install>\Reporting Services\ReportServer\global.asax

Full

 

Everyone

<install>\Reporting Services\ReportServer\global.asax

READ_CONTROL

FILE_READ_DATA

FILE_READ_EA

FILE_READ_ATTRIBUTES

 

Network service

<install>\Reporting Services\ReportServer\ReportService.asmx

Full

 

Everyone

<install>\Reporting Services\ReportServer\ReportService.asmx

READ_CONTROL

SYNCHRONIZE FILE_GENERIC_READ

FILE_GENERIC_EXECUTE

FILE_READ_DATA

FILE_READ_EA

FILE_EXECUTE

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

1This is the WMI provider namespace.

Top

The default drive for locations for installation is systemdrive, normally drive C. When tempdb or user databases are installed

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. SQL Server 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. SQL Server 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 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 autorestart feature.

Must be a member of the Administrators local group.

Database Engine 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 Database Engine Tuning Advisor to tune only those tables that they own. For more information, see "Initializing Database Engine Tuning Advisor on First Use" in SQL Server Books Online.

Important

Before you upgrade SQL Server, enable Windows Authentication for SQL Server Agent and verify the required default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.

Top

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\MSSQL11.MyInstance

  • HKLM\Software\Microsoft\Microsoft SQL Server\MSASSQL11.MyInstance

  • HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.110

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"="MSSQL11"

  • [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP] "InstanceName"="MSASSQL11"

  • [HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\RS] "InstanceName"="MSRSSQL11"

WMI

Windows Management Instrumentation (WMI) must be able to connect to the Database Engine. To support this, the per-service SID of the Windows WMI provider (NT SERVICE\winmgmt) is provisioned in the Database Engine.

The SQL WMI provider requires the following 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 Database Engine.

  • 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.

Top

Named Pipes

In all installation, SQL Server Setup provides access to the SQL Server Database Engine through the shared memory protocol, which is a local named pipe.

Top

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 SQL Server Database Engine.

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 Database Engine login and is a member of the sysadmin fixed server role. When the Database Engine is installed using only Windows Authentication (that is when SQL Server Authentication is not enabled), the sa login is still present but is disabled. For information about enabling the sa account, see Change Server Authentication Mode.

SQL Server Per-service SID Login and Privileges

The per-service SID of the SQL Server service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.

SQL Server Agent Login and Privileges

The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.

AlwaysOn Availability Groups and SQL Failover Cluster Instance and Privileges

When installing the Database Engine as a AlwaysOn Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) 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 Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.

SQL WMI and Privileges

SQL Server Setup provisions the NT SERVICE\Winmgmt account as a Database Engine 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.

Top

SSAS Provisioning

SSAS service account requirements vary depending on how you deploy the server. If you are installing PowerPivot for SharePoint, SQL Server Setup requires that you configure the Analysis Services 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 does not provide a default service account, such as a virtual account, for a PowerPivot for SharePoint installation. For more information about provisioning PowerPivot for SharePoint, see Configure PowerPivot Service Accounts.

For all other standalone SSAS 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 SSAS failover clusters.

All SSAS installations require that you specify a system administrator of the Analysis Services instance. Administrator privileges are provisioned in the Analysis Services Server role.

SSRS Provisioning

The account specified during setup is provisioned in the Database Engine as a member of the RSExecRole database role. For more information, see Configure the Report Server Service Account.

Top

Upgrading From Previous Versions

This section describes the changes made during upgrade from a previous version of SQL Server.

  • SQL Server 2012 requires Windows Vista, Windows 7, Windows Server 2008, or Windows Server 2008 R2. Any previous version of SQL Server running on Windows XP or Windows Server 2003 must have the operating system upgraded before upgrading SQL Server.

  • During upgrade of SQL Server 2005 to SQL Server 2012, SQL Server Setup will configure SQL Server in the following way.

    • The Database Engine 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 Database Engine is provisioned in the Database Engine as a member of the sysadmin fixed server role.

    • The per-service SID’s 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> 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 SQL Server 2008, SQL Server Setup will be preserve the ACE’s for the SQL Server 2008 per-service SID.

  • For a SQL Server Failover Cluster Instance, the ACE for the domain account configured for the service will be retained.

Top

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 SQL Server Database Engine.

Accounts Available With Any Operating System

In addition to the new MSA 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.

Note

If you configure the application to use a domain account, you can isolate the privileges for the application, 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. Be aware that the Local Service account is not supported for the SQL Server or SQL Server 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 SQL Server. 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 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 are not associated with a specific instance, are installed only once, and cannot 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 SQL Server Express and SQL Server Express with Advanced Services.

  • Analysis Services 1

  • Reporting Services

  • Full-text search

Instance-unaware services in SQL Server include the following:

  • Integration Services

  • SQL Server Browser

  • SQL Writer

1Analysis Services in SharePoint integrated mode runs as 'PowerPivot' 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 'PowerPivot' on each physical server.

Top

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\SYSTEM

BUILTIN\Администраторы

Top

Security Considerations for a SQL Server Installation

File Locations for Default and Named Instances of SQL Server

Install Master Data Services