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)).
In this guide, we go through the steps to configure geo replication and backup restore on an Azure SQL Database. The Azure SQL Database is configured 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. Both the Azure Key Vault and logical server for Azure SQL are in the same Microsoft Entra tenant for this guide, but they can be in different tenants.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
After the database is created or restore, the Transparent Data Encryption menu in the Azure portal will show the new database with the same settings as the source database, but may have keys missing. In all cases where a new database is created from a source database, the number of keys displayed for a target database in the Azure portal Additional Database Keys list could be less than the number of keys displayed for a source database. This is because the number of displayed keys depends on individual feature requirements used to create a target database. To list all keys available for a newly create database, use the available APIs in View the database level customer-managed key settings on an Azure SQL Database.
Create an Azure SQL Database with database level customer-managed keys as a secondary or copy
Use the following instructions or commands to create a secondary replica or copy target of an Azure SQL Database configured with database level customer-managed keys. A user-assigned managed identity is required for setting up a customer-managed key for transparent data encryption during the database creation phase.
Create a database copy that has database level customer-managed keys
To create a database in Azure SQL Database as a copy with database level customer-managed keys, follow these steps:
Go to the Azure portal and navigate to the Azure SQL Database configured with database level customer-managed keys. Access the Transparent Data Encryption tab of the Data Encryption menu and check the list of current keys in use by the database.
Create a copy of the database by selecting Copy from the Overview menu of the database.
The Create SQL Database - Copy database menu appears. Use a different server for this database, but the same settings as the database you're trying to copy. In the Transparent Data Encryption Key Management section, select Configure transparent data encryption.
When the Transparent Data Encryption menu appears, review the CMK settings for this copy database. The settings and keys should be populated with the same identity and keys used in the source database.
Select Apply to continue and then select Review + create, and Create to create the copy database.
Create a secondary replica that has database level customer-managed keys
Go to the Azure portal and navigate to the Azure SQL Database configured with database level customer-managed keys. Access the Transparent Data Encryption menu and check the list of current keys in use by the database.
Under Data management settings for the database, select Replicas. Select Create replica to create a secondary replica of the database.
The Create SQL Database - Geo Replica menu appears. Use a secondary server for this database, but the same settings as the database you're trying to replicate. In the Transparent Data Encryption Key Management section, select Configure transparent data encryption.
When the Transparent Data Encryption menu appears, review the CMK settings for this database replica. The settings and keys should be populated with the same identity and keys used in the primary database.
Select Apply to continue and then select Review + create, and Create to create the copy database.
For information on installing the current release of Azure CLI, see Install the Azure CLI article.
Prepopulate the list of current keys in use by the primary database using the expand-keys parameter with current as the keys-filter.
az sql db show --name $databaseName --resource-group $resourceGroup --server $serverName --expand-keys --keys-filter current
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Create a new database as a secondary and provide the prepopulated list of keys obtained from the source database and the above identity (and federated client ID if configuring cross tenant access).
# Create a secondary replica with Active Geo Replication with the same name as the primary database
az sql db replica create -g $resourceGroup -s $serverName -n $databaseName --partner-server $secondaryServer --partner-database $secondaryDatabase --partner-resource-group $secondaryResourceGroup -i --encryption-protector $encryptionProtector --user-assigned-identity-id $umi --keys $keys
Important
$keys is a space separated list of keys retrieved from the source database.
To create a copy of the database, az sql db copy can be used with the same parameters.
# Create a copy of a database configured with database level customer-managed keys
az sql db copy -g $resourceGroup -s $serverName -n $databaseName --dest-name $secondaryDatabase -i --encryption-protector $encryptionProtector --user-assigned-identity-id $umi --keys $keys
Prepopulate the list of current keys in use by the primary database using the command Get-AzSqlDatabase and the -ExpandKeyList and -KeysFilter "current" parameters. Exclude -KeysFilter if you wish to retrieve all the keys.
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Create a new database as a secondary using the command New-AzSqlDatabaseSecondary and provide the prepopulated list of keys obtained from the source database and the above identity (and federated client ID if configuring cross tenant access) in the API call using the -KeyList, -AssignIdentity, -UserAssignedIdentityId, -EncryptionProtector (and if necessary, -FederatedClientId) parameters.
# Create a secondary replica with Active Geo Replication with the same name as the primary database
$database = Get-AzSqlDatabase -ResourceGroupName <ResourceGroupName> -ServerName <ServerName> -DatabaseName <DatabaseName> -ExpandKeyList -KeysFilter "current"
$database | New-AzSqlDatabaseSecondary -PartnerResourceGroupName <SecondaryResourceGroupName> -PartnerServerName <SecondaryServerName> -AllowConnections "All" -AssignIdentity -UserAssignedIdentityId <UserAssignedIdentityId> -EncryptionProtector <CustomerManagedKeyId> -FederatedClientId <FederatedClientId>
-KeyList $database.Keys.Keys
To create a copy of the database, New-AzSqlDatabaseCopy can be used with the same parameters.
Here's an example of an ARM template that creates a secondary replica and copy of an Azure SQL Database configured with a user-assigned managed identity and customer-managed TDE at the database level.
Use a Custom deployment in the Azure portal, and Build your own template in the editor. Next, Save the configuration once you pasted in the example.
Prepopulate the list of current keys in use by the primary database using the following REST API request:
GET https://management.chinacloudapi.cn/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}?api-version=2022-08-01-preview&$expand=keys($filter=pointInTime('current'))
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Create a new database as a secondary and provide the prepopulated list of keys obtained from the source database and the above identity (and federated client ID if configuring cross tenant access) in the ARM template as the keys_to_add parameter.
Restore an Azure SQL Database with database level customer-managed keys
This section walks you through the steps to restore an Azure SQL Database configured with database level customer-managed keys. A user-assigned managed identity is required for setting up a customer-managed key for transparent data encryption during the database creation phase.
Point in time restore
The following section describes how to restore a database configured with customer-managed keys at the database level to a given point in time. To learn more about backup recovery for SQL Database, see Recover a database in SQL Database.
Go to the Azure portal and navigate to the Azure SQL Database configured with database level customer-managed keys that you want to restore.
To restore the database to a point in time, select Restore from the Overview menu of the database.
The Create SQL Database - Restore database menu appears. Fill in the source and database details needed. In the Transparent Data Encryption Key Management section, select Configure transparent data encryption.
When the Transparent Data Encryption menu appears, review the CMK settings for the database. The settings and keys should be populated with the same identity and keys used in the database that you're trying to restore.
Select Apply to continue and then select Review + create, and Create to create the copy database.
For information on installing the current release of Azure CLI, see Install the Azure CLI article.
Prepopulate the list of keys used by the primary database using the expand-keys parameter with your restore point in time as the keys-filter.
az sql db show --name $databaseName --resource-group $resourceGroup --server $serverName --expand-keys --keys-filter $timestamp
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Create a new database as a restore target and provide the prepopulated list of keys obtained from the source database and the above identity (and federated client ID if configuring cross tenant access).
# Create a restored database
az sql db restore --dest-name $destName --name $databaseName --resource-group $resourceGroup --server $serverName --subscription $subscriptionId --time $timestamp -i --encryption-protector $encryptionProtector --user-assigned-identity-id $umi --keys $keys
Important
$keys is a space separated list of keys retrieved from the source database.
Prepopulate the list of keys used by the primary database using the command Get-AzSqlDatabase and the -ExpandKeyList and -KeysFilter "2023-01-01" parameters (2023-01-01 is an example of the point in time you wish to restore the database to). Exclude -KeysFilter if you wish to retrieve all the keys.
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Use the command Restore-AzSqlDatabase with the -FromPointInTimeBackup parameter and provide the prepopulated list of keys obtained from the above steps and the above identity (and federated client ID if configuring cross tenant access) in the API call using the -KeyList, -AssignIdentity, -UserAssignedIdentityId, -EncryptionProtector (and if necessary, -FederatedClientId) parameters.
The following section describes how to restore a deleted database that was configured with customer-managed keys at the database level. To learn more about backup recovery for SQL Database, see Recover a database in SQL Database.
Go to the Azure portal and navigate to the logical server for the deleted database that you want to restore. Under Data management, select Deleted databases.
Select the deleted database that you want to restore.
The Create SQL Database - Restore database menu appears. Fill in the source and database details needed. In the Transparent Data Encryption Key Management section, select Configure transparent data encryption.
When the Transparent Data Encryption menu appears, configure the User-Assigned Managed Identity, Customer-Managed Key, and Additional Database Keys section for your database.
Select Apply to continue and then select Review + create, and Create to create the copy database.
For information on installing the current release of Azure CLI, see Install the Azure CLI article.
Prepopulate the list of keys used by the dropped database using the expand-keys parameter. It's recommended to pass all the keys that the source database was using. You can also attempt a restore with the keys provided at deletion time by using the keys-filter parameter.
az sql db show-deleted --name $databaseName --resource-group $resourceGroup --server $serverName --restorable-dropped-database-id "databaseName,133201549661600000" --expand-keys
Important
restorable-dropped-database-id can be retrieved by listing all restorable dropped databases in the server and is of the format databaseName,deletedTimestamp.
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Create a new database as a restore target and provide the prepopulated list of keys obtained from the deleted source database and the above identity (and federated client ID if configuring cross tenant access).
# Create a restored database
az sql db restore --dest-name $destName --name $databaseName --resource-group $resourceGroup --server $serverName --subscription $subscriptionId --time $timestamp -i --encryption-protector $encryptionProtector --user-assigned-identity-id $umi --keys $keys --deleted-time "2023-02-06T11:02:46.160000+00:00"
Important
$keys is a space separated list of keys retrieved from the source database.
Prepopulate the list of keys used by the primary database using the command Get-AzSqlDeletedDatabaseBackup and the -ExpandKeyList parameter. It's recommended to pass all the keys that the source database was using. You can also attempt a restore with the keys provided at deletion time by using the -KeysFilter parameter.
DatabaseId can be retrieved by listing all restorable dropped databases in the server and is of the format databaseName,deletedTimestamp.
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Use the command Restore-AzSqlDatabase with the -FromDeletedDatabaseBackup parameter and provide the prepopulated list of keys obtained from the above steps and the above identity (and federated client ID if configuring cross tenant access) in the API call using the -KeyList, -AssignIdentity, -UserAssignedIdentityId, -EncryptionProtector (and if necessary, -FederatedClientId) parameters.
The following section describes how to restore a geo-replicated backup of database that is configured with customer-managed keys at the database level. To learn more about backup recovery for SQL Database, see Recover a database in SQL Database.
Go to the Azure portal and navigate to the logical server where you want to restore the database.
In the Overview menu, select Create database.
The Create SQL Database menu appears. Fill Basic and Networking tabs for your new database. In Additional settings, select Backup for the Use existing data section, and select a geo-replicated backup.
Go to the Security tab. In the Transparent Data Encryption Key Management section, select Configure transparent data encryption.
When the Transparent Data Encryption menu appears, select Database level Customer-Managed Key (CMK). The User-Assigned Managed Identity, Customer-Managed Key, and Additional Database Keys must match the source database that you want to restore. Make sure the user-assigned managed identity has access to the key vault that contains the customer-managed key that was used in the backup.
Select Apply to continue and then select Review + create, and Create to create the backup database.
For information on installing the current release of Azure CLI, see Install the Azure CLI article.
Prepopulate the list of keys used by the geo backup of the database configured with customer-managed keys at the database level using the expand-keys parameter.
az sql db geo-backup --database-name $databaseName --g $resourceGroup --server $serverName --expand-keys
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Create a new database as a geo restore target and provide the prepopulated list of keys obtained from the deleted source database and the above identity (and federated client ID if configuring cross tenant access).
# Create a geo restored database
az sql db geo-backup restore --geo-backup-id "/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/recoverableDatabases/{databaseName}" --dest-database $destName --resource-group $resourceGroup --dest-server $destServerName -i --encryption-protector $encryptionProtector --user-assigned-identity-id $umi --keys $keys
Important
$keys is a space separated list of keys retrieved from the source database.
Prepopulate the list of keys used by the primary database using the command Get-AzSqlDatabaseGeoBackup and the -ExpandKeyList to retrieve all the keys.
DatabaseId can be retrieved by listing all restorable dropped databases in the server and is of the format databaseName,deletedTimestamp.
Select the user-assigned managed identity (and federated client ID if configuring cross tenant access).
Use the command Restore-AzSqlDatabase with the -FromGeoBackup parameter and provide the prepopulated list of keys obtained from the above steps and the above identity (and federated client ID if configuring cross tenant access) in the API call using the -KeyList, -AssignIdentity, -UserAssignedIdentityId, -EncryptionProtector (and if necessary, -FederatedClientId) parameters.
Long term retention (LTR) backups don't provide the list of keys used by the backup. To restore an LTR backup, all the keys used by the source database must be passed to the LTR restore target.
Automatic key rotation option for copied or restored databases
Newly copied or restored databases can be configured to automatically rotate the customer-managed key used for transparent data encryption. For information on how to enable automatic key rotation in the Azure portal or using APIs, see Automatic key rotation at the database level.
Next steps
Check the following documentation on various database level CMK operations:
Azure SQL Database
