Some literature first
If you are not, you should start considering database
encryption for your organization as data is the only reason you are in
business. If and when it becomes compromised bad things are bound to happen.
When TDE is enabled or disabled for that matter a background
process is initiated by the server that encrypts all the database files.
This is an Enterprise or Developer version(s) only feature.
How it works
DDL is initiated (SELECT something FROM
SOMEWHERE WHERE otherthing = 'athing') and update lock is taken on the
database causes an encryption scan, by the way this is asynchronous to DDL any
operation that do not conflict with the update and shared lock proceeds without
interruption excluding the database detach and file structure modification.
After the scan is complete DEK is set to encrypted and all
the files on disk, the database and the log files writes to the disk will be
encrypted.
What's encrypted?
Files on disk will be encrypted the *.mdf, *.ndf, snapshots,
backups, "*.ldf” and TempDB is by default encrypted when encryption is
enabled on user databases.
Transaction logs by design are write-once fail safe hence a challenge for TDE thus not everything
is encrypted on them.
What's not encrypted?
Data that is in memory (data in use) is not encrypted TDE
does not offer in memory encryption (Consider Cell-Level encryption if this is
a requirement).
Data in transit is not encrypted either (consider using SSL
if this is a requirement)
Database page files (written to disk), the headers are also
not encrypted because they may be used for reload.
System databases are currently not encrypted by TDE.
Impact on databases
TDE as the name suggests tries to make the encryption as
transparent as possible, no application changes and user experience is meant to
remain the same as non-encrypted database.
Being a database level process is what makes it possible to
perform as a non-encrypted database because the database can still leverage the
usual database optimizations.
Encryption is CPU intensive hence generating high I/O.
Consider your I/O impact prior to enabling encryption. It’s usually estimated
to be about 30% increase in CPU usage.
During the initial encryption scan process, performance is
sharply impacted thus consider enabling the process during light usage period in
your environment.
There is no padding on database files on disk though
transaction logs are padded.
User permissions are not impacted.
Since it’s an Enterprise/Developer versions only feature,
encrypted databases cannot be used in other versions.
Impact on Backups
Don’t loose the key, I repeat don’t loose the key else you
are doomed... enuf sed.
See Create a backup of the DEK on making it work
Making it work
--- Check if encryption exists
SELECT name, is_encrypted FROM SYS.DATABASES
--- Check if certificate exists
SELECT name, is_encrypted FROM MASTER.SYS.CERTIFICATES WHERE pvt_key_encryption_type = 'MK' AND NAME LIKE '%TDE'
--- Create an encryption key DMK
database master key
--- Ensure that the DMK is encrypted by
SMK
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password'
--- Create a certificate to be used by
DEK database encryption key
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate';
--- Create a backup of the DEK this is
the one that goes under the bed you lose it you lose your data
--- I've always preferred UNC path's ensure that you have appropriate permissions defined.
BACKUP CERTIFICATE tdeCert TO FILE = '\\myuncpath\filename.certbak'
WITH PRIVATE KEY (
FILE = '\\myuncpath\filename.pkbak',
ENCRYPTION BY PASSWORD = 'strong cert password');
--- If data is transmitted beyond your
server, consider SSL'ing your box.
--- Create your DEK, ensure that you
pick the appropriate algorithm at the time of publishing this article AES-256
(3 Key Triple DES) was the highest encryption per NIST and NSA.
--- This step here is necessary to set the USE database else you will get the error "Cannot change database encryption state because no database encryption key is set."
USE <MyDatabase>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert
--- Enable your TDE on your database
ALTER DATABASE myDatabase SET ENCRYPTION ON
--- Monitor progress
SELECT db_name(database_id), encryption_state
FROM sys.dm_database_encryption_keys
/******** if you already have an
existing key that needs to be transferred to a different server
******************/
-- This is necessary if you will be
restoring your database in a different SQL Server.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some strong password'
--- Create a certificate generated from
the path where you stored the backup certificate
CREATE CERTIFICATE tdeCert FROM FILE = 'path_to_file'
WITH PRIVATE KEY (
FILE = 'path_to_private_key_file',
DECRYPTION BY PASSWORD = 'strong cert password')