Four Stars

mssql 2016 Always Encrypted column

Hi,

 

I'm trying to insert data into an always encrypted column (mssql 2016).

I first use mssql connection but with no success.

I try to use generic jdbc to use the last Microsoft jdbc driver 6.0 but I still have troubles with column type.

Did someone succeed in the type of connection?

 

Regards,

Francois

6 REPLIES
Six Stars

Re: mssql 2016 Always Encrypted column

Hi there,

 

This isn't something I've played with myself, but from a quick read of the related Microsoft docs, it appears you should just need to set the following in "Additional JDBC Parameters" on the tMSSqlConnection:

 

columnEncryptionSetting=Enabled

Hope this helps.

 

Regards,

 

 

Chris

Four Stars

Re: mssql 2016 Always Encrypted column

This is the parameter in the connection string. but it's not sufficient.
Six Stars

Re: mssql 2016 Always Encrypted column

OK, so when you say you're having no success, are you getting any actual errors?

 

There doesn't need to be anything special about the SQL for your INSERTs to work, so I'm expecting the problem to be on the connection side of things.

 

Regards,

 

 

Chris

Four Stars

Re: mssql 2016 Always Encrypted column

[ERROR]: extract.t_0_1.t - tMSSqlOutput_1 - Operandetype clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryption_type = column_encriyption_keydatabase_name = 'T')
Six Stars

Re: mssql 2016 Always Encrypted column

I understand that column types/sizes are very important when using Always Encrypted columns, and from the error message it appears that there's no size specified for the particular nvarchar field you're trying to insert, so I'm guessing this isn't defined in the schema of your Talend job.

 

If you add a size of 4000 to this column in your schema, does that help?

 

Regards,

 

 

Chris

Four Stars

Re: mssql 2016 Always Encrypted column

it's the same error with size.

For column encryption, driver must be the last microsoft jdbc driver 6. in this case encryption is done by the driver (sqljdbc41.jar or sqljdbc42.jar)