SQL Server 2000 Security - Part 3 (Service Accounts)

Tuesday May 11th 2004 by Marcin Policht
Share:

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

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.

    » See All Articles by Columnist Marcin Policht

  • Share:
    Home
    Mobile Site | Full Site
    Copyright 2017 © QuinStreet Inc. All Rights Reserved