Knime 4.0 and SQL Server Legacy DB Reader

Hi - Just installed 4.0. My legacy database reader for SQL server fails. The reader for Oracle runs OK. The SQL Server error is that there is no response from server. I have returned to 3.7.2 and reader works fine for both Oracle and SQL Server.

Hi clac2188,
this is strange. We haven’t changed much in the existing framework besides changing the node name and categories.
Are you sure that you use the same driver and server settings (e.g. host name and port) in the 3.7.2 and 4.0.0 installation? Ideally you install 4.0.0 next to 3.7.2 by using the self extracting archive. Then open up both KNIME versions and check that both settings are the same and that both use the same driver. Execute the node at the same time in both KNIME versions to see that this is not a network issue.
Bye
Tobias

Hi Tobias, when I am in 3.7.2. it tells me that there are no updates. So I downloaded 4.0 as if a new install. Is this intended by you? Should I be able to get 4.0 by updating 3.7.2? - Christopher

Hello.

I have the same problem as clac2188. All my readers and conectors are with Legacy in name.
Ive reinstall old version and install new one. Knime workspace is the same.

Hi Christopher,
Edited: Initially I said that it would be possible to upgrade from 3.7.x to 4.0 but I was wrong. You have to do a fresh installation with KNIME 4.0 since upgrading is NOT possible. Sorry about that.

You did install KNIME 4.0 new and then opened the old workflow with the new installation? Did you install in KNIME 3.7.2 the official Microsoft SQL Server driver? Unfortunately you need to explicitly install it for each KNIME installation due to license restrictions from Microsoft. In KNIME 4.0 you need to install the KNIME Microsoft JDBC Driver for SQL Server (legacy) (marked yellow in the image below) for the legacy connector node. If this is not installed KNIME uses the open source jTDS driver. The red marked extension is the same driver but for the new database framework (all db nodes without (legacy) at the end).
Can you please double check if it is installed. To check go to File->Install KNIME Extensions… and search for sql server (see below). If you see the entries please install them. If you don’t see them and the Hide items that are already installed option is enabled they are already installed.

If the extensions are installed, could you please post the error message in the forum or send it so me via personal mail if it contains sensitive information.

Bye
Tobias

Hello the Tigger,
I’m sorry to hear that you have the same problem. Did you also install performed a fresh installation with KNIME 4.0? If so could you also please double check which driver you where using in KNIME 3.7.2 and KNIME 4.0 (see my post above)? If you are sure that you use the same driver and settings please also post the error message or send it via personal message if it contains sensitive information.
Thanks
Tobias

Hello.

Yeah, I just preform new fresh installation of Knime.
Funny thing, I had these 2 extensions installed, but when I unistalled SQL Server Legacy, the problem still remains. I wonder if new fresh install with extensions will solve the problem (I guess its the easiest way to do that) or do something more to solve the problem.

I use old workflow dir (Never had problem with these approach).

Thank you Tobias for the post earlier with Knime JDBC Driver :slight_smile:

Hi Tobias, that doesn’t seem to be the solution. I think I have the correct extensions installed. image . Here is the error that I still get: WARN Database Reader (legacy) 0:1245 Could not determine table spec from database query: SQL Server did not return a response. The connection has been closed. ClientConnectionId:fff163cb-0614-4f0d-8de6-dadc1117088b. What do you suggest for next step?

Hi clac2188,
do I understand you correct that the Database Connector node executes successful but the Database Reader node fails? Do you connect to the database via SSL? Maybe the problem is related to this driver issue? Which it seems is also discussed here. I just checked and with KNIME 3.7.2 we shipped Java 8 152 and with KNIME 4.0 we ship Java 8 202. So can you please try and append the following line to the end of your knime.ini file which is located in the KNIME 4.0 installation folder:

-Djsse.enableCBCProtection=false

Thanks
Tobias

# 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.

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

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.”

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

  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.

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

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.

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

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

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

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