Skip to content

Latest commit

 

History

History
253 lines (177 loc) · 12.9 KB

transparent-data-encryption-byok-configure.md

File metadata and controls

253 lines (177 loc) · 12.9 KB
title titleSuffix description services ms.service ms.subservice ms.custom ms.devlang ms.topic author ms.author ms.reviewer ms.date
Enable SQL TDE with Azure Key Vault
Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics
Learn how to configure an Azure SQL Database and Azure Synapse Analytics to start using Transparent Data Encryption (TDE) for encryption-at-rest using PowerShell or the Azure CLI.
sql-database
sql-db-mi
security
seo-lt-2019 sqldbrb=1, devx-track-azurecli
how-to
jaszymas
jaszymas
vanto
03/12/2019

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

[!INCLUDEappliesto-sqldb-sqlmi-asa]

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.

Note

Azure SQL now supports using a RSA key stored in a Managed HSM as TDE Protector. This feature is in public preview. 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.

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.
  • 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
  • (In Preview) To use a Managed HSM key, follow instructions to create and activate a Managed HSM using Azure CLI

PowerShell

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 an Azure Active Directory (Azure AD) identity to your server

If you have an existing server, use the following to add an Azure Active Directory (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

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

Note

(In Preview) 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 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>

The Azure CLI

To install the required version of the Azure CLI (version 2.0 or later) and connect to your Azure subscription, see Install and Configure the Azure Cross-Platform Command-Line Interface 2.0.

For specifics on Key Vault, see Manage Key Vault using the CLI 2.0 and How to use Key Vault soft-delete with the CLI.

Assign an Azure AD identity to your server

# 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

Grant Key Vault permissions to your server

# 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

Add the Key Vault key to the server and set the TDE Protector

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

Turn on TDE

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

Check the encryption state and encryption activity

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

Useful PowerShell cmdlets

PowerShell

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

The Azure CLI

Troubleshooting

Check the following if an issue occurs:

  • If the key vault cannot be found, make sure you're in the right subscription.

    PowerShell

    Get-AzSubscription -SubscriptionId <SubscriptionId>

    The Azure CLI

    az account show - s <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