Previously in this series, we looked into authentication mechanisms used to determine validity of credentials specified by a user connecting to a SQL Server 2000. Part 3 explores another topic related to authentication, but on a different level - SQL Server Services accounts - which determine security context in which SQL Server processes operate.
In the previous
article of this series, we looked into authentication mechanisms used to
determine validity of credentials specified by a user connecting to a SQL
Server 2000. Now, we are going to explore another topic related to
authentication, but on a different level - SQL Server Services accounts - which
determine security context in which SQL Server processes operate. In
particular, we will review available configuration options and security
implications of each for the following services:
SQL Server Engine Service (MSSQLServer) - providing core
features, necessary for the SQL Server to operate in a normal fashion. In the
case of a multi-instance configuration, each instance uses a separate service,
named MSSQL$InstanceName, where InstanceName is the one assigned
during its installation.
SQL Server Agent Service (SQLServerAgent) - responsible for
auxiliary functionality such as monitoring, firing alerts, job management
(including multiserver environments), replication, SQLAgentMail, or execution
of xp_cmdshell extended stored procedure, ActiveX scripts, and CmdExec-based
jobs owned by non-sysadmin accounts. As with SQL Server Engine Service, in a
multi-instance configuration, each instance would have its own service named SQLAgent$InstanceName,
where InstanceName is the one assigned during its installation.
SQL Server Active Directory Helper (MSSQLServerADHelper) - assisting
with registering SQL Server and its objects (and Analysis server) in Active
Directory integration. This service serves all instances sharing the same
Full-Text Search Service (MSSearch) - facilitating full-text
searches, including creation and management of index catalogs. As with SQL
Server Active Directory Helper, there is only one such service per physical
computer, regardless of the number of SQL Server instances.
Microsoft Distributed Transaction Coordinator (MSTDC) - responsible
for managing distributed transactions - also shared among all instances on a
single physical computer.
Each of these services (just like any other Windows service) is associated
with a Windows account, in which security context it operates. Capabilities of
a service are determined by rights and permissions granted to this account.
From a security perspective, you want to limit them only to those that are absolutely
necessary, since compromising SQL Server might allow a hacker to take advantage
of privileges assigned to the underlying service account.
SQL Server Engine Service and SQL Server Agent Service accounts are first
configured during the SQL Server 2000 setup (you can easily alter the original
configuration after the installation completes). At that time, you are prompted
to choose whether services will start automatically and which account will be
used for each (you can assign different account to each service). Regarding the
latter, you have two options - the Local System account or a Windows user
In general, it is not recommended to use the Local System account, as this
configuration, if compromised, grants unlimited access to all Operating System
resources. In addition, since the Local System account is recognized only on
the same computer where SQL Server 2000 is installed, any attempt to connect to
remote systems initiated by SQL services will fail. This, effectively, prevents
use of any SQL distributed features such as MS Exchange integration (via SQL
Mail or SQL Agent Mail) or replication.
A Windows user account can be either a Windows domain account (from a legacy
Windows NT 4.0 or Active Directory 2000/2003 domain) or a local SAM database
account (of the local Windows installation where SQL Server 2000 resides). Note
that using local Windows accounts also (just as with Local System accounts)
affects the ability to access remote resources over the network. In effect,
domain user accounts are used most commonly (in addition, management of local
accounts in larger environments tends to be cumbersome).
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:
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
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
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).
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
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
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.
See All Articles by Columnist Marcin Policht