I recently wrote an article on SQL Server 2005 service accounts, giving some basic information on the services available and how the service accounts are setup and managed. However there was a question from someone on what type of account to use and how to parcel this out amongst the ten potential services that are running. It's unlikely that you will have 10 services running on a single SQL Server, but you could easily have 4 or 5. With a larger, consolidated server and multiple instances, this could grow.

I've had similar questions in the past, often from people trying to determine how to best setup their services, which in SQL Server 2000 were limited to 2. However, there were still many people confused about the best practice for setting up services, especially Windows administrators new to SQL Server. So this article is an attempt to give some advice and reasoning for the different ways to setup your services.

Common Setup

There are two common SQL Server setups that I have seen. Neither is recommended, but they are the easiest ways to setup SQL Server and so many people choose them.

The first one is choosing to run all of the services under the Local System account. This was the only built-in account prior to Windows 2003 that allows you to run a service. It's available from the setup program and most people use this account if they have not already created an account. This isn't recommended as it is equivalent to, and in some ways more powerful than, the local Administrator account. This gives unnecessary privileges to the SQL Server service and if the server is compromised, it could lead to someone taking over your entire Windows server.

I know the hacker argument is not that persuasive since it rarely happens and it's an abstract concept for most people. But there are still risks. Someone could easily create a job or run an xp_cmdshell command that damages the Windows host, deleting files, or doing some other action that might disrupt your service.

The other setup I often see is to have SQL Server setup as an administrative account, either the Administrator (local or domain) or the administrative account of the person that setup SQL Server, such as sjones. People often do this because they think it is required or because they cannot get something to work and default to the most powerful account to prevent any issues. This is a poor practice for the same reasons as using Local System, but also for another one.

We often require password changes for user accounts, including the Administrator account. This is a common, and indeed best, practice that usually results in someone forgetting to change the password for the service. Then a few weeks later SQL Server will not start and no one knows the reason why or a network access fails and a large amount of time is spent troubleshooting.

Both of these practices should be avoided and this can easily be done with a little preparation and by implementing a standard procedure.

Recommendation: DO NOT use Administrator or LocalSystem.

Common Accounts

It is rare that I see one SQL Server in a company, unless they are very small and have purchased a database server for a particular software program. Usually there are two or more, perhaps hundreds, of SQL Servers in your enterprise. And I've often seen them all running under the same domain user account if they are not under LocalSystem or Administrator.

Using a common account for your SQL Servers isn't a great idea. It seems like less of an administrative burden to only create one account, a central place to change the password, and even a single set of permissions to set. But this is a poor idea as well. Suppose you run ten or more servers on one account. A password change would require you to visit all those servers and if you forget one, then you may suffer some of the issues above. I know that you would probably rarely change service account passwords, but I have seen this cause issues before with strange behaviors or servers failing to restart after a patch. Chasing this down when you have 3 servers probably isn't a big deal, but with 300 it's not much fun.

It's also a security risk in that one account now has access to a large number of servers and permissions. For the same reason you do not want to have one user with more access than required, you should not setup your servers like this. Permissions always grow as needs change and they are rarely revoked. Over time you may end up with way more permissions for this account than desired.

This becomes more of an issue with SQL Agent. Since this is a service that runs jobs and performs actions, often on the file system or on another server, it is more of a security risk than the main database server. Also, since this account usually sends mail out in response to events, a separate account helps to determine the source of issues.

It is rare that something will change on your service accounts, that they will be hacked, or that they will do anything other than just run SQL Server. However if something does happen, then would you rather have 1 server/service go down or have 100 go down? Resetting passwords, changing accounts, or anything else on a large number of servers takes time and is completely unnecessary. If a separate account is used, then the dependencies are minimized and this type of thing never happens.

Recommendation: Separate account for each service and server.

User Accounts

So if you believe that the above recommendations are good ideas, what account should you use?

My recommendation is that you create a domain user account for each service and server. This domain user should be a basic account, a member of the Everyone group and no other on the domain for a standalone SQL Server. If you assign this account using the setup program, Enterprise Manager in SQL Server 2000, or Configuration Manager in SQL Server 2005, then the proper rights will be assigned on the local machine, both user rights and the ACL permissions to folders. By using a basic user account with no specific groups, this account has minimal security vulnerabilities.

If you are changing anything about the SQL Server setup, meaning storing files in non-default locations, or requiring access outside of the default locations, then you should also create a group for the service. My recommendation for this is to create a group that uses SQL in its name along with the service and instance names. If this is a domain group, include the server name as well. Assign the rights needed to this and then include the domain user in this group. Use a domain group for network or multi-server access.

I would recommend you not reuse user groups for this purpose, as the rights for those groups change and they should be geared to a particular job function for a user(s). Instead create SQL specific groups that are used to provide the rights needed for that database server to function.

This also simplifies the use of mail services for SQL Server. By choosing a domain user for each mail service (separately for SQL Server and SQL Agent), you can create separate mail boxes and trace back a mail message to the original location. This also reduces the dependency between services so if one mailbox fails for some reason, the other continues to work.

Planning

Before you install SQL Server, or add a service, you should do the minimum amount of planning and at least create a domain user account for each service, and if necessary for non-default permissions, a group as well. This sounds like a lot of administrative work, but it is only done once for each service and is a very simple and quick procedure. Choose a standard naming convention that handles multiple servers, instances, and services. SQL Server 2005 provides a good template for this in how the services and groups are setup.

I would choose a convention that is simple, like SQL_(servername)_(instance)_(service) since I like Hungarian notation and any scripting or other programmatic code does not need to mess with any space-in-the-name issues. The particular convention does not matter as much as long as it is easily recognized by other employees and documented.

As you add these, be sure you set the password to not expire and not require a change. It's a good practice to periodically change these, but if you forget or don't have time, you don't want them expiring. Also, don't require a change since these will likely log on without your interaction the first time.

One last mention on planning: set a strong, complicated, hard to remember password. Since you only have to set this once (hopefully!), make it a long 15-20 character, mixed case, numbers, letters, very strong password. Write it down if you must, maybe even putting all the accounts on one piece of paper for long enough to get them setup then destroy the paper.

Conclusion

Setting up separate accounts for each server, mail sender, computer, and then adding in groups is a bunch of busy work. It's tedious, not fun, and kind of boring, which is why most people don't do it. However, it's also a one-time thing, or at least a relatively rare thing. Spend a few minutes and get the accounts setup properly and it will potentially save you later on down the road.

The last thing you want is to have a server, or worse, 30 servers, not restart after a midnight patch application.

©2006 Steve Jones (dkranch.net)