Assymmetric encrpytion – Enabling TDE

What is TDE
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

TDE and TempDB
The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.

Enabling TDE
The TDE model uses a new specialized symmetric key called the database encryption key (DEK) to encrypt your database. The DEK is protected by a server certificate—a certificate created in the master database. The server certificate is protected by the master database DMK, which must be protected in turn by the SMK.

The first step to enabling TDE is to create a DMK in the master database (if one doesn’t already exist).
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N’StrongPassword’;
GO

After you’ve created the master database DMK you need to create a server certificate in the master database. This is the certificate that will protect the DEK that you’ll create in subsequent steps.

USE master;
GO
CREATE CERTIFICATE TDE_Certificate
WITH SUBJECT = N’TDE Encryption Server Certificate’;
GO

After you create your server certificate you should immediately back it up using the BACKUP CERTIFICATE statement. Immediately store the certificate backup in a secure facility. You’ll need the server certificate if you want to restore the encrypted database from a backup or attach the database files to a different server in the future.

USE master;
GO
BACKUP CERTIFICATE TDE_Certificate
TO FILE = N’c:\Server_Certificate.cer’
WITH PRIVATE KEY
(
FILE = N’c:\Server_Certificate.pvk’,
ENCRYPTION BY PASSWORD = N’AnyStrongPassword’
);
GO
Now that you have a server certificate you can switch back to your user database and create a DEK with the CREATE DATABASE ENCRYPTION KEY statement. You can only create one DEK per user database.

USE AdventureWorksLT2008;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
GO

Finally enable encryption

ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO