--- title: "PowerShell and CLI: Enable SQL TDE using your own Azure Key Vault key | Microsoft Docs" description: "Learn how to configure an Azure SQL Database and Data Warehouse to start using Transparent Data Encryption (TDE) for encryption-at-rest using PowerShell or CLI." keywords: documentationcenter: author: "aliceku" manager: craigg editor: ms.prod: ms.reviewer: "" ms.suite: sql ms.prod_service: sql-database, sql-data-warehouse ms.service: "sql-database" ms.tgt_pltfrm: ms.devlang: "azurecli, powershell" ms.topic: conceptual ms.date: "06/28/2018" ms.author: "aliceku" monikerRange: "= azuresqldb-current || = azure-sqldw-latest || = sqlallproducts-allversions" --- # PowerShell and CLI: Enable Transparent Data Encryption using your own key from Azure Key Vault [!INCLUDE[appliesto-xx-asdb-asdw-xxx-md](../../../includes/appliesto-xx-asdb-asdw-xxx-md.md)] This how-to guide 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 Bring Your Own Key (BYOK) Support, visit [TDE Bring Your Own Key to Azure SQL](transparent-data-encryption-byok-azure-sql.md). ## 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 version 4.2.0 or newer installed and running. - Create an Azure Key Vault and Key to use for TDE. - [PowerShell instructions from Key Vault](https://docs.microsoft.com/azure/key-vault/key-vault-get-started) - [Instructions for using a hardware security module (HSM) and Key Vault](https://docs.microsoft.com/azure/key-vault/key-vault-get-started#a-idhsmaif-you-want-to-use-a-hardware-security-module-hsm) - The key vault must have the following property to be used for TDE: - [soft-delete](https://docs.microsoft.com/azure/key-vault/key-vault-ovw-soft-delete) - [How to use Key Vault soft-delete with PowerShell](https://docs.microsoft.com/azure/key-vault/key-vault-soft-delete-powershell) - The key must have the following attributes to be used for TDE: - No expiration date - Not disabled - Able to perform *get*, *wrap key*, *unwrap key* operations ## 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: ```powershell $server = Set-AzureRmSqlServer ` -ResourceGroupName ` -ServerName ` -AssignIdentity ``` If you are creating a server, use the [New-AzureRmSqlServer](/powershell/module/azurerm.sql/new-azurermsqlserver) cmdlet with the tag -Identity to add an Azure AD identity during server creation: ```powershell $server = New-AzureRmSqlServer ` -ResourceGroupName ` -Location ` -ServerName ` -ServerVersion "12.0" ` -SqlAdministratorCredentials ` -AssignIdentity ``` ## Step 2. Grant Key Vault permissions to your server Use the [Set-AzureRmKeyVaultAccessPolicy](/powershell/module/azurerm.keyvault/set-azurermkeyvaultaccesspolicy) cmdlet to grant your server access to the key vault before using a key from it for TDE. ```powershell Set-AzureRmKeyVaultAccessPolicy ` -VaultName ` -ObjectId $server.Identity.PrincipalId ` -PermissionsToKeys get, wrapKey, unwrapKey ``` ## Step 3. Add the Key Vault key to the server and set the TDE Protector - Use the [Add-AzureRmSqlServerKeyVaultKey](/powershell/module/azurerm.sql/add-azurermsqlserverkeyvaultkey) cmdlet to add the key from the Key Vault to the server. - Use the [Set-AzureRmSqlServerTransparentDataEncryptionProtector](/powershell/module/azurerm.sql/set-azurermsqlservertransparentdataencryptionprotector) cmdlet to set the key as the TDE protector for all server resources. - Use the [Get-AzureRmSqlServerTransparentDataEncryptionProtector](/powershell/module/azurerm.sql/get-azurermsqlservertransparentdataencryptionprotector) cmdlet to confirm that the TDE protector was configured as intended. > [!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 > ```powershell <# Add the key from Key Vault to the server #> Add-AzureRmSqlServerKeyVaultKey ` -ResourceGroupName ` -ServerName ` -KeyId <# Set the key as the TDE protector for all resources under the server #> Set-AzureRmSqlServerTransparentDataEncryptionProtector ` -ResourceGroupName ` -ServerName ` -Type AzureKeyVault ` -KeyId <# To confirm that the TDE protector was configured as intended: #> Get-AzureRmSqlServerTransparentDataEncryptionProtector ` -ResourceGroupName ` -ServerName ``` ## Step 4. Turn on TDE Use the [Set-AzureRMSqlDatabaseTransparentDataEncryption](/powershell/module/azurerm.sql/set-azurermsqldatabasetransparentdataencryption) cmdlet to turn on TDE. ```powershell Set-AzureRMSqlDatabaseTransparentDataEncryption ` -ResourceGroupName ` -ServerName ` -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-AzureRMSqlDatabaseTransparentDataEncryption](/powershell/module/azurerm.sql/get-azurermsqldatabasetransparentdataencryption) to get the encryption state and the [Get-AzureRMSqlDatabaseTransparentDataEncryptionActivity](/powershell/module/azurerm.sql/get-azurermsqldatabasetransparentdataencryptionactivity) to check the encryption progress for a database or data warehouse. ```powershell # Get the encryption state Get-AzureRMSqlDatabaseTransparentDataEncryption ` -ResourceGroupName ` -ServerName ` -DatabaseName ` <# Check the encryption progress for a database or data warehouse #> Get-AzureRMSqlDatabaseTransparentDataEncryptionActivity ` -ResourceGroupName ` -ServerName ` -DatabaseName ``` ## Other useful PowerShell cmdlets - Use the [Set-AzureRMSqlDatabaseTransparentDataEncryption](/powershell/module/azurerm.sql/set-azurermsqldatabasetransparentdataencryption) cmdlet to turn off TDE. ```powershell Set-AzureRMSqlDatabaseTransparentDataEncryption ` -ServerName ` -ResourceGroupName ` -DatabaseName ` -State "Disabled” ``` - Use the [Get-AzureRmSqlServerKeyVaultKey](/powershell/module/azurerm.sql/get-azurermsqlserverkeyvaultkey) cmdlet to return the list of Key Vault keys added to the server. ```powershell <# KeyId is an optional parameter, to return a specific key version #> Get-AzureRmSqlServerKeyVaultKey ` -ServerName ` -ResourceGroupName ``` - Use the [Remove-AzureRmSqlServerKeyVaultKey](/powershell/module/azurerm.sql/remove-azurermsqlserverkeyvaultkey) to remove a Key Vault key from the server. ```powershell <# The key set as the TDE Protector cannot be removed. #> Remove-AzureRmSqlServerKeyVaultKey ` -KeyId ` -ServerName ` -ResourceGroupName ``` ## 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-AzureRmSubscription](/powershell/module/azurerm.profile/get-azurermsubscription) cmdlet. ```powershell Get-AzureRmSubscription ` -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](transparent-data-encryption-byok-azure-sql-key-rotation.md). - In case of a security risk, learn how to remove a potentially compromised TDE Protector: [Remove a potentially compromised key](transparent-data-encryption-byok-azure-sql-remove-tde-protector.md). ## Prerequisites for CLI - 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. - Command-Line Interface version 2.0 or later. To install the latest version and connect to your Azure subscription, see [Install and Configure the Azure Cross-Platform Command-Line Interface 2.0](https://docs.microsoft.com/cli/azure/install-azure-cli?view=azure-cli-latest). - Create an Azure Key Vault and Key to use for TDE. - [Manage Key Vault using CLI 2.0](https://docs.microsoft.com/azure/key-vault/key-vault-manage-with-cli2) - [Instructions for using a hardware security module (HSM) and Key Vault](https://docs.microsoft.com/azure/key-vault/key-vault-get-started#a-idhsmaif-you-want-to-use-a-hardware-security-module-hsm) - The key vault must have the following property to be used for TDE: - [soft-delete](https://docs.microsoft.com/azure/key-vault/key-vault-ovw-soft-delete) - [How to use Key Vault soft-delete with CLI](https://docs.microsoft.com/azure/key-vault/key-vault-soft-delete-cli) - The key must have the following attributes to be used for TDE: - No expiration date - Not disabled - Able to perform *get*, *wrap key*, *unwrap key* operations ## Step 1. Create a server and assign an Azure AD identity to your server cli # create server (with identity) and database az sql server create -n "ServerName" -g "ResourceGroupName" -l "westus" -u "cloudsa" -p "YourFavoritePassWord99@34" -I az sql db create -n "DatabaseName" -g "ResourceGroupName" -s "ServerName" ## Step 2. Grant Key Vault permissions to your server cli # create key vault, key and grant permission az keyvault create -n "VaultName" -g "ResourceGroupName" az keyvault key create -n myKey -p software --vault-name "VaultName" az keyvault set-policy -n "VaultName" --object-id "ServerIdentityObjectId" -g "ResourceGroupName" --key-permissions wrapKey unwrapKey get list ## 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 -g "ResourceGroupName" -s "ServerName" -t "AzureKeyVault" -u "FullVersionedKeyUri az sql server tde-key update -g "ResourceGroupName" -s "ServerName" -t AzureKeyVault -u "FullVersionedKeyUri" > [!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 > ## Step 4. Turn on TDE cli # enable encryption az sql db tde create -n "DatabaseName" -g "ResourceGroupName" -s "ServerName" --status 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 cli # get encryption scan progress az sql db tde show-activity -n "DatabaseName" -g "ResourceGroupName" -s "ServerName" # get whether encryption is on or off az sql db tde show-configuration -n "DatabaseName" -g "ResourceGroupName" -s "ServerName" ## SQL CLI References https://docs.microsoft.com/cli/azure/sql?view=azure-cli-latest https://docs.microsoft.com/cli/azure/sql/server/key?view=azure-cli-latest https://docs.microsoft.com/cli/azure/sql/server/tde-key?view=azure-cli-latest https://docs.microsoft.com/cli/azure/sql/db/tde?view=azure-cli-latest