Problem Scenario
Recently, a very interesting question was put to us by our client. He was getting ready for migration for few databases between two SQL instances with SQL 2012 as source and SQL 2019 as target SQL instance.
For this, he used sp_help_revlogin to copy logins from source to target few weeks back. Source and target SQL instances were both live and in production and before migrating next batch of DBs to target, he wanted to check if there is any possibility of having two SQL logins with same name on source and target and yet have different passwords.
Understanding concept
In order to find the solution, we should understand that whenever a SQL login is created using password, it is assigned a unique SID and then converted into a hexadecimal hash value as shown below:
This hash value generated is also unique which is done by a certain combination of SID and password using some function. To prove this, I created two SQL login with name testlogin1 and testlogin2 with same password as testlogin1. But hash password generated was totally different as shown in below screenshot:
Another way to check the login password value is to go to that particular login and generate create login script. I did that for both logins mentioned above and here also, the values were total different with a message “For security reasons the login is created disabled and with a random password.”
Solution
With all the concepts placed above, only possible solution that I could think of was comparing password Hash for each password. Since, in our case, customer used sp_help_revlogin to migrate logins, password hash was matching for each of them as same SID is migrated to target SQL instance.
The comparison can be done either by storing password hash (using table sys.sql_logins) into a table for each SQL instance and then comparing hash values using inner join. Please note, you will require a linked server from one server to another to do this.
Another way could be by using excel sheet.
Only difference I found was with SQL logins which were created separately on each instance. There password hash was different and same was highlighted to customer.
Let me know if you know any other way of comparing the passwords.
Happy Learning!