PowerShell and Azure CLI: Enable Transparent Data Encryption with customer-managed key from Azure Key Vault
Applies to:
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
This article walks through how to use a key from Azure Key Vault for transparent data encryption (TDE) on Azure SQL Database or Azure Synapse Analytics. To learn more about the TDE with Azure Key Vault integration - Bring Your Own Key (BYOK) Support, visit TDE with customer-managed keys in Azure Key Vault. If you are looking for Azure portal instructions on how to enable TDE with a customer-managed key from Azure Key Vault, see Create server configured with user-assigned managed identity and customer-managed TDE.
This article applies to Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (dedicated SQL pools (formerly SQL DW)). For documentation on Transparent Data Encryption for dedicated SQL pools inside Synapse workspaces, see Azure Synapse Analytics encryption.
Note
Azure SQL now supports using a RSA key stored in a Managed HSM as TDE Protector. Azure Key Vault Managed HSM is a fully managed, highly available, single-tenant, standards-compliant cloud service that enables you to safeguard cryptographic keys for your cloud applications, using FIPS 140-2 Level 3 validated HSMs. Learn more about Managed HSMs.
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Prerequisites for PowerShell
- You must have an Azure subscription and be an administrator on that subscription.
- [Recommended but Optional] Have a hardware security module (HSM) or local key store for creating a local copy of the TDE Protector key material.
- You must have Azure PowerShell installed and running.
- Create an Azure Key Vault and Key to use for TDE.
- Instructions for using a hardware security module (HSM) and Key Vault
- The key vault must have the following property to be used for TDE:
- soft-delete and purge protection
- Instructions for using a hardware security module (HSM) and Key Vault
- The key must have the following attributes to be used for TDE:
- The activation date (if set) must be a date and time in the past
- The expiration date (if set) must be a future date and time
- The key must be in the Enabled state
- Able to perform get, wrap key, unwrap key operations
- To use a Managed HSM key, follow instructions to create and activate a Managed HSM using Azure CLI
For Az module installation instructions, see Install Azure PowerShell. For specific cmdlets, see AzureRM.Sql.
For specifics on Key Vault, see PowerShell instructions from Key Vault and How to use Key Vault soft-delete with PowerShell.
Important
The PowerShell Azure Resource Manager (RM) module is still supported, but all future development is for the Az.Sql module. The AzureRM module will continue to receive bug fixes until at least December 2020. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. For more about their compatibility, see Introducing the new Azure PowerShell Az module.
Assign a Microsoft Entra identity to your server
If you have an existing server, use the following to add a Microsoft Entra identity to your server:
$server = Set-AzSqlServer -ResourceGroupName <SQLDatabaseResourceGroupName> -ServerName <LogicalServerName> -AssignIdentity
If you are creating a server, use the New-AzSqlServer cmdlet with the tag -Identity to add a Microsoft Entra identity during server creation:
$server = New-AzSqlServer -ResourceGroupName <SQLDatabaseResourceGroupName> -Location <RegionName> `
-ServerName <LogicalServerName> -ServerVersion "12.0" -SqlAdministratorCredentials <PSCredential> -AssignIdentity
Grant Key Vault permissions to your server
Use the Set-AzKeyVaultAccessPolicy cmdlet to grant your server access to the key vault before using a key from it for TDE.
Set-AzKeyVaultAccessPolicy -VaultName <KeyVaultName> `
-ObjectId $server.Identity.PrincipalId -PermissionsToKeys get, wrapKey, unwrapKey
For adding permissions to your server on a Managed HSM, add the 'Managed HSM Crypto Service Encryption User' local RBAC role to the server. This will enable the server to perform get, wrap key, unwrap key operations on the keys in the Managed HSM. Instructions for provisioning server access on Managed HSM
Add the Key Vault key to the server and set the TDE Protector
- Use the Get-AzKeyVaultKey cmdlet to retrieve the key ID from key vault
- Use the Add-AzSqlServerKeyVaultKey cmdlet to add the key from the Key Vault to the server.
- Use the Set-AzSqlServerTransparentDataEncryptionProtector cmdlet to set the key as the TDE protector for all server resources.
- Use the Get-AzSqlServerTransparentDataEncryptionProtector cmdlet to confirm that the TDE protector was configured as intended.
Note
For Managed HSM keys, use Az.Sql 2.11.1 version of PowerShell.
Note
The combined length for the key vault name and key name cannot exceed 94 characters.
Tip
An example KeyId from Key Vault: https://contosokeyvault.vault.azure.net/keys/Key1/1a1a2b2b3c3c4d4d5e5e6f6f7g7g8h8h
An example KeyId from Managed HSM:
https://contosoMHSM.managedhsm.azure.net/keys/myrsakey
# add the key from Key Vault to the server
Add-AzSqlServerKeyVaultKey -ResourceGroupName <SQLDatabaseResourceGroupName> -ServerName <LogicalServerName> -KeyId <KeyVaultKeyId>
# set the key as the TDE protector for all resources under the server
Set-AzSqlServerTransparentDataEncryptionProtector -ResourceGroupName <SQLDatabaseResourceGroupName> -ServerName <LogicalServerName> `
-Type AzureKeyVault -KeyId <KeyVaultKeyId>
# confirm the TDE protector was configured as intended
Get-AzSqlServerTransparentDataEncryptionProtector -ResourceGroupName <SQLDatabaseResourceGroupName> -ServerName <LogicalServerName>
Turn on TDE
Use the Set-AzSqlDatabaseTransparentDataEncryption cmdlet to turn on TDE.
Set-AzSqlDatabaseTransparentDataEncryption -ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> -DatabaseName <DatabaseName> -State "Enabled"
Now the database or data warehouse has TDE enabled with an encryption key in Key Vault.
Check the encryption state and encryption activity
Use the Get-AzSqlDatabaseTransparentDataEncryption to get the encryption state for a database or data warehouse.
# get the encryption state of the database
Get-AzSqlDatabaseTransparentDataEncryption -ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> -DatabaseName <DatabaseName> `
Useful PowerShell cmdlets
Use the Set-AzSqlDatabaseTransparentDataEncryption cmdlet to turn off TDE.
Set-AzSqlDatabaseTransparentDataEncryption -ServerName <LogicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName> ` -DatabaseName <DatabaseName> -State "Disabled"
Use the Get-AzSqlServerKeyVaultKey cmdlet to return the list of Key Vault keys added to the server.
# KeyId is an optional parameter, to return a specific key version Get-AzSqlServerKeyVaultKey -ServerName <LogicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName>
Use the Remove-AzSqlServerKeyVaultKey to remove a Key Vault key from the server.
# the key set as the TDE Protector cannot be removed Remove-AzSqlServerKeyVaultKey -KeyId <KeyVaultKeyId> -ServerName <LogicalServerName> -ResourceGroupName <SQLDatabaseResourceGroupName>
Troubleshooting
Check the following if an issue occurs:
If the key vault cannot be found, make sure you're in the right subscription.
Get-AzSubscription -SubscriptionId <SubscriptionId>
- If the new key cannot be added to the server, or the new key cannot be updated as the TDE Protector, check the following:
- The key should not have an expiration date
- The key must have the get, wrap key, and unwrap key operations enabled.
Next steps
- Learn how to rotate the TDE Protector of a server to comply with security requirements: Rotate the Transparent Data Encryption protector Using PowerShell.
- In case of a security risk, learn how to remove a potentially compromised TDE Protector: Remove a potentially compromised key.