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

Hillel Eilat 1 Reputation point
2021-02-23T09:37:43.007+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,134 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee
    2021-02-23T12:57:00.147+00:00

    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


  2. CathyJi-MSFT 21,111 Reputation points Microsoft Vendor
    2021-02-24T09:48:00.173+00:00

    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.

    71449-screenshot-2021-02-24-175851.jpg

    Please refer to the blog to get more.


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