Take a COPY_ONLY backup of TDE protected database on Azure SQL Managed Instance
Published May 24 2019 03:18 AM 27.3K Views
Microsoft

Transparent Data Encryption (TDE) is a 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.

 

Azure SQL Database Managed Instance has automatic backups that are stored on Azure storage, fully encrypted, keeping you compliant, and providing 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 TDE protected databases that use Service managed keys in Managed Instance you cannot take a manual COPY_ONLY backup. 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 includes a process of exporting the protection keys, which is not allowed in Managed Instance.

 

NOTE:

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 does not allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporarily 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 another instance:

 

33111 Cannot find server certificate with thumbprint ...

 

 

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 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 the Log:

DBCC SHRINKFILE (  <logName>, 1)

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

7. Take the backup.

8. Restore backup and make sure it does not ask for the certificate.

 

If you follow this procedure, you should be able to successfully restore backups to another instance.

6 Comments

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...

Copper Contributor

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. 

Brass Contributor

What is needed is a supported way to export un-encrypted backups when TDE is enabled. We have a requirement to have a copy of our data at a 3rd party away from Microsoft. You cannot have all your eggs in one basket.

Copper Contributor

It is perfectly possible to take a COPY_ONLY backup of a TDE-encrypted SQL MI database and restore it elsewhere, even to SQL Server 2022. This is especially important where the destination is not in the same subscription as managed backups are extremely inflexible.

 

It just needs the key to be installed on SQL Server using one of the guides e.g. https://github.com/Rupp29/sql-docs/blob/ca95f8e9ac651d39db7da8c47c70e8ff520ef674/docs/relational-dat...

Microsoft

Yes, It is possible to Restore a Copy-Only Backup ( TDE set up with Customer managed key) from Managed Instance to On Prem SQL server 2022 ( till the end of mainstream support for SQL server 2022) , To restore databases that are encrypted at rest by using TDE, the destination instance of SQL Server must have access to the same key that's used to protect the source database through the SQL Server Connector for Azure Key Vault. For details, review Set up SQL Server TDE with AKV.

Version history
Last update:
‎Nov 09 2020 09:42 AM
Updated by: