SQL Database Transparent Data Encryption with Azure Key Vault configuration checklist

Azure SQL Database and Data Warehouse offer encryption-at-rest by providing Transparent Data Encryption (TDE) for all data written to disk, including databases, log files and backups. This protects data in case of unauthorized access to hardware. TDE provides a TDE Protector that is used to encrypt the Database Encryption Key (DEK), which in turn is used to encrypt the data. The TDE protector is by default managed by the service in a fully transparent fashion, rotated every 90 days and maintained in archive for access to backups. Optionally management of the TDE Protector can be assumed by the customer if more control is desired. This requires storing the TDE protector in a customer-owned Azure Key Vault. If this option is chosen, it is important to fully understand all TDE implications and carefully plan for ongoing key management.

Overview of TDE with customer managed keys and Azure Key Vault integration:

In this scenario, customers must maintain Azure Key Vault, control SQL Database permissions to Azure Key Vault and maintain access to all TDE Protectors to open or restore databases or backups and enable all other operations that require database access to the TDE Protector. The following checklist will help to systematically plan all key management related duties in Azure Key Vault. In addition, we list the most important setup considerations and configuration requirements that must be followed to configure TDE with customer managed keys in Azure Key Vault.

General guidelines:

• Ensure Azure Key Vault and Azure SQL Database are going to be in the same tenant. Cross-tenant key vault and server interactions are not supported.
• Decide which subscriptions will be used for the required resources. Moving the server across subscriptions later requires a new setup of TDE with BYOKs.
• When configuring TDE with BYOK, it is important to consider the load placed on the key vault by repeated wrap/unwrap operations. For example, since all databases associated with a logical server 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. Based on our experience and documented key vault service limits, we recommend associating at most 500 Standard or 200 Premium databases with one Azure Key Vault in a single subscription to ensure consistently high availability when accessing the TDE protector in the vault.
• Recommended: Keep a copy of the TDE protector on premises. This requires a HSM device to create a TDE Protector locally and a key escrow system to store a local copy of the TDE Protector.

Guidelines for configuring Azure Key Vault:

• Use a key vault with soft-delete enabled (required) to protect from data loss in case of accidental key or key vault deletion scenarios:
• Soft deleted resources are retained for 90 days unless they are recovered or purged.
• The recover and purge actions have their own permissions associated in a key vault access policy.
• Grant the SQL server access to the key vault using its Azure Active Directory (Azure AD) Identity. When using the Portal UI, the Azure AD identity will be automatically created and the key vault access permissions will be granted to the server. Using PowerShell, these steps must be completed individually in the correct order and need to be verified. See Configure TDE with BYOK for detailed step-by-step instructions when using PowerShell. Note: The server will lose access to the key vault if the Azure AD Identity is accidentally deleted or the server’s permissions are revoked using the key vault’s access policy.
• Enable auditing and reporting in Azure Key Vault on all encryption keys: Key Vault provides logs that are easy to inject into other security information and event management (SIEM) tools. Operations Management Suite (OMS) Log Analytics is one example of a service that is already integrated.
• To ensure high-availability of encrypted databases, configure one logical server with two Azure Key Vaults in different regions.
• For High Availability of a single SQL database, consider configuring two key vaults:

• Use the Backup-AzureKeyVaultKey cmdlet to retrieve the key in encrypted format and then use the Restore-AzureKeyVaultKey cmdlet and specify a key vault in the second region.
• For Geo-replicated databases, the following AKV configuration is required:

• One primary database with a key vault in region and one secondary database with a key vault in region.
• One secondary is required, up to four secondaries are supported.
• Secondaries of secondaries (chaining) is not supported.
• Note: when assigning the server identity, assign the identity for the secondary first, and for the primary second.

Guidelines for configuring TDE Protectors (asymmetric key) stored in Azure Key Vault:

• Create your encryption key locally on a local HSM device. Ensure this is an asymmetric, RSA 2048 key so it is storable in Azure Key Vault. Larger key sizes are currently not supported by Azure SQL Database.
• Escrow the key in a key escrow system.
• Import the encryption key file (.pfx, .byok, or .backup) to Azure Key Vault.
• (Note: For testing purposes, it is possible to create a key with Azure Key Vault, however this key cannot be escrowed, because the private key can never leave the key vault. A key used to encrypt production data should always be escrowed, as the loss of the key (accidental deletion in key vault, expiration etc.) will result in permanent data loss.)
• Use a key without an expiration date and never set an expiration date on a key already in use: once the key expires, the encrypted databases lose access to their TDE Protector and are dropped within 24 hours.
• Ensure the key is enabled and has permissions to perform get, wrap key, and unwrap key operations.
• Create an Azure Key Vault key backup before using the key in Azure Key Vault for the first time. Learn more about the Backup-AzureKeyVaultKey command.
• Create a new backup whenever any changes are made to the key (for example, add ACLs, add tags, add key attributes).
• Keep previous versions of the key in the key vault when rotating keys, so that databases can still access their virtual log files that continue to be encrypted with the original keys. In addition, when the TDE Protector is changed for a database, old backups of the database are not updated to use the latest TDE Protector. Key rotations can be performed following the instructions at Rotate the Transparent Data Encryption Protector Using PowerShell.
• Keep all previously used keys in Azure Key Vault after changing back to service-managed keys. This will ensure database backups can be restored with the TDE protectors stored in Azure Key Vault. TDE protectors will have to be maintained in Azure Key Vault until all needed backups have been created while using service-managed keys.
• Make recoverable backup copies of these keys using Backup-AzureKeyVaultKey.
• To remove a potentially compromised key during a security incident without the risk of data loss, follow the steps at Remove a potentially compromised key.