mssql 2016 Always Encrypted column

[fdenis]

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

[Anonymous]

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

[fdenis]

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

[Anonymous]

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

[fdenis]

[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')

[Anonymous]

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

[fdenis]

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)

[fdenis · Accepted solution]

Hi, I solve this problem.

Driver part:

Install mssql driver 6.0 or upper

Secondly update jce policy.

Configure your connection string.

Talend Part:

First use jdbc components to use the last version of ms sql driver.

Secondly, you have to copy tJDBCOutput begin, main and end into a tJavaFlex.

In main part:

 Replace setString by setNString when using NVARCHAR (even if you have define NVARCHAR in metadata columns Talend generate setVarchar)

Done.

But I still wait for tMssql components update!

[Anonymous]

I am able to make connection to the sqlserver instance using tJDBCconnection but not able to read data using tJDBCinput component.Not able to retrieve schema of any table. Can you help us with the JDBC connection details. I am pasting the screen shot of my connection details.