Laurentiu Cristofor's blog : 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;
-- EOD

Laurentiu Cristofor's blog : Simple demo for how to encrypt and decrypt a table column in SQL Server 2005