How would I encrypt string data in SQL server 2008 while keeping the ability to query over it?
cryptography, encryption, security, sql-server
Solution
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.
Problem
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.