0

I am working on mule 3.8 community edition, I am simply trying to insert the data in mssql database whose table having "Always Encrypted" enabled. I have written parametrized query under execute dll option in mulesoft database connector. I have also mentioned in connection string present in mule-app.properties as "columnEncryptionSetting=Enabled"

After executing the code I got below error.

Message               : Encryption scheme mismatch for columns/variables '@deliveryheader_shiptotelephonenumber'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '5' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Column_data_encrypt', column_encryption_key_database_name = 'Encrypt_db') (or weaker).  (com.microsoft.sqlserver.jdbc.SQLServerException).
Payload               : {NullPayload}
Payload Type          : org.mule.transport.NullPayload
Element               : /encryptionpocFlow/processors/0 @ encryptionpoc:encryptionpoc.xml:20
Element XML           : <db:execute-ddl config-ref="db_Sql_Configuration" doc:name="Database">
                        <db:dynamic-query>DECLARE @deliveryheader_shiptotelephonenumber varchar(30)='1234567890' DECLARE @deliveryheader_shiptoaddress_address1 varchar(100)='3807 ABCDE XYZS PKWY'
DECLARE @deliveryheader_shiptoaddress_address2 varchar(100)='SUITE 102' DECLARE @deliveryheader_shiptoaddress_city varchar(60)='PQRSE'
DECLARE @deliveryheader_shiptoaddress_county varchar(60)='LMNOP'

INSERT INTO [dbo].[encrypted_table] (deliveryheader_shiptotelephonenumber ,deliveryheader_shiptoaddress_address1,deliveryheader_shiptoaddress_address2 ,deliveryheader_shiptoaddress_city,deliveryheader_shiptoaddress_county) VALUES(@deliveryheader_shiptotelephonenumber,@deliveryheader_shiptoaddress_address1 ,@deliveryheader_shiptoaddress_address2,@deliveryheader_shiptoaddress_city ,@deliveryheader_shiptoaddress_county)</db:dynamic-query>
                        </db:execute-ddl>
--------------------------------------------------------------------------------
Root Exception stack trace:
com.microsoft.sqlserver.jdbc.SQLServerException: Encryption scheme mismatch for columns/variables '@deliveryheader_shiptotelephonenumber'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '5' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Column_data_encrypt', column_encryption_key_database_name = 'Encrypt_db') (or weaker). 
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752)....

Please help me on this if anything i am missing or any changes required.

aled
  • 21,330
  • 3
  • 27
  • 34
Shubham_Relekar
  • 9
  • 2

1 Answers1

0

That is an error returned from MS SQL. I'm not sure @DECLARE statements are valid in a query from the Mule database connector. Try removing them and pass the values directly from Mule payload/variables.

aled
  • 21,330
  • 3
  • 27
  • 34
  • Hi @aled i have tried as you have suggested i have created variables in my flow which having data to insert in the table but again I got an error as Message : Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Column_data_encrypt', column_encryption_key_database_name = 'Encrypt_db') collation_name = 'SQL_Latin1_General_CP1_CI_AS' (com.microsoft.sqlserver.jdbc.SQLServerException). Payload : {NullPayload} please help on this. – Shubham_Relekar Jun 25 '20 at 09:58
  • Seems like the error mentioned in the docs at https://learn.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver15#errors-due-to-passing-plaintext-instead-of-encrypted-values – aled Jun 25 '20 at 14:24
  • hi @aled the solution that suggested in the link you have shared according to that I update my code to check data will insert or not but the same error i got as: Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'Column_data_encrypt', column_encryption_key_database_name = 'Encrypt_db') collation_name = 'SQL_Latin1_General_CP1_CI_AS' (com.microsoft.sqlserver.jdbc.SQLServerException). Payload : {NullPayload} Please help me on this. – Shubham_Relekar Jul 06 '20 at 09:51
  • also in another way i have changes query type to parameterized in mule so after that i am getting error as: Failed to decrypt a column encryption key. Invalid key store provider name: MSSQL_CERTIFICATE_STORE. A key store provider name must denote either a system key store provider or a registered custom key store provider.Valid (currently registered) custom key store provider names are: null. Please verify key store provider information in column master key definitions in the database,& verify all custom key store providers used in your application are registered @aled could u help on this – Shubham_Relekar Jul 06 '20 at 12:33