SQLMI TDE with: Service-Managed Certificate .VS. Azure Key Vault Key

Basic Concepts: Transparent Data Encryption (TDE) performs encryption and decryption of the database, associated backups, and transaction log files at rest by using a symmetric key called the Database Encryption Key (DEK). The DEK is stored on the boot page of a database and protected by the TDE Protector which is either a built-in service-managed certificate or an asymmetric key stored in Azure Key Vault. The TDE Protector is set at the instance level and it is inherited by all encrypted databases on that instance. TDE performs real-time I/O encryption and decryption of the data at the page level.

	TDE with Service-Managed certificate	TDE with Azure Key Vault key (in preview)
Key Management overhead	It’s the default setting for TDE at instance level The built-in server certificate is unique for each server The encryption algorithm used is AES 256. Microsoft automatically rotates the server certificates in compliance with the internal security policy and the root key is protected by a Microsoft internal secret store.	The TDE Protector (the Key Vault key) can be: A. generated by the Key Vault B. Transferred to the Key Vault from an on premises HSM device (recommended way, asymmetric, RSA 2048 or RSA HSM 2048 key) The TDE Protector never leaves the key vault. SQL MI needs to be granted permissions to the customer-owned key vault to decrypt and encrypt the DEK. If permissions of the SQL MI to the key vault are revoked, all the encrypted databases will be inaccessible. The following key management tasks are controlled by users: A. Key rotations and keep previous versions so older database backups can be restored. B. Key vault permissions C. Key backups/restorations. It’s recommended to create a new backup whenever any changes are made to the key and test restoring key backups periodically D. Keep a copy of the TDE protector on premise (the requires an HSM device to create a TDE Protector locally and escrow it in a key escrow system) E. Keep all previously used keys in Azure Key Vault after changing back to service-managed keys. This ensures database backups can be restored with the TDE protectors stored in Azure Key Vault. TDE protectors created with Azure Key Vault have to be maintained until all stored backups have been created with service-managed keys. F. Remove a potentially compromised key during a security incident without the risk of data loss The database will self-heal and become online automatically if the access to the customer-managed TDE Protector is restored within 48 hours. If the database is inaccessible due to an intermittent networking outage, there is no action required and the databases will come back online automatically.
Azure Key vault Management overhead	N/A	Enable soft-delete and purge protection to protect from data loss in case of accidental key – or key vault – deletion Set a resource Lock on the key vault Enable firewalls and virtual networks with Azure key vault but must allow trusted Microsoft services to bypass this firewall (this is true even with the Microsoft.KeyVault service endpoint being configured on SQL MI subnet) Enable auditing/reporting on all encryption keys through Azure Key Vault logging To ensure high availability of encrypted databases, configure each SQL MI with two Azure Key Vaults that reside in two paired regions Azure Key Vault service limits need to be evaluated when planning Key vaults and keys. https://docs.microsoft.com/en-us/azure/key-vault/key-vault-service-limits
Azure Storage requirement	N/A	An Azure storage account is needed to enable logging for Azure key vaults to monitor how and when they are accessed. A new Blob container named insights-logs-auditevent is automatically created in the storage account The logging information is available 10 minutes (at most) after the key vault operation The standard Azure access control methods should be configured to secure the logs Delete old key vault log records that are no longer needed for anything.
Encryption Key Performance	The server certificate is not shared by multiple SQL Mis so there is no direct key performance concern	Since all databases associated with a SQL MI use the same TDE protector, a failover of that server will trigger as many key operations against the vault as there are databases in the server. it is important to consider the load placed on the key vault by repeated wrap/unwrap operations. In a single subscription, to ensure consistently high availability when accessing the TDE Protector in the Azure Key Vault, Microsoft recommends the following configuration: A. Associates no more than 500 Standard / General Purpose databases with one Azure Key Vault B. Associates no more than 200 Premium / Business Critical databases with one Azure Key Vault
Moving TDE protected databases	SQL MI doesn’t support Copy_only backup on a TED enable database. Two solutions to take custom database backups A. Temporarily disable TDE, take a backup, and then re-enable TDE B. Use automatic backups, built-in cross-instance-point-in-time restore to create a copy of the database on another SQL MI and then turn-off the TDE and take a database backup there. (this is the recommended way)	SQL MI supports taking a copy_only backup on TDE enabled databases Once a database is encrypted with TDE using a key from Key Vault, any generated backups are also encrypted with the same TDE Protector. The backup can be restored on another SQL MI as long as it’s configured to use the same TDE Protector.
Database Disaster Recovery	In a geo-replication relationship, both the primary and geo-secondary database are protected by the primary database's parent server key. Microsoft also seamlessly moves and manages the keys as needed for geo-replication and restores.	Create and maintain two Azure Key Vaults with identical contents in the same regions that will be used for SQL MI Failover Group. Keep both key vaults in sync, which means they must contain the same copies of TDE Protectors after key rotation. Maintain old versions of keys used for log files or backups. TDE Protectors must maintain the same key properties. The key vaults must maintain the same access permissions for SQL MIs
SQL Managed Instance configuration	It’s a built-in feature, enabled by default, no configuration needed	SQL MI must be assigned an Azure AD identity so that the Key Vault can be configured to grant key permission to it. The minimum permissions to AKV keys include: get, wrapKey and unwrapKey.