Transparent data encryption for SQL Database

Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application. By default, TDE is enabled for all newly deployed Azure SQL Databases and must be manually enabled for older databases of Azure SQL Database. For Azure SQL Managed Instance, TDE is enabled at the instance level and newly created databases. TDE must be manually enabled for Azure Synapse Analytics.

Move a transparent data encryption-protected database

You don't need to decrypt databases for operations within Azure. The TDE settings on the source database or primary database are transparently inherited on the target. Operations that are included involve:

• Geo-restore

• Self-service point-in-time restore

• Restoration of a deleted database

• Active geo-replication

• Creation of a database copy

• Restore the backup file to Azure SQL Managed Instance

When you export a TDE-protected database, the exported content of the database isn't encrypted. This exported content is stored in unencrypted BACPAC files. Be sure to protect the BACPAC files appropriately and enable TDE after the import of the new database is finished.

Transparent Data Encryption with customer-managed key

Azure SQL transparent data encryption (TDE) with customer-managed key enables the Bring Your Own Key (BYOK) scenario for data protection at rest and allows organizations to implement separation of duties in the management of keys and data. With customer-managed TDE, the customer is responsible for and in full control of key lifecycle management (key creation, upload, rotation, deletion), key usage permissions, and auditing of operations on keys.

In this scenario, the key used for encryption of the Database Encryption Key (DEK), called TDE protector, is a customer-managed asymmetric key stored in a customer-owned and customer-managed Azure Key Vault (AKV), a cloud-based external key management system.

Benefits of the customer-managed TDE

Customer-managed TDE provides the following benefits to the customer:

• Full and granular control over usage and management of the TDE protector.

• Transparency of the TDE protector usage.

• Ability to implement separation of duties in the management of keys and data within the organization.

• Key Vault administrator can revoke key access permissions to make the encrypted database inaccessible.

• Central management of keys in AKV.

• Greater trust from your end customers since AKV is designed so that Microsoft can't see or extract encryption keys.

How customer-managed TDE works

In order for the Azure SQL server to use the TDE protector stored in AKV for encryption of the DEK, the key vault administrator needs to give the following access rights to the server using its unique Azure Active Directory (Azure AD) identity:

• get - for retrieving the public part and properties of the key in the Key Vault

• wrapKey - to be able to protect (encrypt) DEK

• unwrapKey - to be able to unprotect (decrypt) DEK

Key vault administrators can also enable logging of key vault audit events so they can be audited later.

When the server is configured to use a TDE protector from AKV, the server sends the DEK of each TDE-enabled database to the key vault for encryption. The key vault returns the encrypted DEK, which is then stored in the user database.

When needed, the server sends protected DEK to the key vault for decryption.

Auditors can use Azure Monitor to review key vault AuditEvent logs if logging is enabled.

Managed identities for transparent data encryption with BYOK

Managed identities in Azure Active Directory (Azure AD) provide Azure services with an automatically managed identity in Azure AD. This identity can be used to authenticate to any service that supports Azure AD authentication, such as Azure Key Vault, without any credentials in the code. For more information, see Managed Identity Types in Azure.

Managed Identities can be of two types:

• System-assigned

• User-assigned

Enabling system-assigned managed identity for Azure SQL logical servers and Managed Instances is already supported today. Assigning user-assigned managed identity to the server is now in public preview.

For TDE with a customer-managed key (CMK) in Azure SQL, a managed identity on the server is used for providing access rights to the server on the key vault. For instance, the system-assigned managed identity of the server should be provided with key vault permissions prior to enabling TDE with CMK on the server.

Benefits of using UMI for customer-managed TDE

• Enables the ability to pre-authorize key vault access for Azure SQL logical servers or managed instances by creating a user-assigned managed identity and granting it access to the key vault, even before the server or database has been created

• Allows creation of an Azure SQL logical server with TDE and CMK-enabled

• Enables the same user-assigned managed identity to be assigned to multiple servers, eliminating the need to individually turn on system-assigned managed identity for each Azure SQL logical server or managed instance and providing it access to key vault

• Provides the capability to enforce CMK at server or database creation time with an available built-in Azure policy

Considerations while using UMI for customer-managed TDE

By default, TDE in Azure SQL uses the primary user

Assigned managed identity set on the server for key vault access. If no user-assigned identities have been assigned to the server, then the system-assigned managed identity of the server is used for key vault access.

When using the system

Assigned managed identity for TDE with CMK; no user-assigned managed identities should be assigned to the server.

When using a user

Assigned managed identity for TDE with CMK, assigned the identity to the server and set it as the primary identity for the server

The primary user

assigned managed identity requires continuous key vault access (get, wrapKey, unwrapKeypermissions). If the identity's access to the key vault is revoked or sufficient permissions are not provided, the database will move to Inaccessible state

If the primary user

An assigned managed identity is being updated to a different user-assigned managed identity; the new identity must be given the required permissions to the key vault prior to updating the primary

To switch the server from a user

assigned to system-assigned managed identity for key vault access, provided the system-assigned managed identity with the required key vault permissions, and then removed all user-assigned managed identities from the server.