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…
I have heard a lot of people say that Local Admin is still over privileged for the service account. Any chance you could elaborate on why you should use Local Admin?
Good point Mike, I did overstep the mandate of Least Privilege. Local Admin does afford too much rights still and I’ll alter the post to reflect that. What I wanted to get across is that I’d err on the side of Local Admin over Domain Admin if push came to shove. However it sounds as though I am pushing it as a solution.
You should only grant the bare minimum amount of rights that a login needs to perform the necessary tasks whether that is a service task or user task inside of an application or database.
Can we make gotcha #13.1 using an account which is not an admin on the local machine and then forgetting to to grant it the ‘Perform Volume Maintenance Tasks’ right in the local security policy? It’s granted to Admins by default so sometimes people don’t learn to grant it individually. So sad to lose instant initialization.
Wait, where’s #13-10?
Search on Top 13 Mistakes on the blog. They’re all here.
Can you create a page with links to each of the 13 tips?
When I click on the “Top 13 Mistakes” tag, it only pulls up the most recent, then makes you click “Newer” to get to the others instead of giving you a listing of all articles with that tag.
I searched for Top 13 Mistakes and it only pulled up one.
Are you ever going to finish these?