Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) provides the ability to encrypt an entire database and to have the encryption be completely transparent to the applications that access the database. TDE encrypts the data stored in both the database's data file (.mdf) and log file (.ldf) using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption. In addition, any backups of the database are encrypted. This protects the data while it's at rest as well as provides protection against losing sensitive information if the backup media were lost or stolen. The code below demonstrates how to enable TDE on a sample database named UserDB.
--Check if the Database Master Key already present.
USE master;
GO
SELECT * FROM sys.symmetric_keys;
--Drop the existing Master Key.
USE master;
GO
BEGIN TRY
DROP MASTER KEY;
END TRY
BEGIN CATCH
PRINT 'Master Key NOT exists.';
END CATCH
GO
--Create Master Key in master database.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master key password';
GO
--Create Server Certificate in the master database.
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master key password';
CREATE CERTIFICATE SQL_TDE_CERT WITH SUBJECT = 'SQL TDE CERT';
GO
--Create User Database Encryption Key.
USE UserDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE SQL_TDE_CERT;
GO
--Check Database Encryption Key created.
--The tempdb system database will also be encrypted
--if any other database on the instance of SQL Server is encrypted by using TDE.
SELECT DB_NAME(database_id) AS database_name, * FROM sys.dm_database_encryption_keys;
--Enabling Transparent Database Encryption for the User Database.
USE master;
GO
ALTER DATABASE UserDB SET ENCRYPTION ON;
GO
--Check User Database is encrypted.
SELECT name, is_encrypted FROM sys.databases;
--Backup Master Key to file.
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master key password';
BACKUP MASTER KEY TO FILE = 'D:\MSSQL_TDE_KEYS\MasterKey.mtk' ENCRYPTION BY PASSWORD = 'master key password';
GO
--Backup Server Certificate.
USE master;
GO
BACKUP CERTIFICATE SQL_TDE_CERT TO FILE = 'D:\MSSQL_TDE_KEYS\ServerCert.cer'
WITH PRIVATE KEY ( FILE = 'D:\MSSQL_TDE_KEYS\PrivateKey.pvk', ENCRYPTION BY PASSWORD = 'master key password');
GO