SQL Server : How to configure Transparent Data Encryption (TDE)?

How to configure Transparent Data Encryption (TDE)?

USE master;
GO
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password@123'; -- Create master Key

/*command to view Keys*/
SELECT name KeyName,
  symmetric_key_id KeyID,
  key_length KeyLength,
  algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;



CREATE CERTIFICATE TDECerti WITH SUBJECT = 'TDE certificate'; --Create Certificate

/*command to view certificate */
SELECT name CertName,
  certificate_id CertID,
  pvt_key_encryption_type_desc EncryptType,
  issuer_name Issuer
FROM sys.certificates
WHERE issuer_name = 'TDE certificate';


/* create DEK */

USE SQLDummyDB;
GO
 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE TDECerti;


/*Command to view all DEK */
SELECT DB_NAME(database_id) DbName,
  encryption_state EncryptState,
  key_algorithm KeyAlgorithm,
  key_length KeyLength,
  encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;



ALTER DATABASE SQLDummyDB SET ENCRYPTION ON; --Enable TDE

Backup the certificate and keys

Use master;
GO
 
BACKUP SERVICE MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\SvcMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123';


BACKUP MASTER KEY 
TO FILE = 'E:\MSSQLServer\Backup\DbMasterKey.key'
ENCRYPTION BY PASSWORD = 'Password@123'


BACKUP CERTIFICATE TDECerti 
TO FILE = 'E:\MSSQLServer\Backup\TdeCerti.cer'
WITH PRIVATE KEY(
  FILE = 'E:\MSSQLServer\Backup\TdeCerti.pvk',
  ENCRYPTION BY PASSWORD = 'Password@123');
GO

/* Restore Certificate Key */

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='DPassword@123';
GO


USE MASTER
GO
Create CERTIFICATE TDECerti
FROM FILE = 'D:\Backups\TdeCerti.cer'
WITH PRIVATE KEY (FILE = 'D:\Backups\TDECerti.pvk',
DECRYPTION BY PASSWORD = 'Password@123' );
GO

Advantages
1. The performance impact of TDE is minor.
2. Performs real-time I/O encryption and decryption of the data and log files.
3. No application code changes are required.
4. No architectural changes required.
5. Encrypts the Entire data in Database.

Disadvantages
1. Not granular – Cannot just encrypt specific tables/columns
2. Not good for high CPU bottleneck servers
3. No protection for data in memory
4. Not protected through communication/networks