Knime 4.0 and SQL Server Legacy DB Reader

#10

# tobias.koetter, in my case connector and database node works without errors. I just have only in name “(Legacy)” at the end. I guess I shouldnt be worrierd about that?
I deleted jdbc ver 6.2.2.v20 extension.

0 Likes

#11

Hi Tobias - I appreciate you working with me on this. Thank you. I have never used connector nodes. I use the readers directly. I do not use SSL. I tried adding -Djsse.enableCBCProtection=false to the ini file, then restarted and still get the same error. Please advise. - Christopher

0 Likes

#12

Hi Tobias – I’m experiencing the same issue as @clac2188 (Christopher). In an effort to avoid branching, I’ve repeated all of your suggestions, but the issue remains. After executing the legacy database reader I receive the following message –
"ERROR Database Reader (legacy) 0:4720 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.”

0 Likes

#14

Hello hasbeen,

do you use the Database Reader (legacy) together with a Database or Microsoft SQL Server Connector node or do you specify the connection information in the reader node itself like Christopher does? Which driver do you use to connect to the database? Can you append the jdbc url to the post (but without any sensitive information)? Could you successfully create the connection with the same settings (jdbc url, user, etc.) with KNIME 3.7?

I guess you have also tried to add the

-Djsse.enableCBCProtection=false

parameter to the end of your knime.ini file as described above?

Thanks
Tobias

0 Likes

#15
  1. I specify the cnxn within the Database Reader (legacy), like Christopher.
  2. I’ve tried to establish the cnxn using both sqljdbc41.jar (jre7), and sqljdbc42.jar (jre8). First, uploading the path to the jre7, then removing the jre7 path & replacing it with the jre8.
  3. The URL is jdbc:sqlserver://fake_host:1433;databaseName=fake_database.
    I haven’t found success when trying different variations of the connection string pulled from https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017&viewFallbackFrom=sql-server-previousversions
  4. I am able to connect using the same setup within Knime 3.7.
  5. Yes, I added “-Djsse.enableCBCProtection=false” to the .ini file.
0 Likes

#16

Hello hasbeen,

you don’t need to register a driver. You can simply install the KNIME Microsoft JDBC Driver for SQL Server (legacy) as described in my post above. Once the extension is installed you can select com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver to use (see below)

Also if you want to connect to a SSL secured SQL Server you need to specify additional JDBC parameter as described in the MS documentation such as encrypt=true;trustServerCertificate=true which you can append to the database url with a semicolon after the database name.

Bye
Tobias

1 Like

#17

Hello Tobias, have you concluded that the legacy database readers cannot be used in 4.0 as they have been heretofore? I am reluctant to move to 4.0 if I conclude that I must re write all of my workflows. This will be a real loss of productivity.

0 Likes

#18

Hello clac2188,
the existing database legacy nodes should execute in KNIME 4.0 the same as in KNIME 3.x. So all your existing workflows should execute as before when moving to KNIME 4.0.
If this is not the case we will fix this as soon as possible. However until now I couldn’t figure out the problem why the Database Reader is throwing this strange exception. I will follow up with you via private messages to get the log file to get to the core of the problem as soon as possible.
Bye
Tobias

0 Likes

#19

Hello hasbeen,
where you now able to connect to your MS SQL Server with KNIME 4.0?
Thanks
Tobias

0 Likes

#20

Hello!

Ive got problem with workflows. All my old workflows works fine, the problem is with the new ones. Im running Knime with bat mostly and all new workflows have the same error 0x2.

This will be fixed (legacy readers etc)?

0 Likes

#21

Hi theTigger,
what do you mean with error 0x2? I will contact you via private message so that you can send me the log file with the full error message and stack trace.
Thanks
Tobias

0 Likes

#22

Hi Tobias, I’ve updated most of my workflows to make use of the new “DB” series of connection nodes. Thank you!

1 Like

#23

Hi Tobias,
I have just upgraded to 4.0.1 and my connection to an sql server (using a legacy database connector) still doesn’t work. It worked in KNIME 3.7…
The error message I’m getting says:
"Could not create connection to database: SQL server did not produce a response. The connection has been closed. "

Regards
Philippe

1 Like

Migrate DB Nodes from 3.7 to 4.0
#24

Hello Dr_Phil,
I’m sorry to hear that you have problems connecting to SQL Server after upgrading to KNIME 4.0.1. In order to better help you can you give us some more information about your setup.
Do you try to connect to the SQL Server via a SSL secured connection? If this is the case can you please dry the solution mentioned here.
What version of the MS SQL Server do you try to connect to? What driver and version do you use (built-in jTDS or SQL Server driver or a custom registered driver)?
Bye
Tobias

P.s. I have moved your post to this thread which covers this topic.

1 Like

#25

Hi Tobias,
thank you for getting back to me so quickly.
Since posting my question, I have discovered that with KNIME 4.0.1 I can access one SQL server and one SQL server I cannot access. In KNIME 3.7 I can access both. Both use the same node and syntax. My understanding of databases/servers/drivers is too limited to be answer you, but I have since moved back to the older version of KNIME so everything works again. I will liaise with our local database owners to try and resolve this.

Kind regards,
Philippe

0 Likes

#26

Dr Phil - is the SQL server you can address a different MS SQL Server version than the one you cannot address? - CAJ

0 Likes

#27

Hi CAJ,
that is a very good question and that is something I will need to take up with the owners of the databases.
I shall investigate

0 Likes

#28

Try SELECT @@VERSION to check SQL Server version.

1 Like

#29

Hi izaychick63,
that is very helpful
The database I can link to is
Microsoft SQL Server 2014 (SP3-CU3-GDR) (KB4505422) - 12.0.6293.0 (X64)
May 25 2019 22:45:08
Copyright © Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )

and the one I can’t link to is
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright © 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

0 Likes

#30

Hello Dr_Phil,
thanks a lot for the information. According to the Microsoft compatibility matrix version 6.2 of the driver should be compatible with SQL Server 2008. KNIME Analytics Platform comes with version 6.2.2 of the driver for the old db framework which also hasn’t changed for KNIME 4.0. Can you try to download and install an older version of the driver e.g. 6.1 and try if you can then connect to the db.
Did you know that Microsoft has stopped the support for SQL Server 2008 as of last month.
Bye
Tobias

0 Likes