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.
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