Automatic Decryption of Encrypted Data

Hi

Couple of days ago a colleague DBA presented me with a problem. He has a data warehouse with encrypted data, and he wants to run reports against it. The problem is, he has a reporting tool (Business Object) that issues SQL Statements against the database, and he has no way of adding the OPEN KEY statement to the statements that the reporting tool use.

I was able to solve his problem using two SQL Server features.
The first thing was to create views that perform the decrypt operation on all the encrypted columns, and have the reporting tool query the views instead of the base tables.
The second step was to implement a DDL trigger that will run when any user logs into SQL Server. That trigger will check if the login request is made by the login used by the reporting tool, and if so, it will call a stored procedure that will issue the OPEN KEY statement.

Here is the sample code:
Creating the key and test data

use db_test
go
create symmetric key key1 with ALGORITHM = DES encryption by password='P@ssword'
go
create table T (a int, b varchar(100), c varbinary(8000))
go
open symmetric key key1 decryption by password='P@ssword'
insert into T select 1,'paul',encryptbykey(key_guid('key1'),'Paul')
insert into T select 2,'john',encryptbykey(key_guid('key1'),'john')
close all symmetric keys
go

grant select on database::db_test to public
grant CONTROL on database::db_test to public

Create the login and the user used for testing
create login George with password='k', DEFAULT_DATABASE = db_test, check_policy=off
go
create user George for login George
go

Create the procedure that opens the key
create procedure p_OpenKey
as
begin
open symmetric key key1 decryption by password='P@ssword'
end
go

Create the DDL trigger
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
begin try
IF ORIGINAL_LOGIN()= 'George'
begin
exec db_Test.dbo.p_OpenKey;
print 'Hello, George'
end
else
begin
print 'Hello,'+ORIGINAL_LOGIN()
end
end try
begin catch
print ERROR_Message()
rollback;
end catch
END;

There are couple of things to note in the code:
1) The p_OpenKey stored procedure must be created in the database that contains the tables and the key.
2) The reason the OPEN KEY statement is issued in the procedure and not in the trigger is because the trigger runs in the context of the master database, so the key does not exists there.
3) The rollback statement in the DDL trigger makes sure that if we were not able to open the key for some reason, the login request will be denied. This is not mandatory of course, even if the OPEN KEY fails, the user can still work, he just won't get the data decrypted.

The only thing left is to create views on the table and use the DecryptByKey function in order to decrypt the data.

Take Care