FAQs about Transparent Data Encryption

• Can I backup and restore database implementing TDE with an expired certificate?

  Yes, you can back up and restore the database implementing TDE with an expired certificate. However, an error message similar to Warning: The certificate you created is expired is displayed.

• Does database connectivity gets affected with an expired certificate?

  No, database connectivity does not get affected if the certificate has expired.

• How to renew an expired certificate TDE?

  To renew the expired certificate, perform the following steps:

  1. Run the following query to check the current status of encryption or decryption. Note that depending on the database size, it might take some time to get decrypted.

     SELECT DB_NAME (database_id)

     AS DatabaseName, encryption_state, encryption_state_desc =

     CASE encryption_state

     WHEN '0' THEN 'No database encryption key present, no encryption'

     WHEN '1' THEN 'Unencrypted'

     WHEN '2' THEN 'Encryption in progress'

     WHEN '3' THEN 'Encrypted'

     WHEN '4' THEN 'Key change in progress'

     WHEN '5' THEN 'Decryption in progress'

     WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'

     ELSE 'No Status'

     END,

     percent_complete, create_date, key_algorithm, key_length,

     encryptor_thumbprint, encryptor_type

     FROM sys.dm_database_encryption_keys

  2. Disable the database encryption using the following query.

     ALTER DATABASE [DB-NAME] SET ENCRYPTION OFF

  3. Drop the database encryption key using the following query.

     Use [DB-Name]

     DROP DATABASE ENCRYPTION KEY

  4. Drop the certificate at master using the following query.

     Use Master

     DROP CERTIFICATE [Certificate-Name]

  5. Create certificate with new expiry date using the following query. Note that the date format should be in the YYYYMMDD format.

     Use Master

     CREATE CERTIFICATE [Certificate-Name] WITH SUBJECT = 'SQL Server TDE Certificate', EXPIRY_DATE = '[Date]'

  6. Create the database encryption key using the following query. Ensure that you back up the certificate key.

     Use [DB-Name]

     CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE [Certificate-Name]

  7. Enable the database encryption using the following query.

     ALTER DATABASE [DB-NAME] SET ENCRYPTION ON

• What should I do if the a message similar to the following is displayed?

  There is already a master key in the database. Please drop it before performing this statement.

  Run the following query and retry to create the master encryption key.

  DROP MASTER KEY

• How to check for the certificate information?

  • Execute the query as follows:

  use master;

  GO

  select database_name = d.name, dek.encryptor_type, cert_name = c.name from sys.dm_database_encryption_keys dek left join sys.certificates c on dek.encryptor_thumbprint = c.thumbprint inner join sys.databases d on dek.database_id = d.database_id;

  SELECT GETDATE() TodayDateTime

  GO

  SELECT name, pvt_key_encryption_type_desc, issuer_name, subject, expiry_date FROM sys.certificates WHERE name = '<Certificate Name from above query>'

  GO

• What should I do if the This command requires a database encryption scan on database ‘user_db’. However, the database has changes from previous encryption scans that are pending log backup. Take a log backup and retry the command.message is displayed?

  1. Run the following query and perform the renewal process accordingly.

     ALTER DATABASE <DatabaseName> SET RECOVERY SIMPLE

     GO

  2. Enable the encryption using the following query.

     ALTER DATABASE <DatabaseName> SET RECOVERY FULL

     GO