STEFANO DEMILIANI

SQL Server 2005 Cryptography and Varbinary

The first CTP of SQL Server 2005 SP1 was released yesterday and it promises to correct bugs and add (or complete) features present on the actual build of SQL 2005 (for example full database mirroring), but at the last SQL Server 2005 Workshop we had 2 days ago on Microsoft Italy, I've listen that someone is waiting for a patch that corrects a problem (??) that affects an interesting SQL Server 2005 feature: cryptography.

The problem is essentially that you're forced to declare a column type of Varbinary on colums that you want to crypt.

Is this a problem? Why are there so many people that thinks so? Encrypted data are binary data, so the table's column where you want to store the value uses varbinary fields to hold it (or Varbinary(max)). I think it's correct, you can't store a crypted value on plain text I think... What do you think? Is this a problem or not?

To show what I mean, check this sample that we've seen on the Workshop (I've adapted it only to quicly show the feature):

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 = des 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 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 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;

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

select * from v_employees;

drop view v_employees;

delete from t_employees;

drop table t_employees;

drop symmetric key sk_employees;

use master;

drop database demo;

Cryptography is a powerful feature embedded into SQL Server 2005. Obviously, I don't think it's so complete and more work on this new functionality can be done in the future, but I can't see the problem on the Varbinary field.

Print | posted on Friday, March 17, 2006 9:51 AM