Identity and key management for TDE with database level customer-managed keys

  • Article

Applies to: Azure SQL Database

Note

  • Database Level TDE CMK is available for Azure SQL Database (all SQL Database editions). It is not available for Azure SQL Managed Instance, SQL Server on-premises, Azure VMs, and Azure Synapse Analytics (dedicated SQL pools (formerly SQL DW)).
  • The same guide can be applied to configure database level customer-managed keys in the same tenant by excluding the federated client ID parameter. For more information on database level customer-managed keys, see Transparent data encryption (TDE) with customer-managed keys at the database level.

In this guide, we go through the steps to create, update, and retrieve an Azure SQL Database with transparent data encryption (TDE) and customer-managed keys (CMK) at the database level, utilizing a user-assigned managed identity to access Azure Key Vault. The Azure Key Vault is in a different Microsoft Entra tenant than the Azure SQL Database. For more information, see Cross-tenant customer-managed keys with transparent data encryption.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Prerequisites

  • This guide assumes that you have two Microsoft Entra tenants.
    • The first consists of the Azure SQL Database resource, a multi-tenant Microsoft Entra application, and a user-assigned managed identity.
    • The second tenant houses the Azure Key Vault.
  • For comprehensive instructions on setting up cross-tenant CMK and the RBAC permissions necessary for configuring Microsoft Entra applications and Azure Key Vault, refer to one of the following guides:
  • The Azure CLI version 2.52.0 or higher.
  • Az PowerShell module version 10.3.0 or higher.
  • The RBAC permissions necessary for database level CMK are the same permissions that are required for server level CMK. Specifically, the same RBAC permissions that are applicable when using Azure Key Vault, managed identities, and cross-tenant CMK for TDE at the server level are applicable at the database level. For more information on key management and access policy, see Key management.

Required resources on the first tenant

For the purpose of this tutorial, we'll assume the first tenant belongs to an independent software vendor (ISV), and the second tenant is from their client. For more information on this scenario, see Cross-tenant customer-managed keys with transparent data encryption.

Before we can configure TDE for Azure SQL Database with a cross-tenant CMK, we need to have a multi-tenant Microsoft Entra application that is configured with a user-assigned managed identity assigned as a federated identity credential for the application. Follow one of the guides in the Prerequisites.

  1. On the first tenant where you want to create the Azure SQL Database, create and configure a multi-tenant Microsoft Entra application.

  2. Create a user-assigned managed identity.

  3. Configure the user-assigned managed identity as a federated identity credential for the multi-tenant application.

  4. Record the application name and application ID. This can be found in the Azure portal > Microsoft Entra ID > Enterprise applications and search for the created application.

Required resources on the second tenant

Note

Azure AD and MSOnline PowerShell modules are deprecated as of March 30, 2024. To learn more, read the deprecation update. After this date, support for these modules are limited to migration assistance to Microsoft Graph PowerShell SDK and security fixes. The deprecated modules will continue to function through March, 30 2025.

We recommend migrating to Microsoft Graph PowerShell to interact with Microsoft Entra ID (formerly Azure AD). For common migration questions, refer to the Migration FAQ. Note: Versions 1.0.x of MSOnline may experience disruption after June 30, 2024.

  1. On the second tenant where the Azure Key Vault resides, create a service principal (application) using the application ID from the registered application from the first tenant. Here's some examples of how to register the multi-tenant application. Replace <TenantID> and <ApplicationID> with the client Tenant ID from Microsoft Entra ID and Application ID from the multi-tenant application, respectively:

    • PowerShell:

      Connect-AzureAD -TenantID <TenantID> -Environment AzureChinaCloud
New-AzADServicePrincipal  -ApplicationId <ApplicationID>

    • The Azure CLI:

      az login --tenant <TenantID>
az ad sp create --id <ApplicationID>

  2. Go to the Azure portal > Microsoft Entra ID > Enterprise applications and search for the application that was created.

  3. Create an Azure Key Vault if you don't have one, and create a key

  4. Create or set the access policy.

    1. Select the Get, Wrap Key, Unwrap Key permissions under Key permissions when creating the access policy.
    2. Select the multi-tenant application created in the first step in the Principal option when creating the access policy.

    Screenshot of the access policy menu of a key vault in the Azure portal.

  5. Once the access policy and key has been created, Retrieve the key from Key Vault and record the Key Identifier.

Create a new Azure SQL Database with database level customer-managed keys

