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.

1 Answer 1

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.

2
  • thanks Rob, this is for a DevOps deployment pipeline, so it's not using the DAC. I like the idea of checking the accounts password change date, and comparing that to the credentials modify_date column.
    – Hannah Vernon
    Jan 26, 2023 at 22:34
  • 2
    Be careful using that approach, it's not entirely correct: seangallardy.com/… Jan 27, 2023 at 13:50

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.