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.