Pattern matching on encrypted columns

Question

I'm not able to do pattern matching on encrypted column even in SQL Server 2019 version.

SQL Server 2019

BEFORE encryption

select * 
from messageencryption;     

Output:

id    msgcode   msg      
-------------------------------------------
1      AA56B    this is a text message     
2      AA56C    this is a text123 message    
3      AA56D    EXTENDED BOOTCAMP      
4      AA56E    extended bootcamp       
5      AA56J    advance happy new year       
6      AA56K    oneteam       
7      AA56L    cricket team consists of 11 players       
8      AA56M    indian cricket team      

select * 
from messageencryption 
where msg like '%team%';       

Output:

id   msgcode   msg              
----------------------------
6    AA56K     onesmallteam                   
7    AA56L     cricket team consists of 11 players                
8    AA56M     indian cricket team               

AFTER encryption:

select * 
from messageencryption 
where msg like '%team%';

Output

Msg 402, Level 16, State 2, Line 23
The data types varchar(8000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cek', column_encryption_key_database_name = 'encrypt') collation_name = 'Latin1_General_BIN2' and varchar are incompatible in the like operator.

Expected output:

id   msgcode   msg              
----------------------------
6    AA56K     onesmallteam                   
7    AA56L     cricket team consists of 11 players                
8    AA56M     indian cricket team               

Answer 1

It looks like you are using Always Encrypted to encrypt this column. One of the notable limitations in this case is that

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).

In your case you are using RANDOMIZED encryption, so you can't even perform equality comparison!

To do what your query asks, SQL Server engine needs to decrypt all of your data. Let's say it will be inefficient. And since the engine doesn't have access to the encryption keys, this operation must be performed outside of the engine. With SQL Server 2016/2017, this query wasn't even possible to be executed. You had to fetch all the data on the client, perform the decryption and search locally. But since you are using SQL Server 2019, I will assume that you want to take advantage of the new Secure Enclaves functionality. Security Enclaves enables support for rich computations (currently in preview) which will allow pattern matching on encrypted columns. But they are disabled by default and you must explicitly enable them.

First, check are security enclaves supported on your server. Execute this query:

SELECT [name], [value], [value_in_use] FROM sys.configurations
WHERE [name] = 'column encryption enclave type'

If supported, you will get one row (by default with 0 values). To enable it execute:

EXEC sys.sp_configure 'column encryption enclave type', 1
RECONFIGURE

Restart your instance and run the first query to confirm they are enabled. Now you need to enable rich computations on encrypted columns by globally enabling trace flag 127:

DBCC traceon(127,-1)

However, this will not allow you to perform pattern matching on your existing encrypted columns. This can be done only if the columns are encrypted with enclave-enabled column master key (the ENCLAVE_COMPUTATIONS property in the column master key metadata in the database is set). However, the ENCLAVE_COMPUTATIONS property of a column master key is immutable - you cannot change it after the key has been provisioned. So most likely you will need to change the CMK with a new one, which is enclave-enabled (which is specified when you generate a new key).

At the end, I would like to say one more time, that even if this is possible, this doesn't necessary means that you should do it. It is extremely heavy operation. In general pattern matching of strings is heavy, can't use indexes for this type of matching, and now on top of that, all your data must be decrypted! This sounds like really bad idea. Don't do this! If you find yourself trying to search the encrypted data, this usually means your design is flawed. After all, this data should be encrypted for a reason.