I'm attempting to audit/very TDE encryption of a database; significantly I've inherited a bunch of certificates and passwords and I'm attempting to update documentation to properly indicate the current/correct keys and passwords.

Working backwards from the DB > DMK > SMK:


Querying sys.dm_database_encryption_keys and sys.certificates i get:

 DbName | encryp_state | type       |    name   | expiry
 NProd      3           CERTIFICATE     tdeCert   2014-10-10 18:10:55.000

Database is encrypted using tdeCert with expiry of 10/10/2014; correct?


Checking the same sys.dm_database_encryption_keys i see the following for master:

 DbName | is_master_key_encrypted_by_server
 master   1

Does this indicate that the DMK is secured by the SMK?


Performing a fresh backup of the SMK successfully indicates that it is accessible, correct?

-- Creates a backup of the service master key.
BACKUP SERVICE MASTER KEY TO FILE = 'J:\SQLKeys\2021_04_22-service_master_key.key'
    ENCRYPTION BY PASSWORD = 'supersecurepassword';

DMK Confusion

When i run the following query:

    b.name,  a.crypt_type_desc, b.*, a.*
    sys.key_encryptions a   INNER JOIN sys.symmetric_keys b    ON a.key_id = b.symmetric_key_id
    b.name = '##MS_DatabaseMasterKey##';

enter image description here

I get two results, one indicating encryption by master key; another indicating encryption by password. Is this expected? Does this indicate the SMK or password can be used to decrypt the DMK?

Putting Everything Together Is it possible to perform a new backup of the SMK, and a backup of TDECert, and then have all of the required certificates and keys to properly restore the database if needed?

  1. Backup SMK
  2. Backup TDECert


 -- Creates a backup of the service master key.
    BACKUP SERVICE MASTER KEY TO FILE = 'J:\SQLKeys\2021_04_22-service_master_key.key'
        ENCRYPTION BY PASSWORD = 'supersecurepassword';
  -- Creates a backup of the tde certificate and key.
    BACKUP CERTIFICATE tdeCert TO FILE = 'J:\SQLKeys\2021_04_22-tdeCert.cer' WITH PRIVATE KEY (FILE='J:\SQLKeys\2021_04_22-tdeCert.pvk', ENCRYPTION BY PASSWORD = 'supersecure');
        ENCRYPTION BY PASSWORD = 'supersecurepassword';

Additional Questions

  1. Running the SQL: SELECT SIGNBYCERT( CERT_ID( 'tdeCert' ), 'test' ); indicates that I can properly decrypt everything?
  2. Given the age of the certificate is there an advantage to rotating the keys?
  • can't answer all your questions but I know TDE ignores certificate expiry dates Apr 22 '21 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.