SQL Server 2000 Security - Part 3 (Service Accounts)

May 11, 2004

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).