Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Using a Symmetric Key

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 

encryptsymm

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.


Filed under: Blog Tagged: encryption, security, sql server, syndicated, T-SQL

Comments

Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...