Download Complete Steps with Screenshots

Database file encryption occurs at the page level. In an encrypted database, pages are encrypted before being written to disk and decrypted when loaded into memory. Transparent Data Encryption (TDE) does not increase the size of the database.

Transparent Data Encryption (TDE) safeguards data at rest, including both data and log files. It helps organizations comply with various industry laws, regulations, and standards. TDE enables software developers to encrypt data using AES or 3DES encryption algorithms without the need to modify existing applications.

How to encrypt (TDE) in SQL Server

1. Create Master Key in Database Server (Primary Node)
2. Create Certificate                                                                                     (Primary Node)
3. Backup Certificate                                                                                    (Primary Node)
4. Copy Certificate Backup File to Other Secondary Server of Always        (if you are using always on).
5. Create Master Key in Database Server (Secondary Node)                      (if you are using always on).
6. Restore Certificate on Secondary.                                                           (if you are using always on).
7. Create Database Encryption Key on Database                                         (Primary Node)
8. Enable Database Encryption Database                                                    (Primary Node)

Types of Database Encryption

• Database Level - Encrypts data at rest (data and log files).
• Backup Level
  • Database Backup Level
  • Transaction Log Backup Level

Scripts for Encryptions

Execute on Database Primary Node

select * from sys.symmetric_keys;

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='fdr3#edgSar345#drDS';

GO

CREATE CERTIFICATE TDE_Cert

WITH

SUBJECT='Database_Encryption';

GO

SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date

FROM sys.certificates;

mkdir -p C:\Database\Backup\TDE

BACKUP CERTIFICATE TDE_Cert TO FILE = 'C:\Database\Backup\TDE\TDE_Cert' WITH PRIVATE KEY (file='C:\Database\Backup\TDE\TDE_Cert_Key.pvk', ENCRYPTION BY

PASSWORD='fdr3#edgSar345#drDS')

Copy Backup of TDE File on same Directory

Execute on Database Secondary Nodes

select * from sys.symmetric_keys;

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='fdr3#edgSar345#drDS';

GO

CREATE CERTIFICATE TDE_Cert

FROM FILE = 'C:\Database\Backup\TDE\TDE_Cert'

WITH PRIVATE KEY (FILE = 'C:\Database\Backup\TDE\TDE_Cert_Key.pvk',

DECRYPTION BY PASSWORD = 'fdr3#edgSar345#drDS');

select * from sys.symmetric_keys;

SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date

FROM sys.certificates;

Execute on Database Primary Nodes

USE DB1

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

Execute and Verify on All Database Nodes

select db.name

   , db.is_encrypted

   , dm.encryption_state

   , dm.percent_complete

   , dm.key_algorithm

   , dm.key_length

from sys.databases db

left outer join sys.dm_database_encryption_keys dm

   on db.database_id = dm.database_id;