opkself.blogg.se

Note, the only change is to reference sys.certificates in the master database. LEFT JOIN c ON ddek.encryptor_thumbprint = c.thumbprint

The query to see which certificate is used to decrypt the my_db TDE-encrypted database should be: SELECT DatabaseName = d.name The certificate used by TDE to encrypt the database encryption key is actually stored in the master database, which is in turn encrypted by the database master key stored in the master database. So, I can see the database encryption key in, and I can see it's encrypted by a certificate, but the certificate doesn't exist? ║ DatabaseName ║ encryptor_type ║ opened_date ║ name ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ subject ║ WHERE d.name 'tempdb' /* tempdb is auto-encrypted by SQL Server */ LEFT JOIN sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint

INNER JOIN sys.databases d ON ddek.database_id = d.database_id USE įROM sys.dm_database_encryption_keys ddek I've used the following to see the details about the database encryption key, and the associated certificate, however the certificate details from the sys.certificates table is empty. This is the statement that is failing: USE ĮNCRYPTION BY PASSWORD = 'some_killer_password' Ĭannot find the symmetric key 'master key', because it does not exist or you do not have permission. I'm sysadmin on the server, so I should be able to see everything. It's a bit weird, but I'm sure I'm just doing something wrong. I'm trying to backup the master key for a TDE database, but SQL Server says there isn't one.