Top 13 SQL Server Mistakes and Missteps – #9 Service Accounts
The service accounts for SQL Server have undergone expansion in the past few releases of Microsoft SQL Server. Back in the days of SQL Server 2000 and earlier you only had to deal with configuring a service account for the SQL and the SQL Agent services. Now you have to contend with those in addition to SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), Full-Text Search (FTS), SQL Server Integration Services (SSIS), and SQL Server Broker Service.
I tend towards the KISS approach – Keep It Simple Stoopid. I leave the defaults in place for all services except for SQL Server Service and SQL Server Agent Service.
The important thing to remember with service accounts, just as is the case with security in general in SQL Server is that you only want to give the service account the minimum amount of rights that are needed for the service account to do its job and handle all of its responsibilities. This means that domain admin is a no-no. That’s right. No domain admin rights for any of the SQL Server accounts. Period.
So what is the proper configuration for SQL Server Service? Domain User – Local Admin on the SQL Server should be the MAXIMUM level of rights granted to the SQL service account. Truly it should be a domain user only if the service account needs access across a domain to file shares, linked servers, or other functions requiring domain access. On the local side it really only needs access to the folders and functions it needs to perform SQL Server’s required tasks. Furthermore, don’t take the approach I used for so many years and use the same account on each SQL Server. Why?
- Even though no one should ever log in to a remote session on a server for diagnostics or any other reason it may happen. What happens if the DBA or authorized resource gets the password wrong enough times to lock the account? Badness.
- The chance of a virus or attack jumping SQL Servers throughout your domain is greatly enhanced if the same account is used.
As for the SQL Server Agent Service you should be sure to use a domain user account that has rights to only the locations it will be called upon to access during the normal course of work it is being asked to do across the domain. Need to write to a local or remote directory? Make sure that the proper read/write rights are afforded. Using the same account for the SQL Server Agent Service across your domain is completely acceptable. You should also ensure that it is associated with a valid mail profile that you can use to set up Database Mail in Microsoft SQL Server 2005+.
Core thing to remember: never grant more rights than necessary for any SQL Server service.
Next up…