An Encryption Problem

Here's the scenario: we have a SQL Server database that until recently stored sensitive information in plaintext  (yikes!)  And we have an Access 2003 database in which we built a custom little front-end, with linked tables to the SQL database.  The vendor recently encrypted some of the fields in the database (thanks!) and now we need to update the Access database to comply.

The documentation from the vendor provided a basic example of accessing the encrypted information:

OPEN SYMMETRIC KEY APPKEY DECRYPTION BY CERTIFICATE APPCERT

SELECT CONVERT(VARCHAR,DECRYPTBYKEY(CUSTOMER.SSN)) AS SSN FROM CUSTOMER

That should be a pretty easy change in most code-- just open the key, wrap the encrypted field in a couple of functions, and you're good to go.  But linked tables in Access present an interesting problem.   I'll spare you the laundry list of things I tried, but suffice it to say that lots of approaches almost worked, but nothing quite let me get the key open before calling DecryptByKey.

What saved me was a little function called DecryptByKeyAutoCert, which extracts the private key from the cert and uses it to decrypt the value, all in one fell swoop.  Without the need to open the key separately, I could create a SQL view, and use INSTEAD OF triggers for the updates.  With baited breath, I linked my new view in Access... and it worked!

In short, here's the SQL that makes it all possible:

create view my_customer as
select customerid, fname, lname, convert(varchar(12),DecryptByKeyAutoCert(cert_id('APPCERT'),null,customer.ssn)) as ssn
from customer

create trigger trg_my_customer_upd on my_customer
instead of update as
begin
     open symmetric key APPKEY decryption by certificate APPCERT
     update customer set fname=i.fname, lname=i.lname, ssn=EncryptByKey(key_guid('APPKEY'),i.ssn)
     from inserted i where i.customerid=customer.customerid
     close symmetric key APPKEY
end

create trigger trg_my_customer_ins on my_customer
instead of insert as
begin
     open symmetric key APPKEY decryption by certificate APPCERT
     insert into customer (customerid, fname, lname, ssn)
     select customerid, fname, lname, EncryptByKey(key_guid('APPKEY'),ssn)
     from inserted
     close symmetric key APPKEY
end

With this view created, we can now issue simple SELECT, UPDATE, and INSERT queries against my_customer, treating the ssn column as a plaintext column.  The view decrypts the data as it comes into the resultset, and the triggers ecrypt the data as it goes back into the database.  In short, the my_customer view now acts just like the original customer table.

All we had to do then was to delete the original linked table in Access, link to the new view, and rename the linked table to the original name.  No need to rewrite any other logic in our mini-application!

I simplified a few things here, and in our case the cert was created without a password, but it's pretty easy to add that in if you need do.