In recent years, data encryption has gained popularity as 1 of the most efficient methods for securing data inside organizations. Data encryption works by converting data into an unreadable format – called ciphertext – thus protecting the data. This process of converting data to cyphertext is what’s known as encryption.
The term “encryption” in and of itself is very broad. One method encrypts sensitive data in a database and uses a certificates to protect the encryption keys. This ensures that no 1 without the key can entry the data. This kind of protection has to be planned in advance to be efficient.
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 certificates. This certificates 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 laws 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 entry velocity (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
The following script does the following tasks:
1. Creates a master key.
First, we should 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>';
2. Creates 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 certificates so you can use it to connect to your server.
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go
3. Creates a Database Encryption Key
Next, we will utilize the “USE” command to switch over to the database we want to encrypt. After this command is issued, we will establish a connection or association between the certificates we created and its designated database.
Following this step, we should 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 counseled that you use AES_256.
USE AdventureWorks2012; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO
4. Enables 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 should 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 proceed working on the database without slowing down.
ALTER DATABASE <db_name> SET ENCRYPTION SUSPEND;
ALTER DATABASE <db_name> 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.
It’s essential to hold a copy of the certificates you created in a secure location. If your server goes offline, your backup site will need to import this certificates before it can service clients. Suppose your DR servers are already stood up and on warm/hot standby. In that case, we recommend routinely importing the saved certificates on them ahead of time to easily take over without interruption if something happens with the live hardware.
The following script backs up the certificates 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')
To remove the clear data encryption from a database, use the following SQL script.
ALTER DATABASE <db_name> SET ENCRYPTION OFF;
Transparent data encryption (TDE) is a worthy feature of SQL Server that allows you to encrypt data files and transaction logs to protect against physical theft or unauthorized entry. This feature is essential, particularly in today’s climate, for adhering to compliance laws and laws and protecting organizations against cyber espionage. Did you know ClusterControl now helps SQL Server? Start your free 30-day trial and download ClusterControl today, and experience first-hand the benefits of a powerful database automation platform.