SQL Server - Assigning values to "Always Encrypted"(=AE) textual columns fails while informing "Operand type clash"

Question

My application shares table definitions across databases for replicating data between them thereafter.

Following table definition stands for an example of a table where that "Operand type clash" fault occurs.

--drop TABLE [dbo].[AEtargetByTypesN]  
CREATE TABLE [dbo].[AEtargetByTypesN](  
    [ky] [int] NOT NULL,  
    [txt] [char](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,  
    [dat] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,  
PRIMARY KEY CLUSTERED   
(  
    [ky] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
GO  

The script that generates that fault is as simple as follows:

go  
truncate table AEtargetByTypesN   
declare @k integer = 4  
declare @t char(20)='Clash?'  
declare @d integer = 1004  

insert into AEtargetByTypesN (ky,txt,dat) values (@k,@t,@d)  

---

Error issued:

Msg 206, Level 16, State 2, Line 1114

Operand type clash:
char(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK2', column_encryption_key_database_name = 'AEdemo')
collation_name = 'Hebrew_CI_AS'
is incompatible with
char(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK2', column_encryption_key_database_name = 'AEdemo')
collation_name = 'Latin1_General_BIN2'
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 1105]
Statement(s) could not be prepared.
An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@pb021e6afd90e4c0c901e6fda53e08a03' in statement or procedure 'TRUNCATE TABLE AEtargetByTypesN;

DECLARE @k AS INT = @pb021e6afd90e4c0c901e6fda53e08a03;

DECLARE @t AS CHAR (20) = @pf3683e782e7e4d70a5eba57e5e0993ad;

DECLARE @d AS INT = @pc94e107a3c62444a9977ff198fb05943;

INSERT INTO AEtargetByTypesN (ky, txt, dat)
VALUES (@k, @t, @d);

' is missing in resultset returned by sp_describe_parameter_encryption.

---

More information:

As observed above - the only attribute that clashes is the COLLATION.
The table definition was mirrored onto the 'AEdemo' target database from a source database having SQL_Latin1_General_CP1_CI_AS as a database COLLATION.
However - the 'AEdemo' target database COLLATION is set as Hebrew_CI_AS.

SELECT [name] as [database_name], collation_name  FROM sys.databases  WHERE name =  'AEdemo';  

database_name collation_name

---

+------------------
AEdemo Hebrew_CI_AS

---

Questions:

• One may guess that COLLATION can be the reason for that clash - is it?
• Can there be other reasons for that?
• No references regarding COLLATION restrictions of that kind could be found with respect to Always Encrypted.
• What does it actually mean "is incompatible with" with respect to COLLATION?
• Is there any documentation explaining the terms of COLLATION-s being compatible with each other?
• Are there any intermediate 'mediating' definitions behind the scenes which are involved in matching COLLATION-s for compatibility? ( This question relates to a 'Compatibility_136_8200_0' reported in an error message, which was inspected once)
• How can all these conditions be monitored and controlled?

And most important: What can I do about this?

Answer 1

collation_name = 'Hebrew_CI_AS'

It is required to use one of the *_BIN2 collations for Always Encrypted text.

Ref: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15#feature-details

Answer 2

Hi @Hillel Eilat ,

Did you enable “Parameterization for Always Encrypted”. Right-click over the query and select “Query Options”, then “Advanced” and enable the option as shown below.

Please refer to the blog to get more.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.