MS SQL Always Encrypted problem

Has anyone connected to MS SQL Always Encrypted database successfully ?

I am trying to connect to an MS SQL Always Encrypted database but I keep getting errors when inserting into an encrypted column:

The error I get is:
(S0002/206) Operand type clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘CEK_Auto1’, column_encryption_key_database_name = ‘***’)

I have the setting in my JDBC adapter: Column Encryption Setting=enabled

Anyone done this successfully ?

Hi @oliola76 ,

Had not connected earlier with MS SQL Always Encrypted database

But some time ago had come across a Database URL (JDBC Connection) with below parameters. Not sure if it was for MS SQL Always Encrypted database

encrypt=true;EncryptionMethod=SSL;ValidateServerCertificate=false;CryptoProtocolVersion=TLSv1.2

Hello and thank you for your reply.

I have the following parameters in my jdbc connection:
encrypt=true;trustServerCertificate=true; Column Encryption Setting=enabled;

I tried adding the parameters you mentioned but it did not work.

I have a feeling that the problem is somehow related to the encryption keys and webMethods not using them correctly (or using them at all), but I can’t be sure.

The error likely occurs due to a mismatch between the column’s defined size and the data you’re inserting. Ensure the column data type and size match exactly, and confirm that the Always Encrypted settings are properly configured in both the JDBC driver and SQL Server. Also, verify that the JDBC driver version supports Always Encrypted and that the correct encryption key is being used.

Yes, you need to ensure that your driver supports Always Encrypted and that Column Encryption Setting is enabled. Also, make sure that the parameter types in your query match the encrypted column definitions exactly. This includes specifying the correct length for nvarchar types in your SQL statements. Double-check that the JDBC driver version you’re using is up to date and supports Always Encrypted features.

1 Like

Thank you very much for your reply, I found out that there was a missing dll that was causing this error to occur.
The missing DLL was: mssql-jdbc_auth-12.2.0.x64.dll which was not a part of the library we were using
After fixing that I hit another wall and am working on solving this.

2 Likes