Configuration node Microsoft SQL Serverc Connector

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

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

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

1 Like

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?

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

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

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

Can you connect with the legacy connector?

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

no i’m not usig MS SQL SERVER on Azure

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

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

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

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

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

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

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

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”

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

Hi,
the ping was successful

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

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