Cell Level Encryption
A complete walkthrough of cell-level data encryption using a master key, certificate and AES-256 symmetric key.
How to encrypt individual columns - SSN, bank account number, salary - rather than the whole database. This script walks through the entire chain: create a master key, create a certificate, create an AES-256 symmetric key encrypted by that certificate, back up the certificate with its private key, then use EncryptByKey with the row's Id as the key identifier to build an EncryptedEmployee table from a plain Employee table. The decrypt section shows the matching DecryptByKey call, and the final notes cover granting CONTROL on the key and certificate to non-admin users so they can decrypt without being owners.
------------------------------------------------------------------------------------------------------------------------
/*
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsMyTestPassword!'
*/
------------------------------------------------------------------------------------------------------------------------
/*
CREATE CERTIFICATE Cert_Generic_Encryption WITH
Subject = 'Employee Cert',
START_DATE = '1/1/2005',
EXPIRY_DATE = '1/1/2099'
*/
------------------------------------------------------------------------------------------------------------------------
/*
CREATE SYMMETRIC KEY Key_Employee WITH
ALGORITHM = AES_256,
IDENTITY_VALUE = 'This is my phrase and you cant have it, go tell your mama!',
KEY_SOURCE = 'What Key Source? i was expected to supply a Key Source? Rubbish!'
ENCRYPTION BY CERTIFICATE Cert_Generic_Encryption
*/
------------------------------------------------------------------------------------------------------------------------
/* --Backup the certificate, the symmetric key does not require backup and can simply be recreated.
BACKUP CERTIFICATE Cert_Generic_Encryption
TO FILE = 'C:\Cert_Generic_Encryption.cer'
WITH PRIVATE KEY
(
FILE = 'C:\Cert_Generic_Encryption.pvk',
ENCRYPTION BY PASSWORD = '!!Certificate!!Backup!!Password!!'
)
*/
------------------------------------------------------------------------------------------------------------------------
/* --Encrypt the data.
OPEN SYMMETRIC KEY Key_Employee
DECRYPTION BY CERTIFICATE Cert_Generic_Encryption
SELECT
Id,
FirstName,
LastName,
EncryptByKey(Key_Guid('Key_Employee'), Cast(BirthDate as VarChar(100)), 1, Cast(Id AS VarChar(100))) AS BirthDate,
Race,
Gender,
EncryptByKey(Key_Guid('Key_Employee'), SSN, 1, Cast(Id AS VarChar(100))) SSN,
EncryptByKey(Key_Guid('Key_Employee'), BankAccountNumber, 1, Cast(Id AS VarChar(100))) AS BankAccountNumber,
EncryptByKey(Key_Guid('Key_Employee'), Cast(SalaryAmount as VarChar(100)), 1, Cast(Id AS VarChar(100))) AS SalaryAmount,
HireDate
INTO
EncryptedEmployee
FROM
Employee
CLOSE SYMMETRIC KEY Key_Employee
*/
------------------------------------------------------------------------------------------------------------------------
/* --Decrypt the data.
OPEN SYMMETRIC KEY Key_Employee
DECRYPTION BY CERTIFICATE Cert_Generic_Encryption
SELECT
Cast(DecryptByKey(SSN, 1, Cast(Id AS VarChar(100))) AS VarChar(100))
FROM
EncryptedEmployee
CLOSE SYMMETRIC KEY Key_Employee
*/
/*--Allow non-administrators and non-owners to decrypt the data.
GRANT CONTROL ON SYMMETRIC KEY :: Key_Employee TO JMP_Enc_Test
GRANT CONTROL ON CERTIFICATE :: Cert_Generic_Encryption TO JMP_Enc_Test
*/
------------------------------------------------------------------------------------------------------------------------
/* --View key and certficate permissions.
SELECT * FROM fn_my_permissions('Key_Employee', 'SYMMETRIC KEY')
SELECT * FROM fn_my_permissions('Cert_Generic_Encryption', 'CERTIFICATE')
*/
------------------------------------------------------------------------------------------------------------------------