Home
Microsoft

Transparent Data Encryption (TDE) is security feature in Azure SQL Database (Single Database and Managed Instance) that transparently encrypts data pages when they are exchanged between memory and underlying storage.

 

Managed Instance has automatic backups that are stored on Azure storage, fully encrypted, keep you compliant, and provide most of the functionalities that you would need. Managed Instance also enables you to take your own COPY_ONLY backups that have limited usability compared to built-in automatic backups, but in some cases they might be useful (for example to keep backups that will not expire after backup retention period).

 

However, for the TDE protected databases that use Service managed keys in Managed Instance you cannot take the manual COPY_ONLY backups. COPY_ONLY backups might enable a person with high privilege to take your data, and violate your security policy, SQL Server allows you to take your own backups of TDE protected databases but this included a proces of exporting the protection keys, which is not allowed in Managed Instance.

 

We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev) or Geo-restore functionalities to move your database. These automatic backups can be kept up to 35 days. These built-in automatic backups are secure and enables you to be fully compliant. In this scenario COPY_ONLY backups are only in some specific cases. As an alternative you can use the TDE Protector stored in your own Azure Key Vault, Azure’s cloud-based external key management system. If you put the key in the Azure Key Vault you can take the backups that can be decrypted after restore with your own key. However, if you delete the key, your backups cannot be decrypted anymore. 

Strict TDE protection don't allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporary disable TDE, take a backup, and then enable TDE again.

 

In some cases, you might use built-in cross-instance restore to create a copy of the database on another instance and then turn-off the TDE there to make your original instance compliant.

 

However, even in this case you would need to ensure that there are no encrypted pages in the database and wait for decryption to finish before you take a backup. If you backup the database before decryption finishes, you might get the following error when you try to restore the backup on other instance:

 

33111 Cannot find server certificate with thumbprint ...

 

NOTE: The recommended way to backup and restore databases in Managed Instance is using built-in automatic backups and cross-instance point-in-time restore. However, if you need to use the manual backups, here is the procedure that you would need to follow:

 

1. Check if the db is encrypted with TDE:

Select * from sys.dm_database_encryption_keys

2. If the db is encrypted, alter the db to turn off encryption. Make sure there is no active transaction when performing this operation.

Alter database <dbName> set encryption Off

3. Run checkpoint on the db

Checkpoint

4. Drop the database encryption key (DEK) 

DROP DATABASE ENCRYPTION KEY

5. Truncate Log

 

DBCC SHRINKFILE (  <logName>, 1)

6. Run select * from sys.dm_db_log_info and this should not show any active VLF that is encrypted by thumbprint

7. Take the backup

8. Restore backup and make sure it doesn't ask for the certificate.

 

If you follow this procedure, you would be able to successfully restore backups to other instance.

3 Comments
R A
MVP

Thanks for the information, but I must say that I really don't like the idea of disable TDE each time I need to backup the database manually (out of the built-in automatic backups). Moreover, this procedure means that we need to disconnect all users and it can be long procedure with high use of CPU for the decryption...

Occasional Visitor

Agree with the previous commenter. You're suggesting disabling TDE for backup/restore purposes ? This would be a major violation of strict company IT security policies. I suggest we need a transparent backup/restore process that maintains high security.

Microsoft

Thanks for the comments and you are right.

I have updated the article clarifying that we are recommending to use automatic backups, point-in-time restore, cross-instance restore, and geo-restore for backups, which keeps you compliant and provide most of the functionalities that you need.