How to set up SQL Server Column Level Encryption

Question: How do I set up the SQL Server Column Level Encryption ? 

Answer: The setup for  SQL Server Column Level Encryption is  an easy process  - but it does help to understand the underlying framework 

The basic framework for SQL Server Encryption is

1) The SQL Server installation creates a Service Master Key (SMK), and Windows operating system Data Protection API (DPAPI) protects the SMK
2) The SMK protects the database master key (DMK)
3) The DMK protects the self-signed certificate
4) The self-signed  certificate protects the Symmetric key

The code sequence below is a demo and the purpose is to demonstrate setting a up database , create table , add data , create the keys\self signed certificate 

--create database for demo
CREATE DATABASE ColumnEncryption;
GO
USE ColumnEncryption;
GO
--create table for demo
CREATE TABLE testData 
              (ID INT , 
			  Surname VARCHAR(20) NOT NULL,
			  InsuranceNumber VARCHAR(20) NOT NULL);
GO
--insert some test data - pre Encryption
INSERT INTO testData(ID,Surname,InsuranceNumber)
SELECT 100,'Prince','80980980' UNION
SELECT 200,'Santana','80980980' UNION
SELECT 200,'Satriani','80980980' 

GO
--create master key --Always back up your database master key
USE ColumnEncryption;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sqlserver-dba.com!!';
GO
--create certificate 
USE ColumnEncryption;
GO
CREATE CERTIFICATE MyCert_test WITH SUBJECT = 'Column Protection';
GO
--create symetric key - using single key for encryption and decryption
CREATE SYMMETRIC KEY SymKey_demo WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCert_test;

--All keys are now created
--Encrypted columns must be varbinary
ALTER TABLE testData
ADD InsuranceNumber_encrypted varbinary(MAX)
--open symetric key 
OPEN SYMMETRIC KEY SymKey_demo
        DECRYPTION BY CERTIFICATE MyCert_test;
--update some data encrypted
UPDATE testData
        SET InsuranceNumber_encrypted = EncryptByKey (Key_GUID('SymKey_demo'), InsuranceNumber)
        FROM testData
		--close symetric key 
CLOSE SYMMETRIC KEY SymKey_demo;  

--this VERSION OF THE SQL STATEMENT will NOT return encrypted data
select * from testData;

--need to open symetric key 
OPEN SYMMETRIC KEY SymKey_demo
        DECRYPTION BY CERTIFICATE MyCert_test;

SELECT ID,Surname,InsuranceNumber_Encrypted,
CONVERT(varchar, DecryptByKey(InsuranceNumber_Encrypted)) AS 'Decrypted Insurance Number'
FROM testdata;




--check the master key has been created 
SELECT name KeyName, 
    symmetric_key_id KeyID, 
    key_length KeyLength, 
    algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;

--verify certificates
SELECT name CertName, 
    certificate_id CertID, 
    pvt_key_encryption_type_desc EncryptType, 
    issuer_name Issuer
FROM sys.certificates;

Read more on SQL Server Encryption 

A simple guide to Transparent Data Encryption in AWS RDS SQL Server

A guide to restore a TDE database backup to another SQL Server Instance

Impact on TDE encryption in progress on SQL Server restart

Tested TDE with Instant File Initialization on SQL Server databases