0

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)

or

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!

Cheers,

Chris

  • Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area. – Jonathan Fite Mar 6 '17 at 16:27
  • We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them? – Chris Di Carlo Mar 6 '17 at 16:30
  • It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers. – Jonathan Fite Mar 6 '17 at 16:41
0

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.

  • Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible. – RDFozz Mar 6 '17 at 16:44
  • 1
    @Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all. – Chris Di Carlo Mar 6 '17 at 16:51
  • 1
    SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches). – Sean Gallardy Mar 6 '17 at 16:52
  • @RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management? – Chris Di Carlo Mar 6 '17 at 16:53
  • That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow. – RDFozz Mar 6 '17 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.