How to use Like Operator on SQL Server Always Encrypted column?

Question

Please help me in fixing the below query in SQL Sever 2016 where columns FIRST_NAME, LAST_NAME are always encrypted.

SELECT * FROM MY_TABLE
WHERE (LAST_NAME LIKE '%[@,#,$,%,*]%' OR LAST_NAME LIKE '%,%')
OR (FIRST_NAME LIKE '%[@,#,$,%,*]%' OR FIRST_NAME LIKE '%,%');

I am getting the below error as the columns are always encrypted and also I can't declare a variable and assign the string to it.

Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_POC_CERTSTORE', column_encryption_key_database_name = 'UIM-LOCAL-DB') collation_name = 'Latin1_General_BIN2' is incompatible with varchar

Answer 1

Unfortunately if FIRST_NAME and LAST_NAME are Always Encrypted, then you cannot use LIKE operator to search the string.

Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (for example, greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

Reference: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

Answer 2

As of SQL Server 2019, the Secure Enclaves feature allows full querying of Always Encrypted columns -- pattern matching with LIKE, ranges, and sorting -- including randomized columns (not just deterministic).

Without using Secure Enclaves, queries are limited to searching for an exact match. (Always Encrypted fields can also be used in JOINs, GROUP BY, and DISTINCT.)

Microsoft introduction to secure enclaves:

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver15

[This link replaces the previous RedGate link which unfortunately disappeared]