Azure SQL - Managing Database-Level Transparent Data Encryption (TDE) Keys with Azure PowerShell

In my previous article, we explored how to set up server-level Transparent Data Encryption (TDE) in Azure SQL. This method uses a single encryption key to secure multiple databases on a server or managed instance, providing consistent protection across all databases.

In this article, we’ll focus on database-level TDE in Azure SQL Database. This approach allows administrators to manage encryption settings for each database separately, offering more control over data security. By enabling database-level TDE, you can customize encryption to meet specific compliance needs or performance requirements for each database, making it a flexible and robust security solution.

In Azure SQL, TDE protects data at rest by using a master key, which can be managed either by Microsoft as a Service-Managed Key (SMK) or by the customer as a Customer-Managed Key (CMK) in Azure Key Vault.

• Service-Managed Key (SMK): By default, TDE uses a Service-Managed Key stored and managed by Microsoft in Azure’s secure infrastructure. This SMK is automatically rotated and managed, providing an easy-to-use encryption solution.

• Customer-Managed Key (CMK): For more control, we can configure TDE with a Customer-Managed Key (CMK) stored in Azure Key Vault. A CMK allows us to manage access, enforce rotation policies, and control the lifecycle of the encryption key according to our security requirements.

Key Concepts in Database-Level TDE for Azure SQL #

Before we begin, it’s important to understand a few essential components involved in database-level TDE:

• Database Encryption Key (DEK): Each database within an Azure SQL Database instance has its own DEK, which is used to encrypt data at rest.

• Master Key (TDE Protector): Known as the TDE Protector, this master key is responsible for encrypting the DEKs of individual databases. It can be configured either as an SMK (managed by Microsoft) or as a CMK (stored in Azure Key Vault), depending on our security requirements.

After reviewing the main concepts, we are ready to set up database-level TDE in Azure SQL.

First, we will set up a Customer-Managed Key (CMK) in Azure Key Vault and assign the necessary permissions to allow Azure SQL Database to access this key. Next, we will configure the database to use this CMK for Transparent Data Encryption (TDE), which provides encryption control at the database level. Finally, we will enable automatic key rotation to maintain a strong security posture over time.

Prerequisites #

For this guide, it is assumed that you already have the following resources and permissions configured:

1. Azure SQL Server – An existing Azure SQL Server instance where Transparent Data Encryption (TDE) will be configured at the server level.
2. Azure SQL Database – At least one database within the Azure SQL Server instance to apply TDE.
3. Azure Key Vault – An Azure Key Vault instance with Role-Based Access Control (RBAC) enabled for managing access.

Additionally, ensure you have the following permissions:

Azure PowerShell Workaround #

First, you need to ensure the Az.Sql module is installed on your computer and imported into your PowerShell session. To do that, you should use the following commands.

Install-Module -Name Az.Sql -Force 
Import-Module Az.Sql 

Once you’ve imported the module, you’re ready to go. The easiest way to get started is to log in interactively at the command line.

Connect-AzAccount

Set the variables #

Here we define the characteristics and properties of the resources we will use.

$ResourceGroupName="RG-DEMO-NE"
$ServerName="db-server"
$databaseName="db-demo"
$KeyVaultName="KV-DEMO-NE"
$DbKeyName="db-master-tde-key"
$UserAssignedIdentityName = "id-db-tde-demo" 

Setting Up a TDE Encryption Key in Azure Key Vault #

This command creates an RSA key named $DbKeyName in the key vault specified by $KeyVaultName, with operations restricted to wrapKey and unwrapKey. The key is stored in software-protected storage and will serve as a customer-managed key (CMK) for Transparent Data Encryption (TDE) at the database level in Azure SQL Database.

