Encrypt data using Transparent Data Encryption (TDE) in SQL

Introduction

This blog describes how to use Transparent Data Encryption (TDE) to encrypt data in SQL server. The demonstration in this blog is conducted in SQL Server 2017.

Getting Started

Transparent Data Encryption (TDE) is one algorithm supported by the SQL server for encrypting data, it is a real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery.

TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

DEK

DEK known as Database Encryption Key (DEK) management, it is asymmetric key and supported algorithms are AES with 128-bit, 192bit, or 256bit keys or 3 Key Triple DES. Once TDE is enabled on a database then the DEK is used to encrypt the contents of the database and the log. When TDE is enabled for any database on the server, TempDB is also encrypted and its DEK is managed internally by SQL Server.

TDE does not provide encryption across communication channels. We will discuss how to encrypt data across communication channels in my anther blog.

Enabling and Disabling Encryption

Follow these steps to enable TDE for a SQL Data Warehouse:
1. Connect to the master database on the server hosting the database using a login that is an administrator or a member of the dbmanager role in the master database
2. Execute the following statement to encrypt the database, Replace 'DATABASE_NAME' with your database name.
   

    ALTER DATABASE [DATABASE_NAME] SET ENCRYPTION ON  
   

Follow these steps to disable TDE for a SQL Data Warehouse:
1. Connect to the master database using a login that is an administrator or a member of the dbmanager role in the master database
2. Execute the following statement to encrypt the database.
   

    ALTER DATABASE [DATABASE_NAME] SET ENCRYPTION OFF
   

Setps To Verifying Encryption

1. Connect to the master or instance database using a login that is an administrator or a member of the dbmanager role in the master database
2. Execute the following statement to encrypt the database.
   

    SELECT [name],[is_encrypted] FROM sys.databases  
   

Steps to use Transparent Data Encryption

1. Create a master key
2. Create or obtain a certificate protected by the master key.
3. Create a database encryption key and protect it by the certificate.
4. Set the database to use encryption.

The following example illustrates encrypting and decrypting the mydatabaseenc database using a certificate installed on the server named MyServerCert.

 USE master;   
      GO   
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';   
      go   
      CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';   
      go   
      USE mydatabaseenc;   
      GO   
      CREATE DATABASE ENCRYPTION KEY   
      WITH ALGORITHM = AES_128   
      ENCRYPTION BY SERVER CERTIFICATE MyServerCert;   
      GO   
      ALTER DATABASE mydatabaseenc   
      SET ENCRYPTION ON;   
      GO   

Related Articles

1. Enable SQL Remote Connection
2. Encrypt data across communication channels
3. Recovery Database From Warm-Standy Mode
4. Setting Memory Limit for a SQL Server Instance
5. Install certificate to enable encrypted connections to the Database Engine in SQL Server
6. Resolve “Oracle JRE 7 Update 51 (64-bit) or Higher is Required “

Thanks