Enabling Transparent Data Encryption (TDE) for SQL Server on Linux

In recent years, data encryption has gained popularity as one of the most effective methods for securing data within organizations. Data encryption works by converting data into an unreadable format – called ciphertext – thus protecting the data.

The term “encryption” in and of itself is very broad. One method encrypts sensitive data in a database and uses a certificate to protect the encryption keys. This ensures that no one without the key can access the data. This kind of protection has to be planned in advance to be effective.

Transparent Data Encryption (TDE), also known as encrypting data at rest, is a process that protects – or encrypts – data while it’s at rest. It’s a powerful tool that lets you encrypt both data files and log files.

The encryption technology makes use of a certificate. This certificate is dedicated to safeguarding a DEK, or Database Encryption Key, which serves as a private key meant for decrypting your encrypted information.

This framework offers encryption for data, logs, and backup files, which is vital in many situations, especially for software engineers seeking to comply with various laws and compliance regulations related to storing sensitive customer data.

TDE allows software developers to offer end-to-end encryption by fully encrypting data without changing their existing code to adhere to many rules present in specific industries.

Note: TDE is supported on the following SQL Server Linux editions: Standard, Enterprise, and Developer.

Transparent Data Encryption in SQL Server on Linux

Encryption of a database requires that database pages be encrypted before writing them to disk. Similarly, the database pages should decrypt into memory.

TDE does not increase any of the sizes introduced by either encryption or decryption, nor does it significantly affect access speed (3-5% overhead) for reading or writing to an encrypted database.

The following illustration shows the architecture of Transparent Data Encryption (TDE) on a SQL Database. Database-level items like the database encryption key, ALTER DATABASE statements, and some internal system procedures are user-configurable.

Image Reference: Microsoft docs.

Steps to Configure TDE for SQL Server on Linux

In order to configure TDE for SQL Server on Linux, we will need to run through the following steps:

1. Create a master key.

First, we must create the master key in the Master database. To do this, we will use the “USE MASTER” command since we cannot add keys to a user database.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<usestrongpasswordhere>';< code=""></usestrongpasswordhere>';<>

2. Create a Certificate protected by the master key.

Once you have your strong password created and memorized or saved in a secure place, go ahead and create the actual certificate so you can use it to connect to your server.

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';

go

3. Create a Database Encryption Key

Next, we will utilize the “USE” command to switch over to the database we wish to encrypt. After this command is issued, we will establish a connection or association between the certificate we created and its designated database.

Following this step, we must indicate the type of encryption algorithm we intend to apply to our selected database; in this case, it is AES_256 encryption. In most cases, it is recommended that you use AES_256.

USE AdventureWorks2012;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

GO

4. Enable Encryption

Once you have all your encryption options in place, simply use the ALTER DATABASE command to apply them to your database.

ALTER DATABASE AdventureWorks2012

SET ENCRYPTION ON;

GO

To enable Transparent Data Encryption (TDE) on a database, SQL Server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back to disk.

SQL Server 2019 (15.x) introduces a new feature called TDE Pause and Resume. This feature allows you to resume or suspend the scan when you want to. If scanning is suspended, you will be allowed to continue working on the database without slowing down.

• Script to PAUSE the TDE encryption scan:

ALTER DATABASE SET ENCRYPTION SUSPEND;

• Script to RESUME the TDE encryption scan:

ALTER DATABASE SET ENCRYPTION RESUME;

TDE and the Tempdb system database

Your tempdb database is encrypted if any other database on your SQL Server instance is encrypted with TDE. The encryption might have a performance effect for databases that are not encrypted.

Note: In TDE, all files and filegroups in a database are encrypted. If any filegroup in a database is marked READ ONLY, the database encryption operation fails.

Backup Certificate

It’s essential to keep a copy of the certificate you created in a secure location. If your server goes offline, your backup site will need to import this certificate before it can service customers. Suppose your DR servers are already stood up and on warm/hot standby. In that case, we recommend automatically importing the saved certificate on them ahead of time to smoothly take over without interruption if something happens with the live hardware.

The following script backs up the certificate and its private key on the C:Temp directory.
 

BACKUP CERTIFICATE MyServerCert

TO FILE = 'C:tempMyServerCert'

WITH PRIVATE KEY (file='C:tempMyServerCert.pvk',

ENCRYPTION BY PASSWORD='StrongPassword')

Remove TDE

To remove the transparent data encryption from a database, use the following SQL script.

ALTER DATABASE SET ENCRYPTION OFF;

Wrapping Up

Transparent data encryption (TDE) is a great feature of SQL Server that allows you to encrypt data files and transaction logs to protect against physical theft or unauthorized access.

This feature is essential, particularly in today’s climate, for adhering to compliance laws and regulations and protecting organizations against cyber espionage.

But it’s important to remember that encryption is just one of many security best practices for SQL Server.

Stay on top of all things SQL Server by subscribing to our newsletter below.

Follow us on LinkedIn and Twitter for more great content in the coming weeks. Stay tuned!