3

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
abcreddy
  • 102
  • 3
  • 19
  • Isn't the point of an encrypted column so you can't query the data without decrypting? Perhaps I'm misunderstanding. – Jacob H Jan 26 '18 at 16:21
  • Well, you can't. The server doesn't *have* the encryption key, so it cannot decrypt the columns to perform the wildcard match server-side, and there is no way to rewrite the `LIKE` operator internally so it somehow works with encrypted data. – Jeroen Mostert Jan 26 '18 at 16:21
  • One possible way to do this would be to have the client compute the `WHERE` on every insert/update, and store its outcome (yes/no) as a flag, so you just do `WHERE names_contain_special_characters = 1`. Then the encryption of the column no longer matters. This works in this case only because the `LIKE` patterns are constants (and a change in the pattern means you'd have to recalculate all the columns). – Jeroen Mostert Jan 26 '18 at 16:26

2 Answers2

5

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

Kia
  • 51
  • 1
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]

Dana
  • 634
  • 7
  • 12