collation_name = 'Hebrew_CI_AS'
It is required to use one of the *_BIN2 collations for Always Encrypted text.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
My application shares table definitions across databases for replicating data between them thereafter.
--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
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)
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.
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
collation_name = 'Hebrew_CI_AS'
It is required to use one of the *_BIN2 collations for Always Encrypted text.
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.