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
physical computer.
-
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
account.
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).