Transparent Data Encryption in SQL Server

-- ============================================================================
-- Transparent Data Encryption in SQL Server
--
-- Transparent Data Encryption(TDE) is used in SQL Server to protect data by
-- encrypting physical files of the database.In TDE only files (ldf, mdf and
-- bak) will be encrypted, but not the data. In this TDE, database will be
-- encrypted using a Database Encryption Key. This database encryption key will
-- be protected by a certificate and master key which are present in Master
-- Database. To get the database information decrypted on the fly, a user
-- connecting to the database should be accompanied by the Instance level
-- Service Master key, this preserves the security in tact. As Service master
-- key is at instance level, physical data files cannot be taken to other
-- instances and get decrypted, that way data is protected.
--
-- Follow below steps to encrypt a database:
--
-- NOTE: Make sure your Database edition is one of Enterprise, Developer,
-- Evaluation editions. Express Edition doesn't support TDE.
--
-- Source:
-- http://www.intstrings.com/ramivemula/articles/implement-transparent-data-encryptiontde-in-sql-server/
-- ============================================================================

-- Step 1: Create a Master Key
-- The database master key is a symmetric key used to protect the private keys
-- of certificates and asymmetric keys that are present in the database.
use [master];
go

create master key encryption by password = 'P@ssw0rd';
go

-- Step 2: Create a Certificate
-- CREATE CERTIFICATE can load a certificate from a file or assembly. It can
-- also be used to create a self signed certificate.
create certificate AdventureWorksTDECert with subject = 'ADWorksTDE';
go

-- Step 3: Create a Database Encryption Key
-- A database encryption key is required before a database can be encrypted by
-- using Transparent Database Encryption (TDE).
use [AdventureWorks2012];
go

create database encryption key
with algorithm = aes_256 encryption
by server certificate [AdventureWorksTDECert];
go

-- Step 4
-- Encrypt database
alter database AdventureWorks2012 set encryption on;
go