Indexing an encrypted column in sql server

Question

I have patient health information stored in a SQL Server 2012 database. When I search by patient name, their names are encrypted, so the search is very slow. How to add an index to an encrypted column?

I use Symmetric Key encryption (256-bit AES) for varbinary fields.

There are separate encrypted fields for patient name, surname, address, phone number, DOB, SSN. All of them are searchable (partially), except for SSN.

Answer 1

To build the answer that @PhillipH provided: if you do an exact search by (say) last name, you can include a computed column defined as CHECKSUM(encrypt(last_name))(using encryptyour encryption operation). This is safe in that it does not divulge any information - the checksum of the encrypted value does not report anything in plain text.

. , , WHERE encrypted_last_name = encrypt(last_name), : WHERE encrypted_last_name = encrypt(last_name) AND CHECKSUM(encrypt(last_name)) = hashed_encrypted_last_name. , SQL Server , , , . , CHECKSUM - . , / ( , , ), , .

, , , . ( encrypted(TOUPPER(name)), , , ), . , , - , Lucene, , (.. (EFS) Windows). , , , - , SQL Server .

/ , (TDE), , SQL Server . , , ( ), , , . , TDE .

Answer 2

, , clear / . , .