Skip to content

transparent-data-encryption-byok-azure-sql-configure.md

Latest commit

 

History

History
269 lines (203 loc) · 12.3 KB

transparent-data-encryption-byok-azure-sql-configure.md

File metadata and controls

269 lines (203 loc) · 12.3 KB
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

[!INCLUDEappliesto-xx-asdb-asdw-xxx-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.

Prerequisites for PowerShell

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-AzureRmSqlServer `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> `
-AssignIdentity

If you are creating a server, use the New-AzureRmSqlServer cmdlet with the tag -Identity to add an Azure AD identity during server creation:

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

Step 2. Grant Key Vault permissions to your server

Use the Set-AzureRmKeyVaultAccessPolicy cmdlet to grant your server access to the key vault before using a key from it for TDE.

Set-AzureRmKeyVaultAccessPolicy  `
-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.

Tip

An example KeyId from Key Vault: https://contosokeyvault.vault.azure.net/keys/Key1/1a1a2b2b3c3c4d4d5e5e6f6f7g7g8h8h

<# Add the key from Key Vault to the server #>
Add-AzureRmSqlServerKeyVaultKey `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> `
-KeyId <KeyVaultKeyId>

<# Set the key as the TDE protector for all resources under the server #>
Set-AzureRmSqlServerTransparentDataEncryptionProtector `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-ServerName <LogicalServerName> `
-Type AzureKeyVault `
-KeyId <KeyVaultKeyId> 

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

Step 4. Turn on TDE

Use the Set-AzureRMSqlDatabaseTransparentDataEncryption cmdlet to turn on TDE.

Set-AzureRMSqlDatabaseTransparentDataEncryption `
-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-AzureRMSqlDatabaseTransparentDataEncryption to get the encryption state and the Get-AzureRMSqlDatabaseTransparentDataEncryptionActivity to check the encryption progress for a database or data warehouse.

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

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

Other useful PowerShell cmdlets

  • Use the Set-AzureRMSqlDatabaseTransparentDataEncryption cmdlet to turn off TDE.

    Set-AzureRMSqlDatabaseTransparentDataEncryption `
-ServerName <LogicalServerName> `
-ResourceGroupName <SQLDatabaseResourceGroupName> `
-DatabaseName <DatabaseName> `
-State "Disabled”

  • Use the Get-AzureRmSqlServerKeyVaultKey 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-AzureRmSqlServerKeyVaultKey `
-ServerName <LogicalServerName> `
-ResourceGroupName <SQLDatabaseResourceGroupName>

  • Use the Remove-AzureRmSqlServerKeyVaultKey to remove a Key Vault key from the server.

    <# The key set as the TDE Protector cannot be removed. #>
Remove-AzureRmSqlServerKeyVaultKey `
-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-AzureRmSubscription cmdlet.

    Get-AzureRmSubscription `
-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 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