The following are examples for creating a database on Azure SQL Database with a user-assigned managed identity, and how to set a cross-tenant customer managed key at the database level. The user-assigned managed identity is required for setting up a customer-managed key for transparent data encryption during the database creation phase.

  1. Browse to the Select SQL deployment option page in the Azure portal.

  2. If you aren't already signed in to Azure portal, sign in when prompted.

  3. Under SQL databases, leave Resource type set to Single database, and select Create.

  4. On the Basics tab of the Create SQL Database form, under Project details, select the desired Azure Subscription, Resource group, and Server for your database. Then, use a unique name for your Database name. If you haven't created a logical server for Azure SQL Database, see Create server configured with TDE with cross-tenant customer-managed key (CMK) for reference.

  5. When you get to the Security tab, select Configure transparent data encryption.

    Screenshot of the Azure portal and the Security menu when creating an Azure SQL Database.

  6. On the Transparent data encryption menu, select Database level customer managed key (CMK).

    Screenshot of the Azure portal transparent data encryption menu.

  7. For User-Assigned Managed Identity, select Configure to enable a Database identity and Add a user assigned managed identity to the resource if a desired identity isn't list in the Identity menu. Then select Apply.

    Screenshot of the Azure portal Identity menu.

    Note

    You can configure the Federated client identity here if you are configuring cross-tenant CMK for TDE.

  8. On the Transparent data encryption menu, select Change key. Select the desired Subscription, Key vault, Key, and Version for the customer-managed key to be used for TDE. Select the Select button. After you have selected a key, you can also add additional database keys as needed using the Azure Key vault URI (object identifier) in the Transparent data encryption menu.

    Automatic key rotation can also be enabled on the database level by using the Auto-rotate key checkbox in the Transparent data encryption menu.

    Screenshot of the transparent data encryption menu in the Azure portal referencing adding additional keys.

  9. Select Apply to continue creating the database.

  10. Select Review + create at the bottom of the page

  11. On the Review + create page, after reviewing, select Create.

Note

The database creation will fail if the user-assigned managed identity doesn't have the right permissions enabled on the key vault. The user-assigned managed identity will need the Get, wrapKey, and unwrapKey permissions on the key vault. For more information, see Managed identities for transparent data encryption with customer-managed key.

Update an existing Azure SQL Database with database level customer-managed keys

This following are examples of updating an existing database on Azure SQL Database with a user-assigned managed identity, and how to set a cross-tenant customer managed key at the database level. The user-assigned managed identity is required for setting up a customer-managed key for transparent data encryption during the database creation phase.

  1. In the Azure portal, navigate to the SQL database resource that you want to update with a database level customer-managed key.

  2. Under Security, select Identity. Add a User assigned managed identity for this database, and then select Save

  3. Now go to the Data Encryption menu under Security for your database. Select Database level customer managed key (CMK). The Database Identity for the database should already be Enabled as you have configured the identity in the last step.

  4. Select Change key. Select the desired Subscription, Key vault, Key, and Version for the customer-managed key to be used for TDE. Select the Select button. After you have selected a key, you can also add additional database keys as needed using the Azure Key vault URI (object identifier) in the Data Encryption menu.

    Select the Auto-rotate key checkbox if you want to enable automatic key rotation on the database level.

    Screenshot of the Azure portal transparent data encryption menu when updating an existing database.

  5. Select Save.

View the database level customer-managed key settings on an Azure SQL Database

The following are examples of retrieving the database level customer-managed keys for a database. The ARM resource Microsoft.Sql/servers/databases by default only shows the TDE protector and managed identity configured on the database. To expand the full list of keys use the parameter, -ExpandKeyList. Additionally, filters such as -KeysFilter "current" and a point in time value (for example, 2023-01-01) can be used to retrieve the current keys used and keys used in the past at a specific point in time. These filters are only supported for individual database queries and not for server level queries.

To view the database level customer-managed keys in the Azure portal, go to the Data Encryption menu of the SQL database resource.

List all keys in a logical server

To fetch the list of all the keys (and not just the primary protector) used by each database under the server, it must be individually queried with the key filters. The following is an example of a PowerShell query to list each key under the logical server.

Use the Get-AzSqlDatabase cmdlet.

$dbs = Get-AzSqlDatabase -resourceGroupName <ResourceGroupName> -ServerName <ServerName>
foreach ($db in $dbs)
{
Get-AzSqlDatabase -DatabaseName $db.DatabaseName -ServerName $db.ServerName -ResourceGroupName $db.ResourceGroupName -ExpandKeyList
}

Revalidate the database level customer-managed key on an Azure SQL Database

In case of an inaccessible TDE protector as described in Transparent Data Encryption (TDE) with CMK, once the key access has been corrected, a revalidate key operation can be used to make the database accessible. See the following instructions or commands for examples.

Using the Azure portal, find your SQL database resource. Once you have selected your SQL database resource, go to the Transparent Data Encryption tab of the Data Encryption menu under the Security settings. If the database has lost access to the Azure Key Vault, a Revalidate key button will appear, and you'll have the option to revalidate the existing key by selecting Retry existing key, or another key by selecting Select backup key.

Revert the database level customer-managed key on an Azure SQL Database

A database configured with database level CMK can be reverted to server level encryption if the server is configured with a service-managed key using the following commands.

To revert the database level customer-managed key setting to server level encryption key in the Azure portal, go to the Transparent Data Encryption tab of the Data Encryption menu of the SQL database resource. Select Server level encryption key and select Save to save the settings.

Note

In order to use the Server level encryption key setting for individual databases, the logical server for the Azure SQL Database must be configured to use Service-managed key for TDE.

Next steps

Check the following documentation on various database level CMK operations: