How would I encrypt string data in SQL server 2008 while keeping the ability to query over it?

Question

I have a database that will be hosted by a third party. I need to encrypt strings in certain columns, but I do not want to loose the ability to query over the encrypted columns.

I have limited control over the SQL instance (I have control over the database I own, but not to any administrative functions.)

I realize that I can use a .net encryption library to encrypt the data before it is inserted into the table, but I would then loose the ability to query the data with sql.

Answer 1

I like using SQL Server's key management: http://technet.microsoft.com/en-us/library/bb895340.aspx . After you have a key setup then its really easy to use:

To insert records you do this:

insert into PatientTable values ('Pamela','Doc1',
encryptByKey(Key_GUID('secret'),'111-11-1111'),
encryptByKey(Key_GUID('secret'),'Migraine'))

To select the record back out its really simple:

select Id, name, Docname
from PatientTable where SSN=encryptByKey(Key_GUID('secret'),SSN)

The cipher text will always be the same so it is much more efficient to compare the cipher text's instead of going though and decrypting each one.