Edit

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

  • Article

Applies to: SQL Server 2019 (15.x)

Important

The Microsoft SQL Server 2019 Big Data Clusters add-on will be retired. Support for SQL Server 2019 Big Data Clusters will end on February 28, 2025. All existing users of SQL Server 2019 with Software Assurance will be fully supported on the platform and the software will continue to be maintained through SQL Server cumulative updates until that time. For more information, see the announcement blog post and Big data options on the Microsoft SQL Server platform.

This guide demonstrates how to use encryption at rest capabilities of SQL Server Big Data Clusters 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 SQL Server Big Data Clusters encryption at rest, see Key Versions in SQL Server Big Data Clusters. 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:

HDFS Encryption Zones