Simple demo for how to encrypt and decrypt a table column in SQL Server 2005

For my first post, I decided to make available a very simple demo I wrote recently to demonstrate how data can be encrypted in SQL Server 2005. You can paste the following script in SSMS and execute it line by line while following the comments. The script was written and tested on build 1256.73.

— Goals of this demo:
— Show how a column can be encrypted and decrypted and how an authenticator value can be used

create database demo;

use demo;

— create a simple employee table
create table t_employees (id int primary key, name varchar(300), salary varbinary(300));

— create a key to protect the employee sensitive data, in this case – the salary
create symmetric key sk_employees with algorithm = aes_192 encryption by password = ‘Pufd&s@))%’;

— open the key so that we can use it
open symmetric key sk_employees decryption by password = ‘Pufd&s@))%’;

— verify key was opened
select * from sys.openkeys;

— insert some data
— we will use the id as an authenticator value to tie the salary to the employee id
insert into t_employees values (101, ‘Alice Smith’, encryptbykey(key_guid(‘sk_employees’), ‘$200000’, 1, ‘101’));
insert into t_employees values (102, ‘Bob Jones’, encryptbykey(key_guid(‘sk_employees’), ‘$100000’, 1, ‘102’));

— see the result; salary is encrypted
select * from t_employees;

— create a view to automatically do the decryption
— note that when decrypting we specify that the id should be used as authenticator
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

— see the result, the decrypted data is available
select * from v_employees;

— demo the authenticator role
— copy salary of Alice and overwrite the value for Bob
— execute next 3 lines as batch
declare @salary varbinary(300);
select @salary = salary from t_employees where id = 101;
update t_employees set salary = @salary where id = 102;

— note that both entries have the same salary blob
select * from t_employees;

— see the result, the decrypted data for Bob is no longer available
— because it doesn’t match the authenticator, which is his employee id
select * from v_employees;

— now close the key
close symmetric key sk_employees;

— verify key was closed
select * from sys.openkeys;

— see the result, we can no longer decrypt any data because the key is closed
— to access the data again we would need to reopen the key
select * from v_employees;

— cleanup
drop view v_employees;
delete from t_employees;
drop table t_employees;
drop symmetric key sk_employees;

use master;

drop database demo;

Comments (2)

  1. I was just reading this post on Laurentiu Cristofor’s blog with an encryption sample: Simple demo for…

  2. Hi Don,

    I’m writing this in response to your questions about the additional parameters to encryptbykey. These are already documented in current builds of BOL but they may not appear in older builds. Here’s some information about them if your BOL doesn’t cover them:

    The 3rd parameter of encryptbykey (2nd of decryptbykey) is an int that specifies whether an authenticator value is going to be used. If 1, it indicates that an authenticator value is passed. Default is 0.

    The 4th parameter of encryptbykey (3rd of decryptbykey) is the actual authenticator data. Default is NULL.

    Note that the main purpose of this mechanism is to tie an encryption to a specific row. In my example, I used the primary key of the table for this reason.

    What will happen when using the authenticator is that a hash of the authenticator data and of the cleartext will be computed first and then this will be encrypted together with the cleartext. At decryption time, the server code will verify this hash, if present, and if it doesn’t match (because the wrong authenticator data was passed in to the decryption function), then the decryption will return null.

    Hope this helps.