Setting up Windows Services Accounts
Topic last updated -- July 2003
On the Microsoft® Windows NT® and Microsoft Windows® 2000 operating systems, Microsoft SQL Server™ and SQL Server Agent are started and run as Windows services. These services appear in the list of installed services in the Services dialog box, available using Windows Control Panel. The table shows each service name and the term used to refer to the default and named instances of SQL Server, as displayed in the Services dialog box.
|Term for default instance||Term for named instance|
|Microsoft SQL Server||SQL Server||MSSQLSERVER||MSSQL$InstanceName|
|Microsoft SQL Server Agent||SQL Server Agent||SQLSERVERAGENT||SQLAgent$InstanceName|
For Microsoft SQL Server™ and SQL Server Agent to run as services in Windows, they must be assigned a Windows user account. Typically, both SQL Server and SQL Server Agent are assigned the same user account, either the local system or domain user account. However, you can customize the settings for each service during the installation process. For more information about how to customize account information for each service, see Services Accounts.
The Microsoft Search service (full-text search) must always run under the local system account.
Note Microsoft Windows 98 does not support Windows services; instead, SQL Server simulates the SQL Server and SQL Server Agent services. It is not required that you create user accounts for these simulated services.
Using the Local System Account
The local system account does not require a password, does not have network access rights in Windows NT 4.0 and Windows 2000, and restricts your SQL Server installation from interacting with other servers.
Using a Domain User Account
A domain user account uses Windows Authentication, that is, the same user name and password used to connect to the operating system is also used to connect to SQL Server. A domain user account is typically used because many server-to-server activities can be performed only with a domain user account, for example:
- Remote procedure calls.
- Backing up to network drives.
- Heterogeneous joins that involve remote data sources.
- SQL Server Agent mail features and SQL Mail. This restriction applies if using Microsoft Exchange. Most other mail systems also require clients (the SQL Server and SQL Server Agent services) to be run on accounts with network access.
Note Several servers running SQL Server can share the same user account. When setting up replication, it is recommended that a Publisher and all its Subscribers share the same service account for the SQL Server service.
Requirements for Domain User Account
All domain user accounts must have permission to:
- Access and change the SQL Server directory (\Program Files\Microsoft SQL Server\Mssql).
- Access and change the .mdf, .ndf, and .ldf database files.
- Log on as a service.
- Read and write registry keys at and under:
-or- for any named instance: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server.
-or- for any named instance: HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename.
In addition, a domain user account must be able to read and write corresponding registry keys for these services: SQLAgent$InstanceName, MSSearch, and MSDTC.
This table shows additional permissions required for certain functionality.
|SQL Server||Network write privileges||Write to a mail slot using xp_sendmail.|
|SQL Server||Act as part of operating system and replace a process level token||Run xp_cmdshell for a user other than a SQL Server administrator.|
|SQL Server Agent||Member of the Administrators local group||Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator.
Use the autorestart feature.
Use run-when-idle jobs.
Connect to SQL Server using SQL Server Authentication.
|SQL Server||Member of local Power Users or local Administrators group||Add and delete SQL Server objects in the Windows 2000 Active Directory.|
Note If the startup account assigned to the MSSQLServer Service is not a member of the Local Administrators group, or if the BUILTIN\Administrators SQL Server login has been removed, you must add the startup account for the MSSQLServer service or the SQLServerAgent service, or both, to the SQL Server system administrators (sysadmin) role. Grant the [Domain\NTaccount] user a logon to SQL Server.
Changing User Accounts
To change the password or other properties of any SQL Server–related service after installing SQL Server, use SQL Server Enterprise Manager. If your Windows password expires and you change it, be sure to also revise the SQL Server services settings in Windows. For more information, see Changing Passwords and User Accounts.