SQL Encrypted Fields

This document shows step-by-step instructions for setting up encrypted fields in a SQL database table.

/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE PasswordTable (SEQ INT, AdminPassword VARCHAR(50))
GO
INSERT INTO PasswordTable (SEQ, AdminPassword)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO

/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password-1234'
GO

/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'Password-1234'
GO

/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY PasswordTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO

/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE PasswordTable
ADD EncryptAdminPassword VARBINARY(256)
GO

/* Update binary column with encrypted data created by certificate & key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE PasswordTable
SET EncryptAdminPassword = ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),AdminPassword)
GO

/* DROP original column which was encrypted to protect the data */
USE EncryptTest
GO
ALTER TABLE PasswordTable
DROP COLUMN AdminPassword
GO

/* Check the content of the PasswordTable */
USE EncryptTest
GO
SELECT *
FROM PasswordTable
GO

/* Decrypt the data of the AdminPassword */
USE EncryptTest
GO
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT SEQ, CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptAdminPassword)) AS DecryptAdminPassword, EncryptAdminPassword, RegStamp, DateStamp
FROM PasswordTable
GO

/* This is an example of adding a record with an encrypted field */
OPEN SYMMETRIC KEY PasswordTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
INSERT INTO PasswordTable
(EncryptAdminPassword, RegStamp, DateStamp)
VALUES(ENCRYPTBYKEY(KEY_GUID('PasswordTableKey'),'DogBreath91'),'test 2014.01','2014-01-15')
GO