Nine 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

Francois Denis

Don't forget to tag when it's "solved"!

1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: mssql 2016 Always Encrypted column

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!

Francois Denis

Don't forget to tag when it's "solved"!

7 REPLIES
Eight 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

Nine Stars

Re: mssql 2016 Always Encrypted column

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

Francois Denis

Don't forget to tag when it's "solved"!

Eight 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

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

Francois Denis

Don't forget to tag when it's "solved"!

Eight 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

Nine 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)

Francois Denis

Don't forget to tag when it's "solved"!

Nine Stars

Re: mssql 2016 Always Encrypted column

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!

Francois Denis

Don't forget to tag when it's "solved"!