Operand Type Clash when inserting data into a Table with AlwaysEncrypted Columns

[srirampvn]

Driver version or jar name

mssql-jdbc-6.2.1.jre8

SQL Server version

Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64) Apr 27 2017 17:36:12
Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard Evaluation 6.3 (Build 9600: )

Client operating system

Linux centos-gs-79 2.6.32-642.4.2.el6.x86_64 #1 SMP Tue Aug 23 19:58:13 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

Java/JVM version

1.8.0_102-b14

Table schema

Always Encrypted - in memory table created using Java code with Java Key store Provider MSSQL_JAVA_KEYSTORE

Table schema details

Always Encrypted - in memory table created using Java code with Java Key store Provider MSSQL_JAVA_KEYSTORE

Table definition

CREATE TABLE [dbo].[GSSW_BZDAT_CL0859_INST_MEM_20170801]
(
[BATCH_ID] nvarchar COLLATE Latin1_General_CI_AI NOT NULL,
[CLIENT_INST_REF_ID] [bigint] NOT NULL,
[INST_STTS] varchar COLLATE Latin1_General_CI_AI NULL,
[INST_STTS_DT] datetime2 NULL,
[INST_STTS_TZ] [int] NULL,
[CLIENT_INST_REF] nvarchar COLLATE Latin1_General_CI_AI NULL,
[INST_AMT] [decimal](18, 3) NULL,
[PSP01_VLDT_STTS] varchar COLLATE Latin1_General_CI_AI NULL,
[PAYOUT_AMT] [decimal](18, 3) NULL,
[CL_FIX_FEE_AMT] [decimal](18, 3) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[CL_VAR_FEE_AMT] [decimal](18, 3) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[CL_VAR_FEE_RATE] [decimal](18, 7) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[GS_FEE_ASSESS_DT] datetime2 NULL,
[GS_FEE_ASSESS_TZ] [int] NULL,
[FEE_COLL_DT] datetime2 NULL,
[FEE_COLL_TZ] [int] NULL,
[FEE_PROF_NAME] varchar COLLATE Latin1_General_CI_AI NULL,
[BENEF_TYPE] varchar COLLATE Latin1_General_CI_AI NULL,
[BENEF_IDENT] varchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BENEF_CO_REG_NUM] varchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BENEF_BANK_ACCT_NUM] varchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BENEF_BANK_ACCT_NAME] nvarchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BENEF_BANK_NAME] nvarchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BENEF_BANK_BRANCH_NAME] nvarchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [JDBC_CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[BENEF_BANK_BRANCH_CITY] nvarchar COLLATE Latin1_General_CI_AI NULL,
[BENEF_BANK_BRANCH_PROVINCE] nvarchar COLLATE Latin1_General_CI_AI NULL,
[INSERT_DT] datetime2 NULL,
[INSERT_TZ] [int] NULL,
[UPDATE_COUNT] [int] NULL,
INDEX [GSSW_BZDAT_CL0859_INST_MEM_20170801_BATCH_IDX] NONCLUSTERED
(
[BATCH_ID] ASC
),
CONSTRAINT [PK_GSSW_BZDAT_CL0859_INST_MEM_20170801] PRIMARY KEY NONCLUSTERED HASH
(
[BATCH_ID],
[CLIENT_INST_REF_ID]
)WITH ( BUCKET_COUNT = 2097152),
CONSTRAINT [UK_GSSW_BZDAT_CL0859_INST_MEM_20170801] UNIQUE NONCLUSTERED
(
[CLIENT_INST_REF] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

Problem description

• Trying to insert data into the above table using JDBC Batch Insert operation. The Batch Insert operation fails sometimes with the below Java exception.

java.sql.BatchUpdateException: Operand type clash: numeric(18,3) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'JDBC_CEK', column_encryption_key_database_name = 'XXX') is incompatible with decimal
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2303)

We were able to identify the issue that the issue was with columns PAYOUT_AMT and INST_AMT which are decimal columns with AlwaysEncrypted Not enabled on these columns

JDBC URL used:
jdbc:sqlserver://hostname:1433;databaseName=databaseName;columnEncryptionSetting=Enabled;keyStoreLocation=/DBKeys/MSSQLAlwaysEnctyptedKeyStoreTest.jks;keyStoreSecret=MSSQLAlwaysEnctyptedKeyStoreTest;keyStoreAuthentication=JavaKeyStorePassword;

Expected behavior and actual behavior

Batch Insert operation should succeed without error.

Actual behaviour is an occasional failure of batch Insert in the Always Encrypted table.
The JDBC driver is providing encrypted data for the non-encrypted column during Insert as per the exception provided as per error message, Operand type clash: numeric(18,3) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'JDBC_CEK', column_encryption_key_database_name = 'XXX') is incompatible with decimal.

This exception does not occur always and occurs only randomly. If we use the same code and same data to insert data into another table with the same structure, there is no issues.

Repro code

public static void psSetMoneyAsBigDec(PreparedStatement ps, int idx, Money val, BigDecimal def) throws SQLException {
if (val==null && def==null) {
ps.setObject(idx, null);
return;
}

    if (val==null && def != null) {
        ps.setBigDecimal(idx, def);
    }
    else {
        BigDecimal bd = Money.toBigDecimal(val);; 
        ps.setBigDecimal(idx, bd);
    }
}

[AfsanehR-zz]

Hello @srirampvn . We were not able to reproduce the issue you created. Could you track down inserting what value causes this issue? Also could you make sure that always Encrypted is enabled on the connection?
Another recommendation is to specify the proper precision and scales when inserting using ps.setBigDecimal.

[AfsanehR-zz]

Hi @srirampvn . Just wanted to follow up if the issue still exists? Thanks!

[ajlam]

Closing issue due to inactivity. @srirampvn, please reopen the issue if you still encounter it.