Skip to content

Latest commit

 

History

History
86 lines (58 loc) · 4.09 KB

encryption-at-rest-sql-server-tde.md

File metadata and controls

86 lines (58 loc) · 4.09 KB
Raw
title titleSuffix description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic
SQL Server Big Data Clusters transparent data encryption (TDE) at rest usage guide
SQL Server Big Data Clusters
This article shows how to use SQL Server TDE Encryption at Rest feature of BDC
HugoMSFT
hudequei
wiassaf
06/14/2021
sql
big-data-cluster
tutorial

SQL Server Big Data Clusters transparent data encryption (TDE) at rest usage guide

[!INCLUDESQL Server 2019]

[!INCLUDEbig-data-clusters-banner-retirement]

This guide demonstrates how to use encryption at rest capabilities of [!INCLUDEssbigdataclusters-ss-nover] to encrypt databases.

The configuration experience for the DBA when configuring SQL Server transparent data encryption is the same SQL Server on Linux and standard TDE documentation applies except where noted. In order to monitor status of encryption on master, follow standard DMV query patterns on top of sys.dm_database_encryption_keys and sys.certificates.

Unsupported features:

  • Data pool encryption

Prerequisites

Query the installed certificates

  1. In Azure Data Studio, connect to the SQL Server master instance of your big data cluster. For more information, see Connect to the SQL Server master instance.

  2. Double-click on the connection in the Servers window to show the server dashboard for the SQL Server master instance. Select New Query.

    SQL Server master instance query

  3. Run the following Transact-SQL command to change the context to the master database in the master instance.

    USE master;
GO

  4. Query the installed system-managed certificates.

     SELECT TOP 1 name FROM sys.certificates WHERE name LIKE 'TDECertificate%' ORDER BY name DESC;

    Use different query criteria as needed.

    The certificate name will be listed as "TDECertificate{timestamp}". When you see a prefix of TDECertificate and followed by timestamp, this is the certificate provided by the system-managed feature.

Encrypt a database using the system-managed certificate

In the following examples consider a database named userdb as the target for encryption and a system-managed certificate named TDECertificate2020_09_15_22_46_27, per output of previous section.

  1. Use the following pattern to generate a database encryption key using the provided system certificate.

     USE userdb; 
 GO
 CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECertificate2020_09_15_22_46_27;
 GO

  2. Encrypt database userdb with the following command.

     ALTER DATABASE userdb SET ENCRYPTION ON;
 GO

Manage database encryption when using external providers

For more information on the way key versions are used on [!INCLUDEssbigdataclusters-ss-nover] encryption at rest, see [Key Versions in [!INCLUDEbig-data-clusters-2019]](big-data-cluster-key-versions.md). The section "Main key rotation for SQL Server" contains an end-to-end example on how to manage database encryption when using external key providers.

Next steps

Learn about encryption at rest for HDFS:

[!div class="nextstepaction"] HDFS Encryption Zones