3

I'm working on something where using windows authentication to SQL server is difficult if not impossible. My team member is adamant that using SQL auth is much more difficult to manage and is a major red flag from a security and compliance perspective. I can definitely see how it could be more complicated than just using the domain credentials.

I don't have a lot of experience in the world of security and compliance. I'm wondering if this is universally accepted as a non-starter or if this is maybe just his preference?

Thanks!

1

In my experience, deferring to windows controller authentication is advantageous for a few reasons:

  • It simplifies removing access - how many times has someone logged into a sparsely used vendor platform and discovered that someone terminated years ago still had access? Anecdotally, I've had instances where I didn't even recognize the name of the user, as they had been terminated years before I had even been hired.
  • You can build your security/access around AD group membership. Similarly to the above, this simplifies updated/maintenance and decreases the chance someone else has access to something they shouldn't.
  • It lowers your overhead. Adding new users is not fun, and takes time away from other stuff you could be doing. Let the person in charge of onboarding worry about that, and then automatically piggyback off their work.
1

I don't have a lot of experience in the world of security and compliance. I'm wondering if this is universally accepted as a non-starter or if this is maybe just his preference?

Although this might be your co-worker's preference, it is also the best practice as suggested by Microsoft. The following is from the Microsoft website:

SQL Server has two authentication modes: Windows Authentication and Mixed Mode Authentication. In Windows Authentication mode, specific Windows user and groups are trusted to log in to SQL Server. Windows credentials are used in the process, either via NTLM or Kerberos.

It is a best practice to use only Windows logins whenever possible. Using Windows logins with SQL Server achieves single sign-on and simplifies login administration. Password management uses the ordinary Windows password policies and password change APIs. Users, groups, and passwords are managed by Windows system administrators; SQL Server database administrators are only concerned with which users and groups are allowed access to SQL Server and with authorization management.

SQL logins should be confined to legacy applications, mostly in cases where the application is purchased from a third-party vendor and the authentication cannot be changed. Another use for SQL logins is with cross-platform client-server applications in which the non-Windows clients do not possess Windows logins. Although using SQL logins is discouraged, there are security improvements for SQL logins in SQL Server 2005 and later.

From a security perspective it is better to use Windows integrated authentication because in SQL Server authentication mode credentials must be transmitted over the network when the initial connection is established.

Clients often store (or cache) these credentials on the client which increases the attack surface on the client side.

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.