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

In Azure SQL, server-level Transparent Data Encryption (TDE) provides centralized encryption across multiple databases within a server or managed instance. By default, Azure SQL uses a Service Managed Key (SMK) for TDE, but we can also choose to deploy a Customer Managed Key (CMK) for better control.

Here’s a simple overview of how these options work in Azure SQL:

• Service Managed Key (SMK):

By default, Transparent Data Encryption (TDE) in Azure SQL employs a Service Managed Key (SMK) that is stored and controlled by Microsoft. With an SMK, the master key is automatically managed by Microsoft and stored in Azure’s secure infrastructure.

• Customer Managed Key (CMK):

With a CMK, the master key is stored in Azure Key Vault, giving us more control over access, rotation, and key lifecycle management.

Key Concepts in Server-Level TDE for Azure SQL #

Before we begin the implementation, it’s important to review a few essential concepts.

• Database Encryption Key (DEK): Each database within an Azure SQL instance has its own DEK, which is used to encrypt data at rest.
• Master Key (TDE Protector): In Azure SQL, the master key, also known as the TDE Protector, can be either a Service-Managed Key (SMK) or a Customer-Managed Key (CMK). This master key is responsible for encrypting the DEKs of all databases within the SQL Server or managed instance.

After reviewing the key concepts to better understand our tasks, let’s begin the guide.

First, we will set up a customer-managed key (CMK) in Azure Key Vault and assign the necessary permissions. Next, we will configure Azure SQL to utilize this key for Transparent Data Encryption (TDE). We will also enable automatic key rotation to enhance the security of our setup. By the end of this article, you will have a fully configured TDE setup that utilizes Azure Key Vault for secure and centralized encryption management.

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"
$ServerKeyName="srv-master-tde-key"

Setting Up a TDE Encryption Key in Azure Key Vault #

This command creates an RSA key named $ServerKeyName in the key vault specified by $KeyVaultName, with operations restricted to wrapKey and unwrapKey. This key will be stored in software-protected storage, and its primary purpose will be to function as a customer-managed key (CMK) in Transparent Data Encryption (TDE) for Azure SQL Server.

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

Setting Up Managed Identity and Key Vault Permissions for TDE #

These commands enable an Azure SQL Server’s managed identity and grant it the necessary permissions to access and manage keys in Azure Key Vault. This setup is important for Transparent Data Encryption (TDE) with a Customer-Managed Key (CMK), allowing the SQL Server to perform encryption and decryption operations required for data protection.

$identity = (Get-AzSqlServer `
    -ResourceGroupName $ResourceGroupName `
    -ServerName $ServerName).Identity.PrincipalId

$KeyVaultId = (Get-AzKeyVault `
    -ResourceGroupName $ResourceGroupName `
    -VaultName $KeyVaultName).ResourceId

Set-AzSqlServer `
	-ResourceGroupName $ResourceGroupName `
	-ServerName $ServerName `
	-AssignIdentity

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

Applying the TDE Protector Key at Server Level #

These commands configure Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault on an Azure SQL Server. This setup allows SQL Server to use a specific key stored in Key Vault as the master key to encrypt the data encryption keys (DEKs) for all databases on the instance. Additionally, it enables automatic key rotation, ensuring that the master key is periodically updated according to the Key Vault rotation policy.

$ServerKeyId = (Get-AzKeyVaultKey `
    -VaultName $KeyVaultName `
    -Name $ServerKeyName).Id

Set-AzSqlServerTransparentDataEncryptionProtector `
	-ResourceGroupName $ResourceGroupName `
	-ServerName $ServerName `
	-Type AzureKeyVault `
	-AutoRotationEnabled $true `
	-KeyId $ServerKeyId `
	-Force

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

Get-AzSqlServerTransparentDataEncryptionProtector `
	-ResourceGroupName $ResourceGroupName `
	-ServerName $ServerName

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 $ServerKeyName |
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 $ServerKeyName

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

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

Reverting to Azure-Managed TDE Keys (Optional) #

This command is used to revert the Customer Managed Key (CMK) configuration back to the default key management settings. By switching to a Service Managed Key (SMK), Azure SQL Server will automatically handle the encryption key lifecycle, including key rotation and storage.

Set-AzSqlServerTransparentDataEncryptionProtector `
	-ResourceGroupName $ResourceGroupName `
	-ServerName $ServerName `
	-Type ServiceManaged

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.