So much has been said about service accounts for SQL Server, by so many people. So I figured I might chip in with my 2 cents.
Lets start with some background. First, what is a service account. It’s simple, it is the account a Service is running under. Basically the user that starts the software. If you log on to a desktop and start calculator, the service account for CALC.EXE (the process of the calculator) is running under the account you logged onto the system to. The makes it a service account in essence. However when you close the calculator this process stops and you it is gone. With SQL Server you do not want to process to stop when you close your screen or log off from the server. to help with this Windows uses so called services. These are processes that run in the background (as a service) and can start automatically when de system (e.g. a server) starts up. So far the basics… Easy right? So how does this relate to SQL Server.
SQL Server uses multiple services. The database engine uses SQLServ.exe, SQL Server Agent uses the SQLAGENT.exe and various other processes depending on the features of SQL you have installed. These can be seen in your taskmanager under the processes tab. But a process is not a service you might say. That is correct. Services are abstraction that might call upon multiple processes. So if you look on the Services tab in taskmanager, you will also find a service called ‘MSSQLSERVER’. This is the calling service of the process called SQLSERV.EXE. On the process tab you will also see a column called user. In this column you will find the account under which that process is currently being run and if this process has been called by a service, the account listed here is the service account. So now you know what it is and where you can find it… All fine and dandy, but why are service accounts important?
Well, since a service account starts a process it needs certain rights on your server to do that. And because SQL Server processes do stuff with files that exist in Windows it will not only need to be able to start a it also needs access to those files and be able to modify then (adding rows and such…). So it needs access to the filesystem to write stuff in a file (datafiles) and it needs to be able to create new files when you create a new database, etcetera, etcetera…. This service account needs a rights in your Windows environment and that means we need to start looking at security. So one solution is to run all the SQL Services under the a local windows account. Which is possible but not very manageable. Server the need to access data on other server, need to be given rights and that can get messy and expensive to maintain. If you have a few server it is not a huge problem but when you are managing 30 instances of a few 100’s, that it gets really annoying to maintain al those access paths. Also there is a risk that the local service account has too many rights on that server (and it does!). This will potentially expose your data to other processes on the same server that might be running under the same account (local system for instance). So you can imagine that using properly managed service accounts can help a lot with reducing risk for dataloss, reduce management overhead, reduce downtime and in general reduce the attack surface to your SQL Server and more importantly your data. So now we know what they are, where they can be found and why using proper service accounts for SQL Server is so important. Now lets bring it all together and talk about some Best-Practices for SQL Server Service Accounts and what can happen if you do not follow then (yes, the fun horror stories will be told here).
First up, Microsoft publishes a lot of information about service accounts for SQL Server. They do not do this because they like to publish, but because it is important (and shows off their knowledge). At the bottom of this article I will list a few links to relevant info. Do yourself a favour and RTFM on this topic because if you get this wrong you could end up on the front page of a newspaper and subsequently in the line of window 4 at the unemployment office.
Secondly, security is every bodies business in the modern office. Do NOT wait for someone else to address things when you notice something that could have potentially catastrophic consequences. Raise hell if you need to because if the business goes under due to a massive dataleak or loss, you will be out of a job too!
Now for my recommendations on service accounts.
- Read the Microsoft Best Practices for SQL Server Service Accounts. They build the product, they know the product, they are likely more knowledgeable about the product then you and I put together! So please read what they have to say about the topic. (LINK)
- Use accounts with only those rights that it needs and no more, so-called minimum rights. (LINK)
- Do NOT use the same account for multiple services or on multiple servers. For each service of each instance on each server, use dedicated accounts! (LINK)
- Use Active Directory based accounts (Managed Service Accounts) whenever possible. Central management of rights and passwords, anyone? 🙂
Now for the fun part, the horror story.
Too much rights for a service account and no separation between servers
(true story of personal experience).
I was once at a client where they used 1 service account for all services related to SQL Server. This service account was ‘sort of managed’ as in, it was in the AD. HOWEVER, what I regularly encounter is that service account for SQL Server are given WAY too much control…. In this case the administrators gave this service account a membership on the group that has LOCAL ADMIN privileges on ALL servers. Not just SQL Server, but ALL servers in their entire enterprise. This same group was then given a membership on the System Administrators role within all SQL servers. The combination of these 2 basically means I I wanted to do harm, I could do whatever I like to whatever server I want and no-one can stop me. I noticed this and as a good responsible consultant should, I informed the manager of the risks involved with this choice. He informed me that this was a common practice in the SQL Server community (at which point I wanted to run headfirst into the nearest concrete wall but did not…). I remained professional and explained it was not a common practice and it exposes his entire IT landscape to a direct shutdown of all servers, a reboot of certain servers, the dumping of all his databases or even worse the manipulation of all stored data in said databases. Now things got a little ugly…. He did not believe me (that’s fine, not my servers not my career….) and said I should proof it because I was wrong and no such risks exist…. In the following few minutes thing went from bad to worse and he basically called me a liar, a fraud and if I did not proof this he would not pay my fees and I could stuff the bill in that deep dark place that never sees the sun. By this time I was about done with him so I told him to email me his order to proof it in his production landscape, CC his manager in this and have them approve his order also (I don’t mind taking risks, but he was playing Russian roulette with a .45 automatic so taking out some insurance against his idiocy was warranted). I got the mails and approvals I wanted and I went to work…. Sidenote, who knows how to start xp_cmdshell runtime??? I do…. he did not know!! To make a long story short I used a very small script that uses a combination of xp_cmdshell (access to the OS), osql.exe (get a list of all actively advertised SQL server in the subnet) and some clever logic to combine that list with a shutdown command, and guess what…. within about 5 minutes all his SQL Server were offline.!
Moral of the blog: do your job right, read what you should read and make INFORMED decisions about risks because if you don’t, you will find yourself waiting in line 4 of the unemployment office.
Oowww and in case you are wondering, the manager in question was requested to find employment elsewhere in the weeks after this incident and I was requested to review their deployment methods and service account usage. 🙂