SQL Server always encrypted and JpaRepository BigDecimal datatype in Spring Boot/Hibernate

[syahmiabbas]

Driver version

12.2.0.jre11

SQL Server version

Microsoft SQL Azure (RTM) - 12.0.2000.8

Client Operating System

Microsoft Windows Server 2022 Datacenter Azure Edition

JAVA/JVM version

Java version "20.0.1"

Table schema

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [HR].[Employees](
[EmployeeID] [int] NOT NULL,
[FirstName] [varchar](20) NULL,
[LastName] [varchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [cdc-predev-CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Salary] [decimal](20, 4) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [cdc-predev-CEK], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[SSN] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Problem description

We have problems inserting data into an always encrypted decimal/numeric column, as it gives an error of mismatch datatypes. Currently for our table schema defined below we are using BigDecimal type for the decimal/numeric column needing to be generated. We have also generated the tables in MSSQL with hibernate so we expected that inserting data, it will be mapped correctly.

For our table, we have enabled always encryption encrypted using Azure Key Vault for lastName and salary. For the VARCHAR column of lastName, even with encryption enabled, it has no issues having varying lengths of data. When we disabled the always encryption for the salary column, insertion in the table has no issues. Since our data, being salary, which will have varying values, we require this flexibility.

Currently our JPA Entity file looks like this:

import lombok.Data;

import java.math.BigDecimal;

import javax.persistence.Column;
import javax.persistence.Entity;
import [javax.persistence.Id](http://javax.persistence.id/);
import javax.persistence.Table;
import javax.validation.constraints.Size;

@Entity
@Table(name = "Employees", schema = "HR")
@Data
public class BCNEmployeeEntity {

@Id
@Column(name = "EmployeeID")
private Integer employeeID;

@Column(name = "SSN")
@Size(max = 50)
private String ssn;

@Column(name = "FirstName")
@Size(max = 20)
private String firstName;

@Column(name = "LastName")
@Size(max = 20)
private String lastName;

@Column(name = "Salary", columnDefinition="Decimal(20,4)", precision=20, scale=4)
private BigDecimal salary;

}

Here's a sample json body value that we are using to insert into the database:

{
"employeeID": 2,
"firstName": "POC",
"lastName": "Test",
"ssn": "1234",
"salary":166666.5112
}

Expected behavior

We expect the MSSQL JDBC being able to encrypt the salary value sent that can be varying values and able to match to the decimal datatype with the same precision and scale in the MSSQL database, as the the creation of the MSSQL tables were created by hibernate.

Actual behavior

For the string column encrypted for lastName has no issues encrypting, but for the decimal(20,4) column encrypted salary has errors storing the encrypted data. The first part of the error for the data sent "Operand type clash: numeric(18,4)", changes sizes as well depending on the value of the data being sent.

Error message/stack trace

2023-07-18 03:28:40.690 DEBUG 5628 --- [nio-8081-exec-3] org.hibernate.SQL : select bcnemploye0_.EmployeeID as employee1_1_0_, bcnemploye0_.FirstName as firstnam2_1_0_, bcnemploye0_.LastName as lastname3_1_0_, bcnemploye0_.Salary as salary4_1_0_, bcnemploye0_.SSN as ssn5_1_0_ from HR.Employees bcnemploye0_ where bcnemploye0_.EmployeeID=?
Hibernate: select bcnemploye0_.EmployeeID as employee1_1_0_, bcnemploye0_.FirstName as firstnam2_1_0_, bcnemploye0_.LastName as lastname3_1_0_, bcnemploye0_.Salary as salary4_1_0_, bcnemploye0_.SSN as ssn5_1_0_ from HR.Employees bcnemploye0_ where bcnemploye0_.EmployeeID=?
2023-07-18 03:28:40.786 DEBUG 5628 --- [nio-8081-exec-3] org.hibernate.SQL : insert into HR.Employees (FirstName, LastName, Salary, SSN, EmployeeID) values (?, ?, ?, ?, ?)
Hibernate: insert into HR.Employees (FirstName, LastName, Salary, SSN, EmployeeID) values (?, ?, ?, ?, ?)
2023-07-18 03:28:40.796 WARN 5628 --- [nio-8081-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 206, SQLState: S0002
2023-07-18 03:28:40.797 ERROR 5628 --- [nio-8081-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : Operand type clash: decimal(18,4) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cdc-predev-CEK', column_encryption_key_database_name = 'cdc-predev-SQL') is incompatible with decimal(20,4) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'cdc-predev-CEK', column_encryption_key_database_name = 'cdc-predev-SQL')
2023-07-18 03:28:40.818 ERROR 5628 --- [nio-8081-exec-3] c.p.b.s.db.BCNEmployeeDBServiceImpl : Exception occurred.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException([HibernateJpaDialect.java:259](http://hibernatejpadialect.java:259/)) ~[spring-orm-5.3.27.jar:5.3.27]

Any other details that can be helpful

This similar issue without any resolution was also brought out here previously: https://github.com/microsoft/mssql-jdbc/issues/1484

[tkyc]

Just an fyi for team members, this is a duplicate of an internally tracked support issue.

[tkyc]

This issue might be a regression because of a prior fix we did for big decimal. Could you try out the 11.2.3 version of the driver and confirm if your still seeing the problem while I investigate this on my end.

[tkyc]

Nevermind, I just attempted a repro with 11.2.3 and I'm still seeing the error.

[tkyc]

Are you using the "setBigDecimal" function to set the value? If so, you try using the "setValue" function instead to set the salary parameter eg. setValue(salaryParamIndex, JDBCType.DECIMAL, salary, JavaType.BIGDECIMAL, 20, 4, false); as a workaround. I realize you're using Spring so that might not be viable if that's not within your control.

So far from investigating, this issue is for sure a problem with the driver's logic in building type definitions for BigDecimal. I'm surprised that this has gone so long like this without more noise on the problem as this issue has been in the driver since day 1.

[tkyc]

So it looks like you'll need to specify the precision and scale when using AE with BigDecimal. This seems appropriate since using AE you'll need to be precise. Otherwise, the driver will try to make a "guess" as to what the precision and scale should be. The only way for the driver to know the precision and scale is either the user provides that information or the driver makes a column metadata query for that info (which we don't). So, either setValue or setBigDecimal methods will need to be supplied with the exact precision and scale of the column definition. Again, I'm not sure if Spring allows fine control over this (I see in the column annotation for salary the precision and scale is also defined there, but it doesn't look like there's any impact). I'll update our docs on this behaviour, as when I was exploring myself, I wasn't exactly sure and it wasn't quite obvious if this was correct behaviour or not in the driver. Let me know if you have any further questions, otherwise I'll be closing this on my end in a week or so.

[syahmiabbas]

Hi @tkyc,

For the suggestion mentioned for setValue or setBigDecimal methods, this is not a possible approach, as for our current project, it requires the maintainability of schemas using Spring JPA/Spring Data, then utilizing lombok library for our data/field handling and using mapstruct for our mapping.

From our testing we found that with Always Encryption disabled, the insertion of data though JPA with BigDecimal for the salary column with the same configurations has no issues. We have previously used the same approach of Spring Data/JPA with BigDecimal for other drivers like Postgres and H2, and have no issues as well, same tech stack was used, only difference is that we are using custom encryption for our always encrypted columns.

Here's the table creation with Always Encryption disabled that works for the insertion:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [HR].[Employees](
    [EmployeeID] [int] NOT NULL,
    [FirstName] [varchar](20) NULL,
    [LastName] [varchar](20) NULL,
    [Salary] [decimal](20, 4) NULL,
    [SSN] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Since the same approach - using same tech stack(Spring JPA/Data) for previous microservices which is connecting to other databases was all good, we believe this issue is isolated only to MS SQL - Always encrypted, as again connecting to MS SQL using the sql driver without always encrypted is working fine.

[tkyc]

The problem here is that when using Always Encrypted, the column precision and scale is involved in the encryption. The driver needs to send the exact precision and scale to the server in order for it to be valid under Always Encrypted. SQL Server expects this information when Always Encrypted is used. Unless the server relaxes the encryption criteria, the driver can only do so much in this situation. So, either precision and scale needs to be provided or the driver needs to query the server for this info. We don't query for the column metadata because of the extra overhead.

[syahmiabbas]

In our case we have passed in the precision and scale as shown from the spring JPA entity configuration shown above. From our testing it does send over the precision and scale when tested with Always Encrypted disabled, and it works correctly.

As mentioned the MSSQL - Always Encrypted is the main problem as only when Always Encrypted is enabled the precision and scale has a mismatch.

[tkyc]

...when Always Encrypted is enabled the precision and scale has a mismatch.

There's a mismatch because the driver doesn't know the precision and scale and is using the defaults. Whatever is happening in the background of your application (in Spring or JPA), it's not setting the precision and scale.

...we have passed in the precision and scale as shown from the spring JPA entity configuration... From our testing it does send over the precision and scale when tested with Always Encrypted disabled

Right now, I'm suspecting your application isn't sending the precision and scale (despite you claiming that it is). One way to confirm for sure is to do a SQL profile trace on your application. Could you provide a profile trace file from using SQL Server Profiler on an insert? Or, can you provide a reproduction application (with Spring and JPA dependencies, and any application instructions to get it running) for me to run and do so on my end? Thanks.

[syahmiabbas]

Hi @tkyc,

We have double checked on the precision and scale as you mentioned and confirmed that it may be due to JPA's limitation of sending over precision and scale for BigDecimal. The precision and scale set in the JPA above is a validation for the application and it is not sent when using the driver. This then leads to the problem as mentioned with the driver not being able to detect the precision and scale, and the driver will try to make a "guess" as to what the precision and scale should be.

Thank you again for the help.

[tkyc]

@syahmiabbas Thanks for the update. Glad to hear my suspicions were confirmed.