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’
USE [AdventureWorks2019] go create database encryption key with algorithm = AES_128 encryption by server certificate myservercert ##enable TDE ALTER DATABASE [AdventureWorks2019] set encryption on; go
select name, is_encrypted from master.sys.databases;
select * from sys.certificates; select * from sys.symmetric_keys; select * from sys.asymmetric_keys;