Tag: sql server

In my Encryption Primer talk, I do demo on symmetric key use, and wanted to document it here. Encryption is a serious subject, and please do your research and education, as well as testing, before you implement it.

This post will look at some simple encryption and decryption using symmetric keys. I showed how to create a symmetric key before, so I won’t talk about that here, but I’ll just show the code.

Let’s set up a test table. In this case, I’m showing salary in a table, which isn’t something you want to do, but you might have this in an existing application: data you need to encrypt, but it’s stored unencrypted.

-- create a table 
create table Employees ( 
 id int identity(1,1)
 , firstname varchar(200)
 ,lastname varchar(200)
 ,title varchar(200)
 ,salary numeric(10, 4) );
go
insert Employees 
values
  ('Steve','Jones','CEO', 5000)
 , ('Delaney','Jones','Manure Shoveler', 10)
 , ('Kendall','Jones','Window Washer', 5)
;
go 

Here I have three employees and salaries. Now I want to encrypt the salary. However I cannot just encrypt this value. Encryption creates a binary representation of the data, and that won’t fit in a varchar field. So I need to add a column.

alter table Employees 
 add EnryptedSalary varbinary(max);
go 

Now I have a placeholder, so let’s create a key and then update the new binary column with the encrypted value.

-- create a symmetric key
create symmetric key MySalaryProtector
 WITH ALGORITHM=AES_256
 , IDENTITY_VALUE = 'Salary Protection Key'
 , Key_SOURCE = N'Keep this phrase a secr#t' 
 ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword'
;
go 
-- open the key 
open symmetric key MySalaryProtector
 decryption by password='Us#aStrongP2ssword'
;

-- encrypt the data 
update Employees 
 set EnryptedSalary = ENCRYPTBYKEY(key_guid('MySalaryProtector'),cast(salary as nvarchar))
; 
go 
-- remove the old data 
update employees
 set salary = 0
; 
go 

Note that I open the key, which is needed. I can close it at the end, or it will close when my session ends. I don’t close it here as I usually run this demo in the course of one session.

The encryption takes place with the ENCRYPTBYKEY function, which requires the GUID of the key. Why the GUID and not the name I don’t know, but it seems like a PIA, halfway implementation. In any case, the KEY_GUID function is used as the first parameter.

The number needs to be cast as a character, so I do that first, and then it’s the next parameter in the function. If I look at the data, it looks like this:

select id , firstname , lastname , title , salary , EnryptedSalary
 from Employees; go 

If you use the same identity_value and key_source, you should get the same encryption.

To decrypt it, I do this:

-- decrypt the data, with the casting  select id  , firstname  , lastname  , title  , Salary = cast(cast(DecryptByKey(EnryptedSalary) as nvarchar) as numeric(10,4))  , EnryptedSalary  from Employees go 

The encrypted value has a header that tells it what key is used, so as long as it’s open, this works.