TDE

Transparent Data Encryption Eligible SQL Server Editions

First we must determine the correct version of SQL Server that allows Transparent Data Encryption. I like to call it an expensive feature as it requires Enterprise Editions. It also works with Developer Edition, but of course, this is just for testing and development purposes. When implementing this in a production environment you must have the correct version of SQL Server. I’ve listed the eligible editions below.

• SQL 2017 Evaluation, Developer, Enterprise
• SQL 2016 Evaluation, Developer, Enterprise
• SQL 2014 Evaluation, Developer, Enterprise
• SQL Server 2012 Evaluation, Developer, Enterprise
• SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter
• SQL Server 2008 Evaluation, Developer, Enterprise

--TDE encription

Enctyption status;

SELECT DB_Name(database_id) AS 'Database', encryption_state

FROM sys.dm_database_encryption_keys;

--Step1:

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password12#';

select * from sys.symmetric_keys;

   --Step2: 

Use Master;

CREATE CERTIFICATE TDE_cert_SQLDBAHUB

WITH SUBJECT = 'TDE_cert_SQLDBAHUB';

   --drop certificate TDE_cert_CMTDBTEST

select * from sys.certificates;

   --Step3: SQLDBAHUB-database name,

use SQLDBAHUB

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE [TDE_cert_SQLDBAHUB];

   --Step4:

ALTER DATABASE SQLDBAHUB SET ENCRYPTION ON;

Encryption progress check:

SELECT DB_NAME(database_id) as DB,encryption_state FROM

sys.dm_database_encryption_keys WHERE database_id=DB_ID();

SELECT * FROM sys.dm_database_encryption_keys WHERE database_id=DB_ID();

select @@servername 'Server_name',name,is_encrypted from sys.databases where name='SQLDBAHUB'

SE master;

GO

SELECT

serverproperty('mssqlserver') [Instance Name],

ce.name as certificate_name

FROM

sys.databases as [db]

LEFT OUTER JOIN sys.dm_database_encryption_keys as [dm]

ON db.database_id = dm.database_id

LEFT OUTER JOIN sys.certificates as [ce] ON

ce.thumbprint= dm.encryptor_thumbprint

where dm.encryption_State=3 and db.name not in ('SQLDBAHUB')

--Backup the database certificate the just got created

BACKUP CERTIFICATE tdeCert TO FILE = 'D:\MSSQL15.MSSQLSERVER\MSSQL\TDE\TDEdev.cer'

WITH PRIVATE KEY (

FILE = 'D:\MSSQL15.MSSQLSERVER\MSSQL\TDE\tdekey.pvk',

ENCRYPTION BY PASSWORD = 'Password@123')

--Target Server Note: Restore all certificates

       USE MASTER
       GO
       CREATE CERTIFICATE TDECert_VT82R02
       FROM FILE = 'D:\MSSQL15.MSSQLSERVER\MSSQL\TDE\TDEdev.cer'
       WITH PRIVATE KEY (FILE = 'D:\MSSQL15.MSSQLSERVER\MSSQL\TDE\tdekey.pvk',
       DECRYPTION BY PASSWORD = 'Password@123' );

--drop existing certificates <TDECert_VT82R02>

       USE master
       Go
       DROP CERTIFICATE TDECert_VT82R02;
       Go