Setting up Windows Services Accounts

SQL Server Setup Help

SQL Server Setup Help

Setting up Windows Services Accounts

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.

Service
Name
Term for default instance Term for named instance
Microsoft SQL Server SQL Server MSSQLSERVER MSSQ$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.

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.

  • Replication.

  • 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:
    • HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
      -or- for any named instance: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server.

    • HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer.
      -or- for any named instance: HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename.

      HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib.

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.

Service Permission 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.

SQL Server Member of local Power Users or local Administrators group Add and delete SQL Server objects in the Windows 2000 Active Directory.

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.

See Also

Creating Security Accounts

Planning Security

Services Accounts