AnswerBun.com

Should I run SQL Server services on multiple servers under different AD accounts?

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)

  • 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

One Answer

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

Add your own answers!

Related Questions

How to insert several csv files into Elasticsearch?

1  Asked on December 19, 2020 by revolucion-for-monica

 

passing variable values to procedure in postgresql via psql

1  Asked on December 19, 2020 by aditya-bhardwaj

 

PostgreSQL: interpolate missing value

1  Asked on December 17, 2020 by captainahab

 

duplicate key error index MongoDB

1  Asked on December 16, 2020 by simonell

     

Why is Postgres service failing?

1  Asked on December 13, 2020 by mike_butak

   

MySQL Shell dump utility – maximum possible chunk size?

1  Asked on December 10, 2020 by mihir-rane

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP