Understanding SQL Server Services, Part II

Introduction:

In Part I, we looked briefly at the two main SQL Server services: MSSQLServer and SQLServerAgent. We also examined the user contexts each service could run in and the differences that occur when we’re using named instances with SQL Server 2000. In this article we’re going to consider various factors that’ll help us decide which user context to use for each service, as well as managing these services from tools other than SQL Server Enterprise Manager and Service Manager.

Deciding User Context:

Because of the ease with which SQL Server can be installed, the user contexts for the SQL Server services can be and often are overlooked. However, we need to do a careful analysis of how we intend to use each instance of SQL Server. Only then can we select the proper user context for each one. We’ll look at several factors:

• Number of servers to manage
• The use of SQLMail and SQLAgentMail
• Running automated jobs
• Using Replication

Number of Servers:

The number of servers we are trying to manage within our organization has a small part to play in our choice of user context but really hits home in the number of different accounts we use. Consider if we’ve got more than one server. Let’s take 20 servers for our purposes.

We’ve just had a DBA leave the company and this DBA knew all of the service accounts and passwords for all 20 servers (she was a DBA after all). Now let’s go a step further. Let’s say we were running each server under a different user account (domain or local). That means we’ve got 20 different user accounts to modify. We’ve then got to go to twenty servers and make changes there, as well. So we’re talking about 40 or 60 changes to make (the password, SQL Server, and SQL Server agent if applicable), and if we foul up on any of them, we bring a server down temporarily. This is an administrative nightmare. Even if we had an automated tool for changing service accounts, we still have to make 20 different changes.

Now let’s say we’re using a single service account for all 20 servers. That’s only one password to be changed plus the 20 or 40 touches we need to do on the servers. We’re saving 19 changes. The numbers still aren’t great, but they’re better than what we were looking at in the other case. And if we’ve got an automated tool, we’re talking a single (just one) change. We can’t beat that.

Now, all of these numbers have considered that for a given server, MSSQLServer and SQLServerAgent are running under the same user account. If we’re using different accounts, the numbers continue to grow.

Of course, if every SQL Server was running under the System account, we don’t have any changes to worry about with respect to our servers. However, other factors may preclude this option (such as needing to access network resources). In reality, the fewer number of different accounts to manage, the easier our administrative functions become.

SQLMail and SQLAgentMail:

If we’re managing a single server or two, alerts and notifications may not be a big deal to us (though I’d still be debating that alerts and notifications are invaluable here, too). But if we’re managing multiple boxes, we’re very reliant on automatically generated messages from the servers to let us know what’s going on (or at least, if we do, our job again becomes easier). Here’s where SQLMail and SQLAgentMail come in.

It’s SQLAgentMail that sends out alerts and notification via e-mail. However, if there is a need to run SQLMail as well (this can be very handy if we want to process queries by e-mail or send an e-mail in a batch job or stored procedure), then it’s usually best to use the same user account for both MSSQLServer (which uses SQLMail) and SQLServerAgent (SQLAgentMail). This means only one mailbox has to be created on the mail server and only one profile has to be set up on the SQL Server itself. By the way, this is the recommended practice by Microsoft, according to Books Online.

Jobs:

Jobs run under the user context of SQLServerAgent. Any resources that need to be accessed had better have permissions granted to whatever user account is being used. I’ve run into this before. A job was created that accessed files on a different computer. The job failed though the commands looked correct. However, when we looked at the permissions on the directory that contained the files, the user account hadn’t been given permissions. Once that was fixed, the job ran fine.

This permission issue impacts us a lot when we’re scheduling DTS packages to automatically run. When we’re executing the DTS package from inside the Designer or even from the Packages list, it’ll execute based on the user context of the user manually running the package. However, if it’s being run as a job, it’ll be executed under the user context of the SQLServerAgent. About a year ago another DBA in my organization created a DTS package that imported data from an extract file. When he ran the package from the Designer, everything executed just fine. However, when he kicked off the job, the job would always fail. The reason was the same as above. While he had permissions to the directory in question, the SQLServerAgent user account did not. He immediately realized what the issue was because he understood the context with which the job was running, changed the permissions, and his job worked.

Here’s the bottom line: if our job requires us to access network resources, the System account and the local user account won’t work. This only leaves the domain user account as a valid option.

Replication:

Replication can be a tough cookie and a lot of people avoid it all together. I’ll have to admit that I haven’t used replication a lot, because I haven’t had a need to do so. However, there are some things to consider which will influence our choice of user accounts.

