Microsoft SQL server connector encrypting issue

[Albert0_123]

HI,
in our SQL Farm they have prepared certificates to encrypt traffic to SQL. They also set, on the SQL side, the “Force Encryption” parameter = True
We would like to know the following information:

• Does the driver we are using provide encryption or is it necessary to update it? If so, which version should I download?
• we would like to know how you can set the following parameters from the Knime graphical interface:
• Encrypt=true or false
• TrustServerCertificate=True or False

I am using Knime 4.6.2.
Thanks in advance

[ipazin]

Hello @Albert0_123,

you can add JDBC parameters on JDBC Parameters tab of any DB connection node. For MS SQL you can install their official driver (latest version). Here is documentation (version 5.2 as I didn’t find DB documentation for 4.6 but these things should be same hopefully!) where you can check how to do above things and find more info:
https://docs.knime.com/latest/db_extension_guide/index.html#intro

Br,
Ivan

[Albert0_123]

Hi Ivan,
as already mentioned I am using KNIME 4.6.2
The connector node is : Microsoft SQL Server Connector.
In its “connecting settings” tab, both values ​​for “Database Dialects:” and “Driver Name:” are set fixed as the only possible choice
In its “JDBC parameters” tab I entered the following “Name” and “Value”: ​​encrypt true and trustservercerticicate true.
When I run the node I get: “Execute failed: I/O Error: DB server closed connection”.
I could also switch to a more updated version of Knime but first I would like to ask you: in your opinion I’m doing something wrong or something is missing in the configuration?
Many Thanks!

[ipazin]

Hello @Albert0_123,

@Albert0_123

In its “connecting settings” tab, both values ​​for “Database Dialects:” and “Driver Name:” are set fixed as the only possible choice

if you have only one option then I would say you didn’t install official Microsoft JDBC driver. See this part of documentation:
https://docs.knime.com/latest/db_extension_guide/index.html?pk_vid=7afaa7730384d0a217153316883ebaf2#db_plugin

@Albert0_123

in your opinion I’m doing something wrong or something is missing in the configuration?

Would say it’s not version related rather proper configuration is needed. These things can take time…

Br,
Ivan

[Albert0_123]

Hi Ivan,
I downloaded the official Microsoft drivers exactly as you suggested and configured the node like this :

• in its “connecting settings” tab I selected “Official Driver for Microsoft SQL Server v.9.4.0” as the driver name and then checked the none/authentication box
• in its “JDBC Parameters” tab I added the “integratedSecurity” parameter and set it to True.
  The problem is now fixed, everything works fine,
  Thank you so much!
  Br,
  Alberto

[ipazin]

Glad you made it Alberto!
Ivan