I've been doing some work on SQL Server 2005 and service accounts and noticed some interesting things that I thought people should be aware of. There have been quite a few changes in this area with SQL Server 2005 and while none of it's that complicated or confusing, it is different and there are quite a few more moving parts. And most of them for the better.

Services and Instances

The first thing to be aware of is that there are many more services with SQL Server 2005, ten in total if you install everything. I'll stick with the major ones, but the full list is at Setting Up Windows Service Accounts. Each of these services has its own base name and instances of the service will have similar names. The table below shows you how these relate:

If you notice above, some of these services have an N/A for the instance version. This isn't a typo, it's explained next. For the items with InstanceName, this is replaced with the actual name that is assigned to that instance of SQL Server 2005.

One thing to be aware of with the various components is that not all of them are instance-aware. You may be wondering what that means, but basically an instance-aware service is one that is installed with a separate copy of its executables for each instance. An instance-unaware service is one that is only installed once on the Windows host. As an example, Integration Services is only installed once, no matter how many instances you have and there is only one service per Windows installation.

This isn't a good or bad thing, but it does make some limitations that you need to be aware of with the product. Some of the services only need to be installed once. It also means that if you are depending on scaling out these features with multiple installs, it will not work.

Security

There are a few security changes from SQL Server 2000 that I highly welcome, although they may appear to clutter up your server. When you install using setup, the service account chosen is assigned the proper rights and permissions to run SQL Server. If you have done your prep work and created the account before you install SQL Server, then you don't need to worry about rights, nor do you need to run under administrator. Any old domain account will work and setup will assign the permissions.

As in SQL Server 2000, the setup program will grant the appropriate user rights, such as "Log On As A Service", "Replace a Process-Level Token", etc. as needed for that user account. The ACL permissions, however, are handled differently. In SQL Server 2000 there was a list you could check and assign to any account as needed or create your own local group with the necessary permissions.

SQL Server 2005, I'm happy to say, does this for you. For each component, and for each instance, SQL Server 2005 sets up a local group with the appropriate ACL permissions. You can get the list from the reference above, but there's no need. The table below shows the groups created for each component, which you can use if you need to change the service account.

However you don't need to do this yourself. In fact, it's highly recommended you don't do it yourself.

Instead there is now another client utility, the SQL Server 2005 Configuration Manager, that allows you to manage all your services. A screenshot below shows this on one of my machines and it lists all the services I have running.

As you can see the names correspond to the instances and there's also a type to help you identify if you can't decipher the names. This is the preferred tool for starting, stopping, and working with the services. So if you need to change a service account, use this tool.

One great thing about this tool is that not only does it grant the necessary user rights to the new service account as well as place it in the appropriate group, it also REMOVES the old group. Yes, the one thing that so many administrators forget to do, clean up after themselves, is now automated in this tool.

Conclusion

For once, this topic has some great details in Books Online. The reference given above is long and covers the permissions and recommendations in detail. I haven't covered the types of accounts and the procs and cons, but you should really always create a domain (or local) user and let that user run the service. And a different user for each service, which will limit dependencies and failures and allow you to set permissions as needed to handle whatever duties that particular service needs to deal with.

The service accounts are the basis for your SQL Server security. After all, the context and rights assigned to these accounts are what is available to the server and potentially any hacker that compromises your service. Take the time to set them up right and ensure that you are complying with the Trusted Computer Initiative and granting the least permissions necessary to do the job.

©2006 Steve Jones (dkranch.net)