2

I'm currently assessing a setup of Data warehouse implementation in our environment, based on the requirements:

  1. A functional/service ID to be setup to access multiple DB servers of other applications in the company for replication purpose.
  2. Password of the service ID cannot be changed and won't expiry

Based on some researches, I learned that Windows authentications are always recommended as it is more secure (here and here).

Further exploring on windows authentication, I noticed the guides always linked it to Kerberos authentication, which involve domain controller where the an AD account is being used (link). It make the ID management easier but due to it is a functional ID, I'm not sure if this is the best approach. There is also an article here mentioned to use a local account instead of domain account when possible.

Question:

  1. Since it is a service ID, and it will connects to many applications, shall I really avoid domain account? My concern is that the password isn't expiring and it has accesses to most of the DB, the risk is higher if this single account is compromised.
  2. Does setting up login using local Windows account in respective DB server provide the same advantages of domain account (e.g. use Kerberos protocol)?
  3. Is creating multiple local Windows account the recommended approach based on above requirements? Or if there is any resources regarding this can be shared for me to further study on my own.

I hope my questions are subjective but not opinion based/open-ended, and I appreciate for any input and sharing.

1 Answer 1

0

You should use an AD account, rather than local Windows accounts which you have to manually keep synchronized among the servers.

If the service account is used to run the SQL Server service, it should be a Managed Service Account or Group Managed Service Account, so AD will manage the password.

If you need to know the password, then a regular domain account that is excepted from password expiry and account lockout should be used.

3
  • Hi David, thank you for the answer but I have a few questions: 1. if I'm using the local Windows accounts, the password will be non-expiry and I don't think I need to manually keep synchronized anything? 2. I'm looking at service account to connect to the DB instead of running it, I'm aware of the gMSA but the DWH application does not support that.
    – nlks
    Dec 30, 2022 at 1:26
  • If the password is compromised you must change it, even if it doesn’t expire. Dec 30, 2022 at 2:29
  • Understand that we must change the password if it is compromised, but my concern here is that AD ID is used to access to multiple database servers, so if this 1 account is compromised, the impact is very high (e.g. possible data breach of 10 application databases compare to 1 DB breach if I use 1 local Windows account per DB server)
    – nlks
    Jan 17, 2023 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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