Image by Thought Catalog

Post Details

  • ALIF Consulting

Transparent data encryption for SQL Database

About Alif : Alif empowers Microsoft MSP-CSP partners to provide exceptional IT services to their clients to ensure that the partners reduce their costs and focus on their business. We provide white-labelled managed services for technologies like Microsoft Azure, Microsoft 365, Microsoft Dynamics 365, Microsoft Security, SharePoint, Power Platform, SQL, Azure DevOps and a lot more. Our headquarter is in Pune, India whereas we work with over 50 partners across the globe that trust us with their client delivery.


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 of 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 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 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, customer is responsible for and in a full control of a 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 encrypted database inaccessible.

  • Central management of keys in AKV.

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

How customer managed TDE works


In order for the Azure SQL server to use 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 administrator can also enable logging of key vault audit events, so they can be audited later.

When 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. Key vault returns the encrypted DEK, which is then stored in the user database.

When needed, 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 are already supported today. Assigning user-assigned managed identity to the server is now in public preview.

For TDE with 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 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, assign 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 key vault is revoked or sufficient permissions are not provided, the database will move to Inaccessible state

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

  • To switch the server from user-assigned to system-assigned managed identity for key vault access, provide the system-assigned managed identity with the required key vault permissions, then remove all user-assigned managed identities from the server.


1 view0 comments

Recent Posts

See All