Menu Close

Enabling Transparent Data Encryption (TDE) on SQL Server

Previously we discussed how to take encrypted backups in SQL server, in this post will continue the encryption topic and implement Transparent Data Encryption (TDE) on SQL server. TDE does real-time I/O encryption and decryption of data and log files. The encryption uses a database encryption key (DEK) which is a symmetric key. It’s secured by a certificate that the server’s master database stores or by an asymmetric key that an EKM module protects. TDE protects data at rest, which is the data and log files. This ability lets you encrypt data by using AES and 3DES encryption algorithms without changing existing applications. Let’s start then,

First we will check if any database is encrypted already or not. For that we run the query below. Then, we create a master key, but got an error as I have already created on while doing Encrypted Backup Demo previously. 

##Checking Encrypted Databases
select name, is_encrypted from master.sys.databases;
##Creating master key
create master key encryption by password = ‘pwd4TDEencrption’


Will create a Database Encryption Key (DEK) certificate, and most important is to backup those certificate securely.

Create certificate myservercert with subject = 'DEK Certificate'
Backup certificate myservercert to file= ‘J:sharedfolderSQLServerCertificateBKPmyservercert’
Lastly, will enable DEK using the created certificate and encryption algorithm (using AES128 here), and set encryption on database using ALTER DATABASE command.
USE [AdventureWorks2019]
create database encryption key with algorithm = AES_128
encryption by server certificate myservercert
##enable TDE
ALTER DATABASE [AdventureWorks2019] set encryption on;

Will check to cross check the encrypted database’s status using command again.
select name, is_encrypted from master.sys.databases;


Tip: You can check the Keys and Certificates available in SQL Server using below queries.


select * from sys.certificates;
select * from sys.symmetric_keys;
select * from sys.asymmetric_keys;
~Thank you for reading

Leave a Reply

Your email address will not be published. Required fields are marked *