I am looking at changing all of our SQL Server instances to use AD accounts for the running services. I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc. My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.
For example, if I have 2 servers A and B both running a database instance and SSIS:
Scenario 1 (single AD account across domain)
- SQLDB (database service)
- SQLSSIS (SSIS service)
Scenario 2 (multiple AD accounts)
Server A accounts
- SVRA_DB (database service on server A)
- SVRA_SSIS (SSIS service on server A)
- SVRB_DB (database service on server B)
- SVRB_SSIS (SSIS service on server B)
I know scenario 2 won’t work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire. Just wondering if there are any other gotchas to either approach and what best practices might be?
As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.
Thanks in advance for any help or insight!
Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.
Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).
The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package…not the service.
SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.
In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.
Some may tell you that Managed Service Accounts are an option, but…SQL Server cannot use MSA.