0

I have made comparison between two sql servers on my environments A - SQL SERVER 2016 - Passowrd return value B - SQL SERVER 2019 - Password is null

Is there any setting to check.

Thank you in advance

2
  • You probably mean sys.syslogins, not sys.logins, right? Oct 26, 2023 at 10:55
  • That doesn't sound quite right. Could you post exact version for both servers in question? Or at least the one that returns value? (SELECT @@VERSION;) Oct 27, 2023 at 7:21

2 Answers 2

0

Why password return null

Storing passwords is bad security practice. If you store the password, and someone manages to hack your database and steal the passwords, they will try using the password to login to other services.

And if some user uses the same password for multiple accounts, they will inevitably get hacked. Sure, users are encouraged to use unique passwords for different services, but you can't really rely on them.

Instead of saving the password, services usually store just a hash* of password. The password columns is still there for backwards compatibility, but it doesn't return anything for the security.

If you check the newer sys.sql_logins view, you will notice it contains password hash, but not the password itself.

*Mathematical function that returns unique value, from which you can't guess the original input (except by brute forcing it). Password "fingerprint" of sorts.

1
  • I understood. When I run the syslogis query on multiple sql server, sql server A return hash value on sql authentication, but sql server B return null value on sql authentication. Is there some difference in setting on both sql servers?
    – zul nasir
    Oct 26, 2023 at 23:15
0

Why password return null

Because it's documented behavior.

https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-syslogins-transact-sql?view=sql-server-ver16

password nvarchar(128) Returns NULL.

3
  • I understood. When I run the syslogis query on multiple sql server, sql server A return hash value on sql authentication, but sql server B return null value on sql authentication.
    – zul nasir
    Oct 26, 2023 at 23:14
  • Is there some difference in setting on both sql server?
    – zul nasir
    Oct 26, 2023 at 23:15
  • I don't have an answer but I can guess. Encrypted password in sys.syslogins was legacy, nobody should use it so MS hardcoded it to NULL and put it in BOL.
    – SergeyA
    Oct 27, 2023 at 15:08

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.