Encrypt a Column in SQL Server 2017

Sample Users table

The Service Master Key

Service Master Key Exists!

The Database Master Key

use mytestdb;create master key encryption by password = ‘<some crazy strong password’;
use mytestdb;select * from sys.symmetric_keys;
Database Master Key Exists!

The Certificate

use mytestdb;create certificate MySSNCertificate with subject = ‘MY SSN Test Certificate’;
use mytestdb;select * from sys.certificates;
Our SSN cert exists!

The Encryption Key

use mytestdb;create symmetric key SSNSymKey with algorithm = AES_256 encryption by certificate MySSNCertificate;
use mytestdb;select * from sys.symmetric_keys;
TADA!

Let’s Get Encrypting!

use mytestdb;alter table [Users] add SSN_Encrypted varbinary(160);
New encrypted column ready and waiting
use mytestdb;-- Open our encryption key
open symmetric key SSNSymKey decryption by certificate MySSNCertificate;
-- Encrypt the data
update [Users] set [SSN_Encrypted] = EncryptByKey(Key_Guid(‘SSNSymKey’), [SSN]);
-- Close our key
close symmetric key SSNSymKey;
Encrypted!

Getting the Data Back Out

use mytestdb;-- Open the key for use like before
open symmetric key SSNSymKey decryption by certificate MySSNCertificate;
-- Decrypt our data on the way out
select *, convert(nvarchar, DecryptByKey([SSN_Encrypted])) as SSN_Decrypted from [Users]
-- Close our key like before
close symmetric key SSNSymKey;
Decrypted!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jake Moening

Jake Moening

61 Followers

Married, software-developing, backpacking, photographing, cooking, father of three. https://www.codecutting.com