Transparent Data Encryption

/*Encrypt Databases using TDE

Below are the steps to Encrypt databases using TDE Encryption: */

---Creating Demo Database

Create Database EncryptionDemo

GO

CREATE SCHEMA Person1 AUTHORIZATION [Public]

Go

---Creating Table----

/****** Object:  Table [Person].[Address]    Script Date: 12/26/2013 2:32:44 AM ******/

CREATE TABLE [Person].[Address]

(

[AddressID] [int] ,

[AddressLine1] [nvarchar](60) NOT NULL,

[AddressLine2] [nvarchar](60) NULL,

[City] [nvarchar](30) NOT NULL,

[StateProvinceID] [int] NOT NULL,

[PostalCode] [nvarchar](15) NOT NULL,

[SpatialLocation] [geography] NULL,

[rowguid] [uniqueidentifier] not null,

[ModifiedDate] [datetime] NOT NULL DEFAULT (getdate()));

-----Inserting data into the table-----

USE [EncryptionDemo]

GO

INSERT INTO [Person].[Address]

           ([AddressID]

           ,[AddressLine1]

           ,[AddressLine2]

           ,[City]

           ,[StateProvinceID]

           ,[PostalCode]

           ,[SpatialLocation]

           ,[rowguid]

           ,[ModifiedDate])

 select * from AdventureWorks2012.Person.Address

 GO

 --Verify if Any MasterKey exists

 USE master 

 GO 

 SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%' 

 GO

 ---Create Master Key

 USE master 

 GO 

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssword' 

 GO

 ---Create Certificate

 Use master

 GO

 CREATE CERTIFICATE SQLTDECert WITH SUBJECT = 'SQLTDECert' 

 GO 

 /* Verify Certificate */ 

 SELECT * FROM sys.certificates where [name] = 'SQLTDECert' 

 GO

---Create Database Encryption Key with Algorithm and Certificate

 Use EncryptionDemo

 GO 

 CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE SQLTDECert

 GO

---Backup Certificate and Key

 Use master

 GO

 BACKUP CERTIFICATE SQLTDECert TO FILE = 'E:\TDE\TDECertificate.cert' WITH 

 PRIVATE KEY ( FILE = 'E:\TDE\EncryptPrivateKey.key', 

 ENCRYPTION BY PASSWORD = 'p@ssword') 

 GO

----Enable Encryption

 ALTER DATABASE EncryptionDemo SET ENCRYPTION ON

GO

---View Encrypted Databases, Temp DB will get encrypted automatically

SELECT DB_NAME(database_id) AS DatabaseName ,Encryption_State AS EncryptionState ,

key_algorithm AS Algorithm ,key_length AS KeyLength FROM sys.dm_database_encryption_keys 

GO 

SELECT NAME AS DatabaseName ,IS_ENCRYPTED AS IsEncrypted FROM sys.databases 

where name ='EncryptionDemo' 

GO

--Note Temp DB will automatically get Encrypted

------------------Reverting the changes-------------------
/*Disable Encryption on the database*/

 ALTER DATABASE EncryptionDemo SET ENCRYPTION OFF
GO

---Drop Encryption Key
USE EncryptionDemo
DROP DATABASE ENCRYPTION KEY
GO
---Drop Certificate
USE Master
DROP CERTIFICATE SQLTDECert
Go
---Finally drop Master Key
USE Master
DROP MASTER KEY
GO
---Need to restart the SQL Server to remove Encryption from Temp DB

/* If we try to enable and disable the TDE second time in the same session, we will receive below error:

Msg 33122, Level 16, State 2, Line 85
This command requires a database encryption scan on database 'EncryptionDemo'. However, the database has changes from previous encryption scans that are pending log backup. Take a log backup and retry the command.
Msg 5069, Level 16, State 1, Line 85
ALTER DATABASE statement failed.

Resolution : Change the database to Simple recovery model or take a log backup */

Alter Database EncryptionDemo set Recovery Simple with no_wait