Transparent Data Encryption In SQL Server

by

Introduction

SQL Server Transparent Data Encryption (TDE) is a security feature. TDE is known as encryption at rest. Database files and backups are protected from unauthorized access using AES or 3DES  algorithm.

It keeps the database in its original, unencrypted form as long as explicit data masking is not implemented.

Before SQL server 2019, TDE was enterprise edition feature. TDE is now available in SQL 2019 standard as well.

When using TDE, keep in mind that the encryption process may impact performance. Since data encrypts at the file level, it must decrypt before reading or writing, which can add extra processing time. However, this performance impact is generally minimal and should not be a concern for most applications.

Refer to understand more about performance impact of TDE:
https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/security/transparent-data-encryption

When TDE is enabled on a user database, Tempdb is immediately encrypted, but TDE cannot directly encrypt system databases such as master, model, and msdb..

Enabling TDE on a SQL Server database is a straightforward process. It is easy to enable and use the Advanced Encryption Standard (AES) or Triple Data Encryption Algorithm(3DES) algorithm, making it an effective method for protecting sensitive data from unauthorized access.

Refer official image for more understanding of TDE architecture and key hierarchy

Configuring TDE

TDE is configured in below steps sequentially on a SQL Server Instance

  1. Create a master key.
  2. Create a certificate which is protected using master key.
  3. Create a database encryption key protected by certificate.
  4. Encrypt the database.
--Create master key 
Use  Master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'anystrongpassword';--remember to save this password in a secure repository
GO

--Create certificate
CREATE CERTIFICATE EncryptCert
	WITH SUBJECT = 'Encryption Certificate';
GO

--Create Database encryption Key protected by Certificate
USE < DBNAME >
GO

CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE EncryptCert;
GO

--Enable TDE 
ALTER DATABASE < DBNAME >

SET ENCRYPTION ON;
GO

-- Take backup of certificate to a file, keep password in repository
BACKUP CERTIFICATE EncryptCert TO FILE = '<<anyPath>>\EncryptCertificate.cert'
WITH PRIVATE KEY (
		FILE = '<<anyPath>>\EncryptionKey.key'
		,ENCRYPTION BY PASSWORD = 'anystrongpassword'
		)

Considerations for TDE

  1. We must restore the certificate and key created on the destination server before attaching or detaching a database with TDE enabled to another instance.
  2. Encryption keys and certificates are required to restore the backups of databases with TDE enabled on another server as they are also encrypted.
  3. TDE only secures data at rest and does not prevent users with higher privileges from viewing the data.
  4. Certificate should not be dropped from master database as it would cause encrypted databases to be in accessible.
  5. Very Large database encryption takes some time, which can be checked using database encryption state command below.
SELECT DB_NAME(Database_id)
	,encryption_state
	,encryption_state_desc
	,percent_complete
	,encryption_scan_state
	,encryption_scan_state_desc
	,encryption_scan_modify_date
FROM sys.dm_database_encryption_keys

/*Encryption_state Indicates whether the database is encrypted or not encrypted.

0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key change in progress
5 = Decryption in progress
6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Encryption_scan_state	Indicates the current state of the encryption scan.

0 = No scan has been initiated, TDE is not enabled
1 = Scan is in progress.
2 = Scan is in progress but has been suspended, user can resume.
3 = Scan was aborted for some reason, manual intervention is required. Contact Microsoft Support for more assistance.
4 = Scan has been successfully completed, TDE is enabled and encryption is complete. */

TDE Compatibility with Replication/Always On/Log shipping

  • Replication: TDE does not automatically encrypt replicated objects
  • Availability Groups: We need to restore certificate on all replicas for AG to be TDE encrypted.
  • Log shipping : We need to restore certificate on all secondary servers for LS to be functional.
Post Views: 82