This editorial was originally published on Aug 10, 2012. It is being re-run as Steve is on vacation.
Many years ago I worked in a small company that only had about 5 or 6 servers. We had one system administrator whose job it was to manage all the servers. One day our sysadmin was on vacation when there was a problem with the Exchange server. One of the other developers worked on the system and ended up fixing it, but changed the service account password while doing so. The next day I walked into the office to find a group of people stymied as to what was wrong with the development server and version control system. Everyone claimed they hadn't changed anything on that server, and they were right. However our admin used the same domain account for all servers, including my SQL Servers. I changed the SQL service account that day.
One of the recommendations that I learned a long time ago, and one that I make regularly, is that every SQL Server instance should have a separate security account. In that case, I had separate accounts created for each database instance, and for each SQL Agent instance. We used long, random passwords that were never stored, and if we needed to access a password, we just changed it. That kind of flexibility and separation prevented any crosstalk issues between services, and it allowed us to easily alter permissions or passwords for one service without affecting any others.
The other day I saw someone recommending a single service account for all SQL Servers. Someone else recommended a single account for each version of SQL Server, using separate accounts where it's really needed. That's a better recommendation, but I still prefer completely separate accounts. I know that some security groups don't like that, but is it that big a problem? This Friday I wanted to ask you about your experiences.
Do you find separate accounts for each instance (or Agent) to be a security or administrative issue?
I'm not sure why this is unwieldy. Service accounts rarely change, and you could easily script changes to a group of accounts with PowerShell or some other tool. Once I set a service account, the only thing I might ever do later is alter the permissions to add access to a folder. When that happens, I definitely want to have separate accounts for each instance.
Let us know this Friday how you feel and what works for you.