How to check if credential password for TDE matches the secret key of Key Vault

Question

How to check if credential password for TDE matches the secret key of Key Vault

Micuen Tabazura

SSC Enthusiast

Points: 183
More actions
April 2, 2025 at 8:33 am

#4571776

Hi all,
In my company we have many databases encrypted with TDE and there is some automatic process or pipeline that is not working correctly and updates the secret key in the Key Vault without applying the change also in the credential password in the SQL server. I need some help while we found this process and fix it.
The thing is that everything continues to work correctly for weeks at least until a server restart occurs and then the database becomes inaccessible. I don't know if there are other events that cause this.
I enabled logging of the cryptographic provider to the Windows event log and have detected the problems there, but as I say, they are usually only triggered by rebooting and forcing an access to the Azure Key Vault through the cryptographic provider dll.
What I would need is, from the SQL Server side, to know if the credential I have is OK, i.e. force the SQL Server to access the Azure Key Vault through the cryptographic provider dll to validate if the credential password matches the secret.
The only way I have found to do this is to run a dbcc checkdb, which fails when tried, but if the database is large it takes a long time to finish and affects performance.
Do you know of any other way to force SQL Server to check this accessing the Azure Key Vault using cryptographic provider dll?
Thanks and regards

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tav29 Old Hand Points: 312 More actions · Answer 1

--Check The Credential setup

SELECT name, credential_identity FROM sys.credentials WHERE name = 'YourCredentialName';


--Validate the Key Vault Key
SELECT name, key_algorithm FROM sys.symmetric_keys WHERE name = 'YourTDEKeyName';

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal

Micuen Tabazura SSC Enthusiast Points: 183 More actions · Answer 2

Thanks @tav29 but that doesn't help for what I need.

Maybe I didn't explained myself clearly.

On this Q&A someone explains how often SQL Server contacts Azure Key Vault:

How often does SQL Server contact the Azure Key Vault for a Certificate or Key? - Microsoft Q&A

dbcc checkdb is the only way I know, apart from service restart and encrypt/decrypt operations, that implies EKM cryptographic provider DLL accessing Azure Key Vault and writing to Windows Event Log in case of warning or error (after having enabled logging)

I would like to know if there is another SQL command that forces that kind of access, like dbcc checkdb does, but lighter, to avoid affecting performance.

That way I can be completely sure that I have an issue without waiting for a planned maintenance window or patching cycle to have a restart and find that my database is inaccessible.

Thank you again, regards!

This reply was modified 5 months ago by Micuen Tabazura.

Tav29 Old Hand Points: 312 More actions · Answer 3

Found this:

Use Key Vault Insights: Azure provides Key Vault Insights, a feature in Azure Monitor, to visualize metrics like request frequency, latency, and failures.

Monitor SQL Server Activity: If you're using SQL Server's Always Encrypted or Transparent Data Encryption (TDE) with Azure Key Vault, monitor the SQL Server logs for operations that trigger Key Vault access.

jasona.work SSC Guru Points: 50216 More actions · Answer 4

Would creating a small, empty database that you TDE-encrypt / decrypt, perhaps on a schedule be an option? This would satisfy one of the reasons given in the MS Q&A you linked to "force" SQL to try to talk to the Vault.

And when I say a "small" database, maybe something only a couple MB at the most, so it would take very little time and resources to encrypt / decrypt. Granted, this is kind of a "hacky" solution, not as "elegant" as a query, but...