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)
Scenario 2 (multiple AD accounts)
Server A accounts
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.
Answered by user507 on October 12, 2020
1 Asked on December 19, 2020 by revolucion-for-monica
1 Asked on December 19, 2020 by aditya-bhardwaj
1 Asked on December 19, 2020
4 Asked on December 16, 2020 by ronaldo
1 Asked on December 16, 2020 by j-hache
1 Asked on December 16, 2020 by gagandeep-singh
1 Asked on December 15, 2020
1 Asked on December 14, 2020 by adam-mulla
0 Asked on December 12, 2020
1 Asked on December 12, 2020 by michelle
3 Asked on December 11, 2020 by rai-micheal
1 Asked on December 10, 2020 by mihir-rane
0 Asked on December 8, 2020 by mouchin777
1 Asked on December 7, 2020 by chad-richardson
0 Asked on December 7, 2020 by nalzok
3 Asked on December 7, 2020 by genichm
Get help from others!