How to set up SQL Server Column Level Encryption

09 June,2022 by Tom Collins

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


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to set up SQL Server Column Level Encryption


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer