0

I'm working on an enterprise application and for some security reason I have to enable Always Encrypted. DBA and Production IT team did what needed from their side:

I can generate this script from using SQL server management studio under security -> always encrypted key

USE [DE_NAME]
/****** Object:  ColumnMasterKey [*****]    Script Date: 23/12/2022 17:30:17 ******/
CREATE COLUMN MASTER KEY [*****]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
    KEY_PATH = N'https://xxxxxxxxx.vault.azure.net/keys/******/####'
)
GO

What mean that Alaways Encrypted is configured in the DB?

Production IT team :

  • Create Keyvault
  • Create a Service Principal/application Registration in Azure AD
  • register Name(clientId) and secret in KeyPass
  • At keyvault level added role Assignement

From my side, I added the conf as descripted in Microsoft documentation:

https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver16

Application inf :

  • Spring boot
  • Java 8
  • mssql diver : 9.2.1.jre8
  • I added mssql-jdbc_auth-9.2.1.x64.dll and sqljdbc_auth.dll to -Djava.library.path

Test code :

 try (Connection sourceConnection = DriverManager.getConnection("jdbc:sqlserver://server;DatabaseName=db_name;encrypt=true;trustServerCertificate=true;integratedSecurity=true;columnEncryptionSetting=Enabled;keyVaultProviderClientId=xxxxxxxx-xxxx-xxxx-xxxx-xx71xx1axxd3;keyVaultProviderClientKey=XXX7X~XXXMv21mXqXxX4FBi7XoXmxxxXDSZXw")) {
    
    PreparedStatement stmt = sourceConnection.prepareStatement("INSERT INTO Table_name(id, aaa, bbb, ccc, ddd, eee) VALUES (?, ?, ?, ?, ?, ?)");

    stmt.setInt(1, 999999);
    stmt.setString(2, "local_test_0");
    stmt.setInt(3, 989898);
    stmt.setString(4, "local_test_0");
    stmt.setString(5, "local_test_0");
    stmt.setString(6, "local_test_0");//col encrypted

    stmt.executeUpdate();

} catch (SQLException e) {
    e.printStackTrace();
}

When I run this code I get the following error message :

com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: varchar(12) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK-XXXX-Shared-XX-XX', column_encryption_key_database_name = 'XXXX') collation_name = 'SQL_Latin1_General_CP1_CI_AS' is incompatible with nvarchar(132) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK-XXXX-Shared-XX-XX', column_encryption_key_database_name = 'XXXX')
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)

It's like the driver didn't encrypt data before inserting it.

Could you help please ?

Bhavani
  • 1,725
  • 1
  • 3
  • 6
M-BNCH
  • 393
  • 1
  • 3
  • 18
  • 1
    When working with Always Encrypted you have to be exactly correct with your data types - not only data type but length/size must also match - otherwise you'll get errors such as the one you're seeing where `varchar(12)` != `nvarchar(132)`. – AlwaysLearning Dec 26 '22 at 00:29
  • 1
    If `aaa` on the database side is of type `nvarchar(132)` then what happens if you change `stmt.setString(2, "local_test_0");` to `stmt.setNString(2, "local_test_0");` (note the `N`) or even better: `stmt.setObject(2, "local_test_0", java.sql.Types.NVARCHAR, 264);`? (NOTE: the `scaleOrLength` parameter here is measured in bytes, so 132 x UCS-2 characters == 264 bytes.) – AlwaysLearning Dec 26 '22 at 00:32
  • @AlwaysLearning Thank you, it was that. I made the change, it's working now. Tested for select query, waiting for write right from dba to check insert but it's clear that the problem is resolved. Thanks again – M-BNCH Dec 26 '22 at 18:11

0 Answers0