5

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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323

1 Answers1

6

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.

Rob Dalzell
  • 856
  • 7
  • 12