Configuration node Microsoft SQL Serverc Connector

KNIME Analytics Platform
1

Good morning,
I need to connect to my Microsoft SQL Server, but I can’t configure the connection node.
Can you help me?

First of all, do I have to use the Legacy node or not?

Thanks a lot to everyone
Nicola

2

Hi @nik80,

Is remote connection allowed in your server properties?

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-ver15

:blush:

1 Like
3

Hi, @armingrudd
Yes, it is allowed :+1:

1 Like
4

So you just need to enter the IP, port number, DB name and your credentials. What is your problem in configurations? Or was it just solved?

5

1
1667×598 46.7 KB

it remains running until after a long time it stops due to time out

6

Follow these steps and let me know if it solves the issue:

https://docs.sdl.com/LiveContent/content/en-US/SDL%20Tridion%20Docs-v1.1.2/GUID-CF5C89EA-0918-4002-8391-80CBEDE0BB5E

1 Like
7

I checked the configurations and they are ok. I have MS SQL 2005 and using another application (SPSS) I can reach it without problems

8

Can you connect with the legacy connector?

9

Hi,
are you using a MS SQL Server on Azure? If yes, can you try downloading the 8.2 driver from here, register it in the preferences (KNIME -> Databases) and use it in a normal DB Connector node using the connection string provided in the Azure config under “Connection strings” -> “JDBC”?
Kind regards
Alexander

2 Likes
10

no i’m not usig MS SQL SERVER on Azure

11

Hi,
can you still try with that other driver? I had success with it when the other one did not work.
Kind regards
Alexander

12

But in preferences - Database is it necessary to configure something?

13

Hi,
Yes, you have to register the JDBC driver there. Just add another entry to the list, select MS SQL as Database Type, give it an ID and name, at the bottom click “find driver classes” and then apply and close the settings. Now the new driver should be available in the DB Connection node.
Kind regards
Alexander

14

2
2585×794 38.8 KB

Hi Alexander,
I am trying to create a connection as you indicated to me. I first downloaded the jdbc drivers from the MS site (I downloaded version 6.0 thinking it was the most suitable for my SQL Server 2005) but when I try to look for the drivers it doesn’t find anything. can you help me? Thank you

15

Hi,
I can find the driver class in my KNIME. Are you using the file /sqljdbc_6.0/enu/jre8/sqljdbc42.jar? This is the one I got in a .tar.gz file from the Microsoft website.
Kind regards
Alexander

17

Hello,
I was able to find the indicated drivers (photo 1)

1
1587×794 43.7 KB

But even if the connector nod is configured (photo 2)

2
2665×596 44.6 KB

I get the following result:
Execute failed: Execute failed: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “SQL Server did not return a response. The connection has been closed. ClientConnectionId: abb675c3-b05d-49e1-af84-0fcd7b55f58c”

18

Hi,
there is an issue over at the Microsoft GitHub repo for the SQL Server JDBC driver: https://github.com/microsoft/mssql-jdbc/issues/414. Maybe it is related. But it could also be that your computer simply cannot access the server. Have you tried pinging it from the command line?
Kind regards
Alexander

19

Hi,
the ping was successful

20

Hi,
ok, that’s good. The issue on the Microsoft GitHub page mentions a workaround: Could you add -Djsse.enableCBCProtection=false to your Knime.ini in your KNIME installation folder? This is not a permanent solution, obviously, but might tell us whether this is the same issue.
Kind regards
Alexander

21

Hello,
I inserted what indicated in the .ini file
now i get this error: Execute failed: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “SQL Server did not return a response. The connection has been closed. ClientConnectionId: 7644f89c-6930-4139-8451-e5911fc49f23”.