When selecting service accounts and their configuration, you should keep in mind the following guidelines:
- Set each account's password to never expire (using Local Users and Groups or Active Directory Users and Computers MMC snap-in, depending on the type of account). Note that if you intend to change it, you will also need to type in the new password in the SQL Enterprise Manager or Services MMC snap-in (Services MMC snap-in can be used if SQL Server is not running).
- Always use SQL Server Enterprise Manager to designate a new account for SQL Server or SQL Server Agent services (from the Security tab of the SQL Server Properties dialog box and from the General tab of the SQL Server Agent Properties dialog box). This way, the account will automatically be granted proper user rights, permissions on the relevant folders (hosting SQL Server 2000 installation directory and databases), and registry entries. If for some reason this is not possible (e.g. in case of MSDE), you will need to perform the following steps manually in order to obtain the same results (the detailed description of this process is published in the Microsoft Knowledge Base article Q283811:
- Select an existing (or create a new) user account which will be used by the SQL Server Service and/or SQL Server Agent Service.
- Assign it to the SQL Server and/or SQL Server Agent services using Services MMC snap-in (part of the Administrative Tools menu) - but do not start these services yet.
- Grant the service account appropriate user rights - with Active Directory based or local group policy, depending on account type. After you launch the Group Policy Editor, containing either local or Active Directory container-specific settings, in the Computer Configuration portion of the policy, drill down to Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment folder. After double-clicking on the relevant (outlined in the KB article Q283811) user rights from the list displayed in the details pane, add the service account name to the group of privileged accounts.
- Grant the service account permissions to registry keys outlined in the KB article Q283811- with the help of the Registry Editor.
- Grant the service account permissions to folders and their content outlined in the KB article Q283811 (assuming that you followed Microsoft recommendations and installed SQL Server on an NTFS-formatted partition),
- You might need to add the service account to the SQL Server 2000 fixed server sysadmin role. You can handle this either with SQL Enterprise Manager (e.g. by launching Create Login Wizard) or with Query Analyzer (by executing sp_grantlogin and sp_addsrvrolemember stored procedures, as demonstrated in the KB article Q283811).
- Last but not least - avoid adding the account to local privileged groups (such as Administrators or Power Users), unless absolutely necessary. This membership is not required unless you are planning on performing the following actions:
- publishing the server or any of its objects (e.g. databases or replication articles) in Active Directory. For more information on this functionality, refer to one of our earlier articles. In this case, you need to ensure that the SQL Server Service account is a member of the local Administrators or Power Users group (in order to be able to start SQL Server Active Directory Helper Service, which takes care of the registration process).
- executing xp_cmdshell extended stored procedure or ActiveX scripting and CmdExec jobs owned by users who are not part of SysAdmin fixed server roles (we will discuss server and database roles in our next article). In this case, you need to ensure that SQL Server Agent Service account has "Act as Part of the Operating System" and "Replace a Process Level Token" privileges, in order for jobs to execute in the security context of their owner's account. It also needs to be a member of the local Administrators group, in order to be able to retrieve SQL Agent proxy account (defiined on the Job System tab of the SQL Server Agent Properties dialog box in SQL Server Enterprise Manager) credentials stored locally in the form of LSA secrets (Windows-specific secure mechanism for storing credentials, which makes them accessible only to members of the local Administrators group).
- using the AutoRestart feature of SQL Server Agent account (available from the Advanced tab of the SQL Server Agent Properties dialog box in the SQL Server Enterprise Manager). In this case, the SQL Server Agent service account needs to be a member of the local Administrators group.
- applying "Start whenever the CPU(s) become idle" setting when scheduling SQL Server Agent jobs (available from the job schedule Properties dialog box). In this case, the SQL Server Agent service account needs to be a member of the local Administrators group.
- using replication with the default snapshot folder and remote Distribution and Merge Agents. By default, the snapshot folder is set to C:\Program Files\Microsoft SQL Server\MSSQL\Repldata on the Distributor computer and is accessed via C$ administrative share (drive letter might change, depending on the installation directory of SQL Server 2000 instance). In order for Distribution and Merge Agents, which operate in the security context of SQL Server Agent account, to access this share, they need to be members of the local Administrators group on the Distributor.
- implementing multiserver administration, which provides the ability to manage SQL Server Agent jobs across environments consisting of multiple SQL Servers from a single computer, known as the master server (MSX). A master server functions as the source of jobs, which are copied to all target (TSX) servers and executed. The same master server functions also as a repository for jobs status for all of its target servers. The procedure of authenticating the connection between target servers and their master changed with SQL Server 2000 SP3.
Prior to the release of SQL Server 2000 SP3, a pre-defined TSX SQL login was auto generated and SQL Authentication enforced during MultiServer Setup Wizard (which is the primary method to configure the MSX environment). In this case, the SQL Server Agent Service account needs to be a member of the local Administrators group. This requirement results from the fact that when SQL Authentication is used, the name and password of TSX login are stored locally on the master server in the form of an LSA secret (just as previously mentioned SQL Agent proxy account) and can be retrieved only by members of the local Administrators group. Since SQL Server Agent Service is responsible for managing MSX operations, it has to be a member of the local Administrators group in order to retrieve TSX login authentication information.
Starting with SQL Server 2000 SP3, SQL Server Authentication is no longer enforced when running MultiServer Setup Wizard and the TSX account is no longer auto-generated. Instead, you have an option of selecting either SQL or Windows Authentication. In the case of Windows Authentication, SQL Server Agent Service account is used to communicate between Master and Target servers. An additional benefit (besides increased security resulting from eliminating inherently less-secure SQL Authentication) is the fact that the SQL Server Agent Service account no longer needs to be a member of the local Administrators group on the master server.
As far as the three remaining services we listed at the beginning of this article, you should enable them only if you are relying on their functionality. SQL Server Active Directory Helper (MSSQLServerADHelper) and Full-Text Search Services operate in the security context of the Local System account. Distributed Transaction Coordinator Service can be configured to use the Network Service account, which gives it sufficient network access and, at the same time, limits substantially its local privileges (and potential exposure to new vulnerabilities).
In our next article, we will discuss the process of authorization, which follows successful authentication and determines the level of access granted to SQL Server and its objects.