Encryption and Full Text Search?

Topics: Cryptography Application Block
Feb 11, 2007 at 10:31 PM
Edited Feb 12, 2007 at 1:27 AM
I'm writing an app for a client who wants their data from a certain text field to be encrypted in the database, AND still fully searchable.

My plan is to parse the string to identify each word, then encrypt it word by word, before saving to the database. The FTS index gets populated with the encrypted words. When a search is done from my ASP.NET page, it encrypts each word of the search term before doing a full text search in SQL Server 2005.

One barrier is that the initialization vector (IV) will change between when a row was encrypted, and when the search terms are created and encrypted - thus they'll never match. Any ideas on how to keep the IV consistent? I know this is not as secure, but I don't see any other good way to be able to do full text search with encryption.