0

Does any application requires a windows/domain login to be explicitly added to a SQL Server instance or by replacing the login with an AD group that belongs to the server and has elevated permissions I would achieve the same thing?

To clarify, I am referring specifically to application accounts (In this case we have an account for TFS (team foundation services)) and not actual "users" accounts. Will any application fail if they can't find an explicit windows/domain user on the server?

2
  • Are you configuring a new TFS and want some advice on best practices or you're experiencing some error on an old instalation? If you already has an error, please, post the error message.
    – Ronaldo
    Commented Apr 20, 2020 at 14:09
  • This is not specific to TFS; I was just giving an example. I am a DBA and we are cleaning up individual logins on our SQL Servers by replacing individual logins with AD groups on the server. This TFS login account we have I am not entirely sure what it is for... It could be a service account or it could have been an account somebody added years ago for the initial setup. What I am really asking though is whether or not there is ever a situation where an application REQUIRES an individual account.. i.e. a scenario where having an AD group will not work.
    – Data Dill
    Commented Apr 20, 2020 at 14:16

2 Answers 2

0

Microsoft's doc about Connecting Through Windows Authentication says:

By using Windows Authentication, Windows groups can be created at the domain level, and a login can be created on SQL Server for the entire group. Managing access from at the domain level can simplify account administration.

There are no remarks about restrictions for apps when using a group instead of a login. Also, John F. Tamburo' article says:

  1. The group membership is used as a path to access; all activity is logged against the actual user's account. sp_who2 and every auditing tool will show logins based on the actual end user; and DDL/DML changes will be audited to that user. No more guessing who used the application's SQL login - It's gone! For applications that run as Windows services, the service account under which the application runs is made a member of the necessary groups. (emphasis added)

So, as long as the group mapped as the SQL Server login keeps the needed privileges for the applications, no problem would be found on that change.

0

Does any application requires a windows/domain login to be explicitly added to a SQL Server

Yes, application designers do stupid things. It is not possible to guess all the stupid things the designers of applications in your environment have done.

I have a vendor using one of the databases I support, who while they do a regular update, have a check to make sure the account 'sa' exists and that is has sysadmin rights. This is hard coded in their update process.

Best practice is to rename and disable the 'sa' account Reference

For years, when they needed to do an update, I have to go create the account 'sa' and grant it sysadmin rights. They don't use that account to make the update, but it has to exist. Will they every change their code? I don't know they have been promising to for years.

Your goal to move everyone to windows groups is a good one but you will need to use caution.

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.