Add-AzKeyVaultKey `
   -VaultName $KeyVaultName `
   -Name $DbKeyName `
   -KeyOps wrapKey,unwrapKey `
   -KeyType RSA `
   -Destination "Software"

Setting Up Managed Identity and Key Vault Permissions for TDE #

These commands assign a user-assigned managed identity to an Azure SQL Database and grant it the necessary permissions to access Azure Key Vault. This setup enables Transparent Data Encryption (TDE) with a Customer-Managed Key (CMK) at the database level by establishing a secure connection between the Azure SQL Database, its managed identity, and Azure Key Vault. By leveraging the Key Vault Crypto Service Encryption User role, the database gains the required permissions to access the customer-managed key stored in Azure Key Vault, allowing it to perform encryption and decryption operations essential for safeguarding data at rest.

$DatabaseUserAssignedIdentity = Get-AzUserAssignedIdentity `
    -ResourceGroupName $ResourceGroupName `
    -Name $UserAssignedIdentityName

Set-AzSqlDatabase `
   -ResourceGroupName $ResourceGroupName `
   -ServerName $ServerName `
   -DatabaseName $DatabaseName `
   -AssignIdentity `
   -UserAssignedIdentityId $DatabaseUserAssignedIdentity.Id

New-AzRoleAssignment `
   -ObjectId $DatabaseUserAssignedIdentity.PrincipalId `
   -RoleDefinitionName "Key Vault Crypto Service Encryption User" `
   -Scope $KeyVaultId

Applying the TDE Protector Key at Database Level #

These commands configure Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault on an Azure SQL Database. This setup allows the specified database to use a key stored in Azure Key Vault as the master key (TDE protector) to encrypt the database’s data encryption key (DEK). Furthermore, the configuration allows for automatic key rotation, ensuring that the master key is periodically updated following the rotation policy we will define in the next step of our Azure Key Vault setup.

$DbKeyId = (Get-AzKeyVaultKey `
   -VaultName $KeyVaultName `
   -Name $DbKeyName).Id

Set-AzSqlDatabase `
   -ResourceGroupName $ResourceGroupName `
   -ServerName $ServerName `
   -DatabaseName $DatabaseName `
   -EncryptionProtector $KeyId `
   -EncryptionProtectorAutoRotation

Check the configuration applied at the Database level #

We use this command to verify that the TDE protector in SQL Server is configured correctly, as defined in the previous command.

Get-AzSqlDatabase `
   -ResourceGroupName $ResourceGroupName `
   -ServerName $ServerName `
   -DatabaseName $DatabaseName

Configuring TDE Key Rotation and Verifying Rotation Policy #

This command ensures that the specified key in Azure Key Vault is automatically rotated. In this example, the key is set to rotate either every 358 days after its creation or 30 days before its expiration, whichever comes first. Additionally, the policy defines a maximum lifetime of 2 years, after which the key will expire if not rotated. Regularly refreshing encryption keys in this way is essential for maintaining secure encryption practices.

Get-AzKeyVaultKey `
   -VaultName $KeyVaultName `
   -Name $DbKeyName | 
Set-AzKeyVaultKeyRotationPolicy `
   -ExpiresIn "P2Y" `
   -KeyRotationLifetimeAction @{
         Action = "Rotate"; 
         TimeAfterCreate = "P358D";
         TimeBeforeExpiry = "P30D"
   }

This command helps verify that the key rotation policy is set based on the parameters defined in the previous command.

Get-AzKeyVaultKeyRotationPolicy `
   -VaultName $KeyVaultName `
   -Name $DbKeyName
   -IncludeVersions

Review key version history in Azure Key Vault #

This step is optional but it allows us to audit and track the life cycle of a key.

Get-AzKeyVaultKey `
   -VaultName $KeyVaultName `
   -Name $DbKeyName `
   -IncludeVersions

Reverting to Azure-Managed TDE Keys (Optional) #

This command reverts the Transparent Data Encryption (TDE) protector for the database named $DatabaseName in the Azure SQL Server specified by $ServerName.It resets the database-level TDE configuration to inherit the encryption protector from the server.

Invoke-AzSqlDatabaseTransparentDataEncryptionProtectorRevert `
    -ResourceGroupName $ResourceGroupName `
    -ServerName $ServerName `
    -DatabaseName $DatabaseName 

Important #

• Database-level encryption protector can only be reverted to the server level encryption when the server is configured with a Microsoft Managed Key (SMK).
• The database-level encryption protector is removed, and the database inherits the server-level encryption protector.
• This operation does not change the encryption state of the database itself, but changes which key is used as the protector.

References and useful links #

• Official Microsoft Documentation.

Thank you for taking the time to read my post. I sincerely hope that you find it helpful.