Database Administrators Asked by Chris Di Carlo on October 12, 2020
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 November 8, 2021
2 Asked on November 8, 2021 by thepieman314
1 Asked on November 8, 2021 by olivier2101
1 Asked on November 5, 2021 by matt-alexander
5 Asked on November 5, 2021 by phill-pafford
2 Asked on November 5, 2021 by user3598321
1 Asked on November 5, 2021 by andrew-cooke
1 Asked on November 5, 2021
1 Asked on November 5, 2021 by reluctantdba
2 Asked on November 5, 2021
0 Asked on November 5, 2021 by damon
1 Asked on November 5, 2021
2 Asked on November 5, 2021 by hooman-valibeigi
1 Asked on November 5, 2021 by user6826691
2 Asked on October 28, 2021 by ian-yu
1 Asked on October 28, 2021
0 Asked on October 28, 2021 by moritz-roessler
0 Asked on October 28, 2021 by jodocus
1 Asked on October 28, 2021 by marcin-wilczek
1 Asked on October 28, 2021 by apt605
Get help from others!
© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP