Compare a credential secret against a known value

Suppose I have a SQL Server Credential saved in the database with a specific secret. I want to only modify the credential if the secret saved in the database is different from the actual secret.

Sometimes, an example is good, so take a look at this:

CREATE CREDENTIAL [MyCred]
WITH IDENTITY = N'DOMAIN\User'
    , SECRET = N'some_password';

So, after the password for DOMAIN\User is changed from some_password to some_new_password, I need to update the secret, but only if the stored value does not match. i.e. I don't want to blindly drop and recreate the credential.

With server principals, I can use the LOGINPROPERTY([login_name], 'PasswordHash') function to get the hashed version of the encrypted password stored in the master database, but that doesn't seem to work for credentials.

Encrypted Credential passwords can be seen via DAC:-

SELECT *
FROM [master].sys.sysobjvalues
WHERE valclass = 28;

They aren't stored the same way as Login passwords, they're encrypted using the Service Master Key (SMK).

This post goes further, and links to a PowerShell script to decrypt them:-

https://www.netspi.com/blog/technical/adversary-simulation/decrypting-mssql-credential-passwords/

You could automate the running of the PowerShell script to export the cleartext passwords for comparison.

Maybe a simpler way is to just check the [modify_date] of sys.credentials, if the date is earlier than a known password change (assuming you knew it had been changed), you'd know you needed to update the Credential.

If you're dealing with AD passwords, you can use Get-ADUser to find the password change date to compare with, but this gets trickier to get a reliable result under more complex AD environments.

StackExchange Database Administrators Q#322640, answer score: 6