SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2005 Service Accounts

By Steve Jones, (first published: 2006/03/23)

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:

Component Default Service Named Instance Service
SQL Server SQL Server (MSSQLServer) SQL Server (InstanceName)
SQL Agent SQL Server Agent (MSSQLServer) SQL Server Agent (InstanceName)
Integration Services SQL Server Integration Services N/A
Analysis Services SQL Server Analysis Services (MSSQLServer) SQL Server Analysis Services (InstanceName)
SQL Server Active Directory Helper SQL Server Active Directory Helper N/A
SQL Server Reporting Services SQL Server Reporting Services (MSSQLServer) SQL Server Reporting Services (InstanceName)
SQL Server Browser SQL Server Browser N/A
SQL Server FullText Search SQL Server FullText Search (MSSQLServer) SQL Server FullText Search (InstanceName)
SQL Server Writer SQL Server VSS Writer N/A

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.

Component Default Service Named Instance Service
SQL Server MSSQLSERVER SQLServer2005MSSQLUser$InstanceName
SQL Agent SQLServerAgent SQLServer2005SQLAgentUser$InstanceName
Integration Services SQLServer2005DTSUser N/A
Analysis Services MSOLAP SQLServer2005MSOLAPUser$InstanceName
SQL Server Active Directory Helper N/A N/A
SQL Server Reporting Services ReportServer SQLServer2005ReportServerUser$InstanceName
SQL Server Browser SQL Server Browser N/A
SQL Server FullText Search SQLServer2005MSFTESQLUser$MSSQLServer SQLServer2005MSFTESQLUser$InstanceName
SQL Server Writer N/A N/A
SQL Server Notification Services SQLServer2005NotificationServicesUser N/A

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)

 
Total article views: 49576 | Views in the last 30 days: 51
 
Related Articles
BLOG

SQL Server – Script to get Service Account for all local instances

Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a......

BLOG

SQL Server – Script to get Service Account for all local instances

Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a......

FORUM

SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

URGENT HELP= SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

FORUM

service account

Service account

FORUM

SQL Server Agent Service account

Why did I have to add our domain sql server service account manually?

Tags
 
Contribute