Is it possible to use full text search on encrypted column in SQL Server 2008

Question

I have a column, that is encrypted using symmetric key in a database. An encrypted content is just a text. I would like to query this text using full text search. Is it possible? I was thinking about using full text search filters to index a column, but didn't find any ready-to-use filter. So is it possible to develop such a filter, in particular, is it possible to access encryption key, that is stored in a database, from filter code and decrypt the text from the column? Could you recommend any tutorial, how to start with such a development?

Sapless

doesn't that defeat the purpose of the content being encrypted? – Colostomy

That's a good question. I suppose, that just indexing an encrypted text, wouldn't be a security issue, but if I'm wrong, then please, correct me. To be precise. Single words from encrypted contents have no critical business value. Just whole encrypted text should be secured. – Schweiz

But a dictionary attack could expose the contents of all the records even if it had to do so one word at a time. – Gerlac

Leave the data un-encrypted and protect it by other means – Surrealism

Mitch is right: ability to search encrypted text means the encryption is compromised. "no criticial busniess value" should be read as "the clear text can be fully reconstructed by a clever search attack" – Bonded

OK, I will take your comments under consideration. I will think about the risk considering, that ability to search is limited to the small number of privileged and trusted users as well as the search operations can be monitored by the system operator. I will also think about the risk of extraction a full text from search index. But, what I wanted to know is, is it possible at all to full text index encrypted column? For example using custom search filter. – Schweiz

Answer 1

From what I understand, there is no support for encrypted indexes. You basically have two options:

1. You can index partial data in clear (without encryption) and match the partial data to the fully encrypted data.
2. Decrypt the data before searching

Although this post was for SQL Server 2005, it remains true for SQL Server 2008.

Caponize