Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Services for SQL Server 2000

By Brian Kelley, (first published: 2002/01/03)

Services for SQL Server 2000

Introduction

In the last couple of months I've been working primarily with NT administrators and Visual Basic developers with regards to interfacing with SQL Server 2000.  One of the things I've discovered on the NT side of the house is a fundamental lack of understanding of how SQL Server 2000 runs in the OS.  This was a topic I discovered with dismay when interviewing DBA candidates for my current company.  Only two of a whole slew of potential employees understood SQL Server and the services it uses, which made me pause.  Because of the way SQL Server is installed and administered, I wasn't terribly surprised by the DBA candidates.  I did, however, impress upon them it was essential to understand how SQL Server ran, because that could ultimately answer certain problems encountered in a typical production environment.  As for the developers, I'll forgive them, because it isn't really their job to know.  The NT administrators on the other hand... but that's another story.

So this article will concentrate on the basic services under which SQL Server 2000 runs.  By understanding how SQL Server operates and the permissions thereof, we can quickly narrow down on issues that will occur in a production environment.  Also, we can help our NT administrators understand what they shouldn't stop and/or disable when SQL Server needs to be kept running.  Ever have a system administrator shut down the SQL Server Agent service running backups by mistake?  Not a pretty sight!

We'll look at several things in this article:

Basic Services

Looking at Server Manager (NT 4), we might see the following:

or looking at Services under Windows 2000:

We're interested in the following services:

Under a normal SQL Server 2000 installation on a Windows 2000 Server or Advanced Server, we'd see all but MSSQLServerOLAPService. MSSQLServerOLAPService is not seen unless SQL Server 2000 Analysis Services is installed.  Now that we're familiar with the names of the services, let's look at each in turn.

Distributed Transaction Coordinator

Microsoft Distributed Transaction Coordinator (MS DTC) is not a part of SQL Server, per se.  Under Windows 2000, it is administered under Component Services.  MS DTC serves to coordinate distributed transactions across several servers.  Under SQL Server 2000, we would begin a distributed transaction (provided Distributed Transaction Coordinator is installed and running) with:

BEGIN DISTRIBUTED TRAN

or

BEGIN DISTRIBUTED TRANSACTION

It is important to note that distributed transaction resources are not confined to SQL Server or even to RDBMS services such as Oracle or DB2.  Message queues are one non-RDBMS resource that can be accessed by MS DTC.  For the most part we don't delve into MS DTC as DBAs except with respect to failover clustering.  Failover clustering depends on MS DTC running in clustered mode.  The Cluster Wizard (comclust.exe) can be used to configure MS DTC to run in clustered mode.

Microsoft Search

Microsoft Search is the service which supports Full Text Indexing.  If Full Text Indexing wasn't part of the install (it is by default on SQL Server 2000 Standard and Enterprise installations), the Microsoft Search service is likely not present in the Services list.  The advantage we get with Full Text Indexing is that indexes are stored on the file system and retrievals for text searches are often faster through Microsoft Search than through SQL Server if we're having to deal with wildcards.  Also, full text searches allow us to do searches against forms of words which isn't possible with a standard T-SQL query using SQL Server's native engine.  However, the draw back is that full text indexes aren't constantly being updated.  Jobs which update full text indexes will need to be built with time schedules reflecting how often data is being updated. 

Finally, Microsoft Search is not available for SQL Server installations on the Microsoft Windows 9x platforms.  It has to run as an NT service and therefore a SQL Server running on a non-NT platform cannot install Full Text Search.  However, Windows 9x clients can use Full Text searches if an NT-based SQL Server has it installed.

MSSQLServer

This is the base service for a default instance of SQL Server.  We'll talk about named instances in a bit, but with respect to default instances, the SQLServerAgent service is dependent upon this service running.  Also, the account under which the MSSQLServer service is where SQL Server will look for a MAPI profile for use with SQLMail. More on this later as well.

MSSQLServerADHelper

Generally, the MSSQLServer runs as a domain user account in networked installations.  Though it it is typical for this domain user account to be given local Administrator rights on the server where the service is running, this isn't enough to register SQL Server with Active Directory.  As a result, the MSSQLServerADHelper service has been added to the mix for SQL Server 2000. Both the MSSQLServer and MSSQLServerOLAPService will use it in order to register SQL Server engines and Analysis Services with Active Directory. 

In order to register objects with Active Directory, the account doing the registering either needs to be in the Domain Admins group or be the localsystem Windows account for a particular server.  Since we typically run MSSQLServer as a domain user (which does not have Domain Admin rights), the MSSQLServer service would be unable to register itself with Active Directory.  The work around by Microsoft is MSSQLServerADHelper.  Whenever MSSQLServer or MSSQLServerOLAPService needs to register an instance, the MSSQLServerADHelper service will be started and utilized.  After registration is done, it will be stopped.  So it is normal to see this service set to Manual and not to be running if we were to look at the list of Services at any given time. On a side note, regardless of how many instances of SQL Server that may be running on a particular server, only one MSSQLServerADHelper service is required because all instances will use it.

