SQL 2025 master class LAB 13 TDE

  --step 1

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='MasterPassword';

GO

CREATE CERTIFICATE TDEServerCertificate

WITH

SUBJECT='TDEServerCertificate';

GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'TDEdemo')
    CREATE DATABASE TDEdemo;
GO 

--Step 2 

USE TDEdemo 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDEServerCertificate;

GO

ALTER DATABASE TDEdemo

SET ENCRYPTION ON;

GO

--Step 3 

USE master 

BACKUP CERTIFICATE TDEServerCertificate

TO FILE = 'C:\Backup\TDEServerCertificate'

WITH PRIVATE KEY (file='C:\Backup\TDECertKey.pvk',

ENCRYPTION BY PASSWORD='MasterPassword')

 

--Step 4 If necessary to restore. Certifcate already exists

USE MASTER

GO

CREATE CERTIFICATE TDEServerCertificate

FROM FILE = 'C:\Backup\TDEServerCertificate'

WITH PRIVATE KEY (FILE = 'C:\Backup\TDECertKey.pvk',

DECRYPTION BY PASSWORD = 'MasterPassword' )

--with thanks and acknowledgement to https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/

 --Housekeeping

 use MASTER

ALTER DATABASE TDEdemo
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TDEdemo;
GO

 DROP CERTIFICATE TDEServerCertificate
 DROP MASTER KEY

--delete files under c:\backup