I am looking to apply SQL column level encryption using symmetric keys. The initial steps needed to create the Database Master Key, Certificates and Symmetric Keys seems straight forward and I have tested encrypting/decrypting data using Symmetric Keys successfully.
However, once the data is encrypted I don't know how best to query it. E.g.
SELECT PlainTextA, PlainTextB, PlainTextC
WHERE CONVERT(varchar, DECRYPTBYKEY(EncyptedColumn)) = @SearchTerm
would surely result in a full table scan?
Another option I thought might work is encrypting the search criteria first e.g.
SELECT PlainTextA, PlainTextB, PlainTextC
WHERE EncyptedColumn = ENCRYPTBYKEY(KEY_GUID('KeyName'), @SearchTerm)
but this doesn't work as the encrypted value generated is always different.
Any suggestions would be greatly appreciated.