question

HillelEilat-6943 avatar image
0 Votes"
HillelEilat-6943 asked ·

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

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-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeanGallardy-MSFT avatar image
0 Votes"
SeanGallardy-MSFT answered ·

collation_name = 'Hebrew_CI_AS'

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

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

· 1 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Sean.

The fact that "Always Encrypted" mandates using *_BIN2 collation is well known.
Indeed - the COLUMN in question is qualified as "COLLATE Latin1_General_BIN2", as required.
Reading my question carefully - the point is that when the DATABASE collation is 'Hebrew_CI_AS' - a clash occurs.
If the DATABASE collation is set to - say - SQL_Latin1_General_CP1_CI_AS - it may work fine.
This conflicting case is not mentioned in the URL given in your answer.
Neither can one understand what is the point here.

Thanks.

Hillel.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

Hi @HillelEilat-6943,

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.



· 1 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Cathy

^^Did you enable “Parameterization ... ”.

Sure I did!


Look - I am far beyond all these setup issues.
The fault is clear and is reproduced in a consistent manner-gap between COLLATION-s


Reproduce by:

1. Set data base COLLATION to Hebrew_CI_AS


 USE master;  
 GO  
 ALTER DATABASE AEdemo  COLLATE Hebrew_CI_AS;  
 GO  
 USE AEdemo;

2. Create a Column Encryption Key - CEK2


3. Create the table as described above.


Note The column in question is qualified as [char](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH ...

 [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,

4. Issue the INSERT DML sequence


 declare @k integer = 4
 declare @t char(20)='Clash?'
 declare @d integer = 1004
    
 insert into AEtargetByTypesN (ky,txt,dat) values (@k,@t,@d)
















0 Votes 0 ·