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.
» 
Comment and Contribute
