2

When using SQL Server Authentication mode, I know that usernames and hashed passwords are saved in the master database. I wonder is there a way to retrieve that information? If yes, which tables stores it? (except contained database users)

Thanks for support.

2

I wonder is there a way to retrieve that information? If yes, which tables stores it? (except contained database users)

For SQL Server authentication you have password stored in hash format you can see it from sys.sql_logins, the column password_hash stores password in hash format. The information about SQl server logins are stored in master database and each login has SID respective to it. Only SA login has same SID no matter what server it is. To get SQL authenticated logins you can use below query

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'S'
and name not like '%##%'

You can use sys.database_principals and sys.server_principal to gain more information about logins and users.

Regarding would you be able to recover the hashed password or decrypt it in some somway, the answer is NO you cannot.

  • Wouldn't a brute force search work, though? Sure, it's going to be painfully slow for sufficient complex passwords. – vonPryz Aug 30 '18 at 8:52
  • 1
    Yes, a brute force would work. But then you technically didn't recover or decrypt it. You just guessed until you happened to guess right. :-) – Tibor Karaszi Aug 30 '18 at 8:58

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.