Column Level Encryption Walkthrough

Here is a quick script to demonstrate reversible encryption.

First, some configuration -

1. Setup a master key 
2. Create a Certificate 
3. Create a Symmetric key

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'ins3cur3-p4ss'
GO

CREATE CERTIFICATE Certificate1
   WITH SUBJECT = 'AES Encryption';
GO
 
CREATE SYMMETRIC KEY Key1
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Certificate1;
GO

Next create the test environment by

1. Creating a table 
2. Inserting Data 
3. Showing the results

CREATE TABLE EncryptionTest
 (ID INT IDENTITY(1,1)
 ,email VARCHAR(100) NOT NULL 
 ,passwordplain NVARCHAR(16) NOT NULL
 ,passwordencrypt VARBINARY(256) NULL
 )
GO

INSERT INTO EncryptionTest (email,passwordplain) VALUES ('someone@someplace.com', 'simplepass')
GO

SELECT * FROM EncryptionTest 

ID email passwordplain passwordencrypt 
1 someone@someplace.com simplepass NULL 

Now populate the passwordencrypt function using the EncryptByKey function.

OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE Certificate1;

UPDATE EncryptionTest
SET passwordencrypt = EncryptByKey(Key_GUID('Key1'), passwordplain);

CLOSE SYMMETRIC KEY Key1;

SELECT * FROM EncryptionTest 

ID email passwordplain passwordencrypt 
1 someone@someplace.com simplepass 0x00543AEC5035FA48BE3115FF0E14A3320100000093F2435E1F1DC8ACCC03097E6AD5D1262C4F2FF1ADE341D38207035E1FCD1B2A82123F5DCBDA3414BC3490593924B830 

Finally, prove we can decrypt the password again.

OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE Certificate1;

SELECT ID, email, passwordencrypt, CONVERT(NVARCHAR(16), DecryptByKey(passwordencrypt)) 
AS [DecryptedPassword]
FROM EncryptionTest

CLOSE SYMMETRIC KEY Key1;

ID email passwordencrypt DecryptedPassword 1 someone@someplace.com 0x00543AEC5035FA48BE3115FF0E14A3320100000093F2435E1F1DC8ACCC03097E6AD5D1262C4F2FF1ADE341D38207035E1FCD1B2A82123F5DCBDA3414BC3490593924B830 simplepass