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

You could generate a hash (such as Md5 ) and store the hash value in the db. When you query you can select * from [my table] where value = {md5 hash}

Answer 2

if you use the same encryption key you could encrypt your search query string and match against that. Say my password is runrun i encrypt it to ZAXCXCATXCATXCA then when i want to search for a user with password runrun encrypt it first and it will match the table entry.

Answer 3

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.

Answer 4

AFAIK, Most RDBMS do not support this, what I usually see is either:

A) The DB query API encrypts the data with a key that only the local server knows before it is sent to the remote db and decrypts when it's received.

or

B) The remote database stores everything encrypted with a key that it knows (probably at run time, given physically by an admin, or it's given the key with the query).

A will let you use the database without letting the owners know what's being stored, but you wont be able to do queries on the actual encrypted data other than maybe equality. B only protects against physical server theft (server has to be off though or they can get the key from memory...).

What I assume you want is called Private Information Retrieval. It's a fairly young field, I don't think you're going to find a decent implementation at the moment.