TDE on Azure SQL Managed Instance

Question

TDE is not enabled on MI by default. I had manually enabled TDE by setting the encryption to true in the db properties. Encryption of the DB took around 20 mins and below is the output from the sys.dm_database_encryption_keys table.

1. How do we verify if TDE is enabled in Managed instance or not? As neither Azure provides access to automated backups of MI nor i can manually take the backup when the TDE is turned on in MI.

2. Would like to access the automated backups (7day backup feature) and verify the files can be restored or not. Provide the procedure.

3. Hoping the backups are encrypted as TDE is enabled on my DB, so how can these be decrypted as Azure is not providing any keys to decrypt.

Answer 1

Use the following query to find encrypted database:

select name, is_encrypted from sys.databases

You cannot directly access backup files; however, you can run point-in-time restore to verify that any previous backup can be restored as a new database (you have restore button on the portal for each database or you can use PowerShell see - https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/06/28/point-in-time-restore-of-a-database-on-azure-sql-managed-instance-using-azurerm-sql-powershell-library/)

Point-in-time restore is the only way to restore encrypted automatic backups and Azure automatically de-crypts the backup on restore. You cannot restore backup from MI on SQL Server because MI is always higher version than SQL server. Therefore, even if you de-crpt the backup file you cannot use it on SQL Server, and if you want to restore it on Managed Instance, MI will find the key.