PowerShell and CLI: Enable Transparent Data Encryption with customer-managed key from Azure Key Vault

This article walks through how to use a key from Azure Key Vault for Transparent Data Encryption (TDE) on a SQL Database or Data Warehouse. 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.

Prerequisites for PowerShell

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

Important

The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

Step 1. Assign an Azure AD identity to your server

If you have an existing server, use the following to add an Azure AD 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 an Azure AD identity during server creation:

$server = New-AzSqlServer `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-Location <RegionName> `
-ServerName <LogicalServerName> `
-ServerVersion "12.0" `
-SqlAdministratorCredentials <PSCredential> `
-AssignIdentity 

Step 2. 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

Step 3. Add the Key Vault key to the server and set the TDE Protector

Note

The combined length for the key vault name and key name cannot exceed 94 characters.

<# 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> 

<# To confirm that the TDE protector was configured as intended: #>
Get-AzSqlServerTransparentDataEncryptionProtector `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> 

Step 4. 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.

Step 5. Check the encryption state and encryption activity

Use the Get-AzSqlDatabaseTransparentDataEncryption to get the encryption state and the Get-AzSqlDatabaseTransparentDataEncryptionActivity to check the encryption progress for a database or data warehouse.

# Get the encryption state
Get-AzSqlDatabaseTransparentDataEncryption `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> `
-DatabaseName <DatabaseName> `

<# Check the encryption progress for a database or data warehouse #>
Get-AzSqlDatabaseTransparentDataEncryptionActivity `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> `
-DatabaseName <DatabaseName>  

Other 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 using the Get-AzSubscription cmdlet.

    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

Prerequisites for CLI

Step 1. Create a server with an Azure AD identity

  cli
  # create server (with identity) and database
  az sql server create --name <servername> --resource-group <rgname>  --location <location> --admin-user <user> --admin-password <password> --assign-identity
  az sql db create --name <dbname> --server <servername> --resource-group <rgname>  

Tip

Keep the "principalID" from creating the server, it is the object id used to assign key vault permissions in the next step

Step 2. Grant Key Vault permissions to the logical sql server

  cli
  # create key vault, key and grant permission
   az keyvault create --name <kvname> --resource-group <rgname> --location <location> --enable-soft-delete true
   az keyvault key create --name <keyname> --vault-name <kvname> --protection software
   az keyvault set-policy --name <kvname>  --object-id <objectid> --resource-group <rgname> --key-permissions wrapKey unwrapKey get 

Tip

Keep the key URI or keyID of the new key for the next step, for example: https://contosokeyvault.vault.azure.net/keys/Key1/1a1a2b2b3c3c4d4d5e5e6f6f7g7g8h8h

Step 3. Add the Key Vault key to the server and set the TDE Protector

 cli
 # add server key and update encryption protector
 az sql server key create --server <servername> --resource-group <rgname> --kid <keyID>
 az sql server tde-key set --server <servername> --server-key-type AzureKeyVault  --resource-group <rgname> --kid <keyID>

    

Note

The combined length for the key vault name and key name cannot exceed 94 characters.

Step 4. Turn on TDE

  cli
  # enable encryption
  az sql db tde set --database <dbname> --server <servername> --resource-group <rgname> --status Enabled 
  

Now the database or data warehouse has TDE enabled with a customer-managed encryption key in Azure Key Vault.

Step 5. Check the encryption state and encryption activity

 cli
  # get encryption scan progress
  az sql db tde list-activity --database <dbname> --server <servername> --resource-group <rgname>  

  # get whether encryption is on or off
  az sql db tde show --database <dbname> --server <servername> --resource-group <rgname> 

SQL CLI References

https://docs.microsoft.com/cli/azure/sql

https://docs.microsoft.com/cli/azure/sql/server/key

https://docs.microsoft.com/cli/azure/sql/server/tde-key

https://docs.microsoft.com/cli/azure/sql/db/tde