TDE Transparent database encryption

Start encrypting your data folks, better be safe than sorry and TDE is a simple way of keeping at least the mdf files and backups safe from snooping eyes.

In more technical terms TDE encrypts data at rest, so data that is on the disk is encrypted, it is decrypted when read into memory, so if you have access to the database through a SQL Server login you can see the decrypted data just as usual. Also the backups are encrypted so an attacker can’t just copy a .bak file and run with it, they’ll need the certificate to be able to decrypt it.

Better than nothing and you can set it up in AlwaysOn too, I have documented a simple way to do it below.

IMPORTANT!!!

Keep your passwords and certificates safe, preferably NOT on the DB servers. Ok? Good 🙂

Run this on a test AO a couple times until you are sure of all the steps.

--Primary
USE Master;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword';
GO

-- Create certificate
CREATE CERTIFICATE TEST_Cert
WITH 
SUBJECT='Test Cert';
GO

-- Backup to somewhere on disk
BACKUP CERTIFICATE TEST_Cert
TO FILE = 'Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (file='Z:\dbbackup\TEST_Cert_Private',
ENCRYPTION BY PASSWORD='alsoreallystrongpassword')

--Secondary
USE Master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword'; --does not need to match primarys master key password
GO

CREATE CERTIFICATE TEST_Cert
FROM FILE='Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (
FILE = 'Z:\dbbackup\TEST_Cert_Private',
DECRYPTION BY PASSWORD='alsoreallystrongpassword')


--Now check on both servers that we have the certificate installed
SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;

--Also check with AlwaysOn dashboard that the databases are in sync

--Primary
USE DB_To_Be_Encrypted  -- I am sure your databases have better names...
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TEST_Cert;
GO

ALTER DATABASE DB_To_Be_Encrypted --This will take a while even if the command returns immediately
SET ENCRYPTION ON;
GO

--On primary we can run some commands to check logspace used, encryption progress and such
USE MASTER;
GO

dbcc sqlperf(logspace)
go

sp_helpdb DB_To_Be_Encrypted
go

-- Check encryotion process
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;
GO

 

How do we remove TDE? Easy, below will clean up what we added above.

--Primary
alter database DB_To_Be_Decrypted set encryption off

-- When it has finished decrypting
use DB_To_Be_Decrypted
drop database encryption key


--On both primary and secondary
use master 
drop certificate TEST_Cert
drop master key

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *