SQL 2008 encryption - Encryption Data

-- Create EmployeeReviews table and grant permissions
USE AdventureWorks

IF EXISTS (SELECT * FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.[Name] = 'EmployeeReview' AND s.[Name] = 'HumanResources')
DROP TABLE HumanResources.EmployeeReview

--comments 是打算加密的欄位
CREATE TABLE HumanResources.EmployeeReview
(EmployeeID int NOT NULL,
ReviewDate datetime DEFAULT GETDATE() NOT NULL,
Comments varbinary(2000) NOT NULL)

--
-- Create database master key
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

-- 這邊因為有datebase key，所以不用指定encryption by password
-- Create certificate
CREATE CERTIFICATE HRAppCert
WITH SUBJECT = 'HR certificate'
GO

-- 使用 HRAppCert加密，產生一把symmetric key:HRKey
-- Create symmetric key
CREATE SYMMETRIC KEY HRKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE HRAppCert
GO

-- 使用symmetric key:HRKey 加密
-- Insert encrypted data
-- 鑰匙要開關
OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert

INSERT INTO HumanResources.EmployeeReview
VALUES
(1, DEFAULT, EncryptByKey(Key_GUID('HRKey'),'Increasing salary to $35,000'))

CLOSE ALL SYMMETRIC KEYS


-- Read the decrypted data as HRApp
OPEN SYMMETRIC KEY HRKey DECRYPTION BY CERTIFICATE HRAppCert
SELECT CONVERT(varchar,DecryptByKey(Comments)) AS Comments FROM HumanResources.EmployeeReview
CLOSE ALL SYMMETRIC KEYS