Enclave-enabled like not working for Deterministic

Question

We are using Azure SQL on cloud with the Enclave-enabled.

A table created by:

CREATE TABLE [dbo].[both] (

[id] INT IDENTITY(1,1) PRIMARY KEY,

[ran] [varchar](100) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,

[determ] VARCHAR(100) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL

);

declare @ran varchar(10) = 'rr';

declare @det varchar(10) = 'dd';

insert into both (ran, determ) values (@ran, @det);

select * from dbo.both where determ like @det;

error:

Msg 33277, Level 16, State 2, Line 17

Encryption scheme mismatch for columns/variables 'determ', '@det'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'en2') and the expression near line '7' expects it to be RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 10]

Statement(s) could not be prepared.

An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p4da10ebebef5489c91243a7db8ba54f6' in statement or procedure 'DECLARE @ran AS VARCHAR (10) = @p4da10ebebef5489c91243a7db8ba54f6;

DECLARE @det AS VARCHAR (10) = @p275ee05cd3c24dfa9f04e1aee906933d;

SELECT *

FROM dbo.both

WHERE determ LIKE @det;

' is missing in resultset returned by sp_describe_parameter_encryption.

is there way to let Deterministic work for like and = with and without enclaved?

Answer 1

I also had severe problems to get your example to work, and it took me quite a while to figure out why. However, looking at the your error message, it does not match the message I got. So you may be running into something else.

Eventually, this ran successfully for me:

DROP TABLE IF EXISTS both
CREATE TABLE dbo.both (
      id int IDENTITY(1,1) PRIMARY KEY,
      random  varchar(100) COLLATE DATABASE_DEFAULT
           ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Auto1, 
           ENCRYPTION_TYPE = RANDOMIZED, 
           ALGORITHM       = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
     determ varchar(100) COLLATE Latin1_General_100_BIN2_UTF8
          ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Auto1, 
          ENCRYPTION_TYPE = Deterministic, 
          ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);
go
declare @random varchar(100) = 'rr';
declare @determ varchar(100) = 'dd';
insert into both (random, determ) values (@random, @determ);

The key was that I have a UTF8 collation as my database collation, and with varchar, the code page of the database collation must match the code page of the collation of the encrypted column. For nvarchar this is not an issue. (And, unless you are using a UTF-8 collation, you should use nvarchar for string data in most cases.)

But since you get a different error message, this may not apply to you. But try nvarchar anyway and check your database collation.

Also, make sure that you have checked this option in SSMS:

Then again, as we already have discussed there is little reason to use deterministic encryption when you have enclaves, so go for randomised.