The SQLServerAgent (for the publisher) is required to be part of any Publication Access List, because the Replication agents run under the context of the SQLServerAgent. So if we’ve got multiple subscribers with sensitive publications (they shouldn’t be seen by each other), we probably shouldn’t use the same SQLServerAgent user account on the Publisher and the respective subscribers if we’re allowing for pull subscriptions. Here’s a real world example:

Let’s thing of a bank and specifically two departments, loan systems and customer support. Each has its own SQL Server installation. Customer support sees a subset of the loan data, and we’ve created a publication based on that subset. We’ve also created a publication for loan systems which has everything. Now let’s say these aren’t the only publications the publisher has, so we’ve decided to use pull subscriptions to balance out some of the load for our replication efforts. Now if the customer support SQL Server is running the same user account as our publisher with respect to SQLServerAgent, it would indeed be possible to accidentally (putting all malicious possibilities aside) create a subscription of the loan systems publication without too much effort. This is obviously something we want to avoid.

Let’s say however, that we are intending to distribute the exact same data across multiple sites. Choosing the same SQLServerAgent user account for all servers gives us a single user account with the permissions we require. And by default the various agents are going to use their SQLServerAgent, so we reduce our configuration efforts greatly.

Wrapping it up:

Microsoft recommends a domain user account with administrative rights to the local system for each of the two service accounts. Moreover, if we’re using SQLMail and SQLAgentMail, Microsoft goes a step further and recommends that the two services use the same account (and the same mail profile). This is a good general practice. There aren’t a lot of installations where we can get away with using the System account, and if we keep an eye to the future and possible expansion, it’s probably not a good idea to do so. Local user accounts are generally discouraged for the same reason. In fact, if we have to access network resources, only domain user accounts will do.

With that said, the question is how many different user accounts do we use? And this is ultimately impacted by some of the factors we’ve already considered. I’m a big fan of the K.I.S.S (Keep It Simple, Stupid) method. So I try to use as few different accounts as possible. It makes administration a lot easier, and though we didn’t talk about it, it means fewer accounts to monitor from a security perspective. When we weigh in all the factors we’ve talked about today, along with any applicable security policies for our respective organizations, the answer should be fairly straight forward.

Shortly after I posted the article, I got an e-mail from fellow SWYNK.com
columnist, Steve Jones (sjones@swynk.com). 
He disagreed with the recommendation on a single account if you don’t have to
worry about changing the password on a frequent basis.  Since I’ve worked
in organizations that do relatively frequent password refreshes, my
recommendations come from that line of thinking.  However, Steve’s
suggestions come from another angle.  Basically, if we’re creating very
difficult to crack passwords for administrator accounts, setting everything up,
and then locking up the passwords or destroying them, this method works very
effectively.  I have friends in the field who swear by this security
methodology, and given my druthers, I would choose it over the regular password
resets. Here are some excerpts from Steve’s message (used with permission):

There are two different preferred methods presented here.  Ultimately,
the security policy of our organizations determine which path is most
viable.  There are pros and cons to both methods, so consider them
carefully before you implement them. 

 Other Tools:

This is a short part of the article and I wouldn’t even bring it up if my recent experience with those technical interviews hadn’t taught me an important lesson: Enterprise Manager and Service Manager are so easy to use that some candidates have never used anything else for SQL Server management.

Since SQL Server and SQL Server Agent run as services, any tools that manage NT services can be used to help us manage our SQL Servers. Excluding any third party applications, that means we’ve got Server Manager from NT 4.0 (srvmgr \<Computer Name> from the command line) and the additional snap-ins for MMC (Microsoft Management Console) from Windows 2000. With Server Manager we can control the services of a remote server as if we had accessed the Services applet from the Control Panel on the box. There have been times when I’ve had problems with Enterprise Manger (which is also snap-in of MMC) and Service Manager, but I’ve been able to get at the services using Server Manager. Then there are cases when all I want to do is change a password. I find this task is generally quicker if I’m using Server Manager rather than waiting on Enterprise Manager to bring up the multi-tabbed dialog box along with the series of message boxes asking me if I want to stop and restart the service.

Now that Windows 2000 is upon us, Server Manager has a lot of its functionality replaced by snap-ins distributed with Windows 2000 for the MMC. One of these is the Services Snap-In, which skips all the extra steps I’ve got to go through with even Server Manager. I can get right at the services of multiple systems with but a single mouse-click once I’ve got everything set up in MMC. This is handy, indeed, and makes management efforts across multiple servers quicker and less error-prone. The important thing to remember here is there are other tools to manage SQL Server services other than Enterprise Manager and we should be familiar with at least the ones Microsoft provides, if not more.