lacework-global-621
Encrypt SQL server's Transparent Data Encryption (TDE) protector with Customer-managed key (Automated)
Profile Applicability
• Level 2
Description
Transparent Data Encryption (TDE) with Customer-managed key support provides increased transparency and control over the TDE Protector, increased security with an HSM-backed external service, and promotion of separation of duties.
With TDE, a symmetric key (called the database encryption key) stored in the database or data warehouse distribution encrypts the data at rest. To protect this Data Encryption Key (DEK) in the past, you could only use a certificate that the Azure SQL Service managed. Now, with Customer-managed key support for TDE, an asymmetric key stored in the Azure Key Vault can protect the DEK.
The Azure Key Vault is a highly available and scalable cloud-based key store which offers central key management, leverages Federal Information Processing Standards (FIPS) 140-2 Level 2 validated hardware security modules (HSMs), and allows separation of management of keys and data for additional security.
Based on business needs or criticality of data/databases hosted on a SQL server, best practices recommend encrypting the TDE protector by a key managed by the data owner (Customer-managed key).
Rationale
Customer-managed key support for Transparent Data Encryption (TDE) allows user control of TDE encryption keys and restricts who can access them and when. Azure Key Vault, Azure’s cloud-based external key management system, is the first key management service where TDE has integrated support for Customer-managed keys. With Customer-managed key support, the database encryption key is protected by an asymmetric key stored in the Key Vault. The asymmetric key is set at the server level and inherited by all databases under that server.
Impact
Once TDE protector is encrypted with a Customer-managed key, it transfers entire responsibility of respective key management on to you, and hence you should be more careful about doing any operations on the particular key in order to keep data from corresponding SQL server and Databases hosted accessible.
When deploying Customer Managed Keys, it is prudent to ensure that you also deploy an automated toolset for managing these keys (this should include discovery and key rotation), and Keys should be stored in an HSM or hardware backed keystore, such as Azure Key Vault.
As far as toolsets go, check with your cryptographic key provider, as they may well provide one as an add-on to their service.
Audit
From Azure Portal
- Go to
SQL servers
. For the desired server instance - Click On
Transparent data encryption
- Ensure that
Customer-managed key
is selected - Ensure
Make selected key the default TDE protector
is checked
From Azure CLI
az account get-access-token --query "{subscripton:subscription,accessToken:accessToken}" --out tsv | xargs -L1 bash -c 'curl -X GET -H "Authorization: Bearer $1" -H "Content-Type: application/json" https://management.azure.com/subscriptions/$0/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/encryptionProtector?api-version=2015-05-01-preview'
Ensure the output of the command contains properties
kind
set to azurekeyvault
serverKeyType
set to AzureKeyVault
uri
is not null
Remediation
From Azure Console
- Go to SQL servers. For the desired server instance:
- Click On Transparent data encryption.
- Set Transparent data encryption to Customer-managed key.
- Browse through your key vaults to Select an existing key or create a new key in the Azure Key Vault.
- Check Make selected key the default TDE protector.
From Azure CLI
Use the below command to encrypt SQL server's TDE protector with a Customer-managed key:
az sql server tde-key set --resource-group <resourceName> --server <dbServerName> --server-key-type {AzureKeyVault} --kid <keyIdentifier>
References
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-byok-azure-sql
https://azure.microsoft.com/en-in/blog/preview-sql-transparent-data-encryption-tde-with-bring-your-own-key-support/
https://winterdom.com/2017/09/07/azure-sql-tde-protector-keyvault
https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-data-protection#dp-5-use-customer-managed-key-option-in-data-at-rest-encryption-when-required
https://docs.microsoft.com/en-us/azure/key-vault/general/basic-concepts
https://docs.microsoft.com/en-us/cli/azure/sql/server/tde-key?view=azure-cli-latest
Additional Information
It is only possible to apply this configuration on SQL server. The same configuration is in effect on SQL Databases hosted on SQL Server. Ensuring protection of TDE by a Customer-managed key on SQL Server does not ensure the encryption of SQL Databases. Transparent Data Encryption : Data Encryption (ON/OFF) setting on individual SQL Database determines database encryption.