SQL Server Authenticated Users, why are we still using them?

Question

SQL Server Authenticated Users, why are we still using them?

Viewing 5 posts - 46 through 49 (of 49 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Eric M Russell - Wednesday, April 11, 2018 7:57 AM
There is an alarming issue when using SQL Server authentication that many DBAs don't consider. Keep in mind that once an application or user logs in, there is nothing preventing them from changing the account password using ALTER LOGIN statement and thus blocking any other users from logging in. An account doesn't need sysadmin membership to change it's own password.
ALTER LOGIN TestApp WITH
OLD_PASSWORD = 'TheOriginalPassword'
PASSWORD = 'HaHaHa@ImSoEvil';

That's more an alarming issue with letting people share login information regardless of whether it's SQL Server, AD, or any other system that stores login information.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

ZZartin - Wednesday, April 11, 2018 8:30 AM
Eric M Russell - Wednesday, April 11, 2018 7:57 AM
There is an alarming issue when using SQL Server authentication that many DBAs don't consider. Keep in mind that once an application or user logs in, there is nothing preventing them from changing the account password using ALTER LOGIN statement and thus blocking any other users from logging in. An account doesn't need sysadmin membership to change it's own password.
ALTER LOGIN TestApp WITH
OLD_PASSWORD = 'TheOriginalPassword'
PASSWORD = 'HaHaHa@ImSoEvil';
That's more an alarming issue with letting people share login information regardless of whether it's SQL Server, AD, or any other system that stores login information.

For domain authenticated service accounts, the AD admin can disable account's permission to change the password, so there is some level of centralized control. However, as far as I know, the DBA can't similarly disable user password reset permission for SQL accounts.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

qbrt SSCrazy Points: 2737 More actions · Answer 3

Just wanted to throw this out. I've setup and used gMSA in the past. Although I'm no expert at it.

https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview

There are many other links on this topic on the internets.

lmalatesta SSChasing Mays Points: 637 More actions · Answer 4

Eric M Russell - Wednesday, April 11, 2018 8:59 AM
For domain authenticated service accounts, the AD admin can disable account's permission to change the password, so there is some level of centralized control. However, as far as I know, the DBA can't similarly disable user password reset permission for SQL accounts.

If you really want to disallow SQL users changing their passwords, you can set a trigger on ALTER_LOGIN that throws an error and rolls back any attempts to change the password.

That practice seems ill advised to me, but it's not hard to do and since it's a trigger, you've got a lot of flexibility. For example, you could create a database with a table such that only logins that correspond to records in that table are unable to change their password.

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 5

Eric M Russell - Wednesday, April 11, 2018 8:59 AM
ZZartin - Wednesday, April 11, 2018 8:30 AM
Eric M Russell - Wednesday, April 11, 2018 7:57 AM
There is an alarming issue when using SQL Server authentication that many DBAs don't consider. Keep in mind that once an application or user logs in, there is nothing preventing them from changing the account password using ALTER LOGIN statement and thus blocking any other users from logging in. An account doesn't need sysadmin membership to change it's own password.
ALTER LOGIN TestApp WITH
OLD_PASSWORD = 'TheOriginalPassword'
PASSWORD = 'HaHaHa@ImSoEvil';
That's more an alarming issue with letting people share login information regardless of whether it's SQL Server, AD, or any other system that stores login information.
For domain authenticated service accounts, the AD admin can disable account's permission to change the password, so there is some level of centralized control. However, as far as I know, the DBA can't similarly disable user password reset permission for SQL accounts.

This would only happen if you let users run SQL statements. Run everything through Stored Procedures and don't allow direct reads and writes!

Student of SQL and Golf, Master of Neither