MSSQLServerOLAPService

Unless Analysis Services is installed on a particular server, this service will not be present.  It is installed separately of any normal MS SQL Server installs and as a result is not a common sight on most SQL Server installations.  MSSQLServer is not required for Analysis Services, as it by default uses a Microsoft Access (.mdb) database for its Repository, though the Repository can be migrated to SQL Server.  With respect to permissions for the service, it is important that the MSSQLServerOLAPService has sufficient permissions to access any and all data sources with which to build and process objects from.  It is the service's user account, not that of an Analysis Services administrator, that is used to access the data source.

 SQLSERVERAGENT

As can be deduced by the name, this is the service for the SQL Server Agent.  If one selects all of the default choices on a SQL Server 2000 installation, the SQL Server Agent will be setup with the exact same user account as the MSSQLServer service.  However this is not a requirement, though it is usual.  As SQL Mail looks for a MAPI profile under the user account the MSSQLServer service is running under, SQLAgent Mail looks for a MAPI profile under the user account the SQLSERVERAGENT runs under.  In addition, any jobs which require access to network resources will run under the context of the user account specified for SQLSERVERAGENT.  This is a significant point when trying to diagnose why a xp_cmdshell command or DTS package runs when manually executed and fails when run through a job. If this is the case, the usual culprit is the user account which SQLSERVERAGENT runs under does not have sufficient permissions to access the resources required.

Multiple Instances

Starting with SQL Server 2000, multiple instances of the SQL Server engine can be installed on a system.  This also allows one to have SQL Server 7.0 running as the "default" instance with a SQL Server 2000 engine running as a named instance.  Or multiple SQL Server 2000 engines can be installed, which isn't all that unusual if one is looking to make the most of a database cluster.  An Active/Passive means one server of a two server cluster is essentially remaining unused.  Install a named instance and one can go to an Active/Active configuration with a database engine running on each physical server, thereby maximizing the use of hardware, and providing the fail-over necessary to maintain high-availability.

With named instances, additional services for both SQL Server and SQL Server Agent are required per instance.  How Microsoft handles this is to combine MSSQL$ and SQLAgent$ with the name of the instance.  So if I were to have both a default instance and a named instance called Server2, I'd see services with the following names:

Service Instance  Service Name
SQL Server Default MSSQLServer
SQL Server Server2 MSSQL$Server2
SQL Server Agent Default SQLSERVERAGENT
SQL Server Agent Server2 SQLAgent$Server2

The other services related to a SQL Server engine install, MS DTC, Microsoft Search, and the AD Helper service, stay at only one service apiece.  The multiple instances of the SQL Server engine will use these singular services as needed.

Permissions

I've already spoken briefly when considering each service in turn about permissions and it's not my intent to go into a lot of detail here.  Here are the basics:

  • Each service can run as the localsystem account, a domain account, or a local user account (account created explicitly on the server)
  • Choice of permissions determines the extent of what each service can do
  • From a security perspective, give a service account only the permissions it needs and no more (e.g., no Domain Admin accounts under normal circumstances)

Here are some specifics:

  • SQL Mail requires a MAPI profile.  As a result, if SQL Mail is to be used, a domain account is necessary.
  • SQLAgent Mail also requires a MAPI profile.  It too requires a domain account in order to be used.
  • If network resources are required such as replication, the MSSQLServer service requires a domain user account with sufficient privileges
  • If CmdExec and ActiveScripting is required for jobs not owned by sysadmins, a domain user or local user account is required for SQLSERVERAGENT
  • If network resources are required for jobs, SQLSERVERAGENT requires a domain user account with sufficient privileges
  • If autorestart features are to be used, SQLSERVERAGENT must be running as a domain or local user account
  • On-idle jobs require a domain or local user account for SQLSERVERAGENT
  • MSSQLServerADHelper either needs to be the localsystem account or a domain account with Domain Admin privileges (the former is best)
  • MSSQLServerOLAPService requires an account with sufficient privileges to access required data sources

Books Online (BOL) goes into a great amount of detail for the MSSQLServer and SQLSERVERAGENT accounts, so I won't try to reproduce all the information here.  Look in BOL for the absolute minimum permissions for these services and also to see the pros and cons of using various accounts with varying levels of privileges.

 

Total article views: 49944 | Views in the last 30 days: 30
 
Related Articles
FORUM

IIS requirement for sql server installation

IIS requirement for sql server installation

FORUM

SQL Service Account

SQL Service Account

FORUM

service account

Service account

ARTICLE

Configuring Service Account Privileges for SQL Server

How to grant Windows privileges to the SQL Server's service account.

ARTICLE

Picking Service Accounts

It is something you do rarely, but with SQL Server 2005 and ten services, there are more decisions t...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones