Issues connecting to db with MS SQL Server Connector

[Bartosz_Cicharski]

Hello, I’m trying to connect to a MS SQL Server database with a KNIME connector. I use the driver named Official Driver for Microsoft SQL Server v. 9.4.0. When I hit execute, after few seconds of working I receive this error in Console:
ERROR Microsoft SQL Server Connector 3:4 Execute failed: The TCP/IP connection to the host 192.168.0.40, port 1433 has failed. Error: “Connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

It doesn’t matter if I:

• provide login credentials in this node or with credentials node
• use the driver mentioned above or v. 7.2.0, which I believe was installed with KNIME.

I use vpn to connect to this db.

When I use the same credentials and details, on the same vpn in DBeaver (app for SQL queries), there are no issues and I can connect.

What might be the cause and solution for this? This is really frustrating, because on Friday I’m supposed to show how cool KNIME is to some people at my new work and without this connection working - it would be very hard.

[Doru]

Hi @Bartosz_Cicharski ,

1:
No sure if this is your case but I encounter something similar when I used a personal VPN. A working KNIME connector stopped running. The reason is that the cloud never seen a connections from my new IP address range. Tried to connect from MSSMS and been asked to add the IP range to trusted connections. After that the connector in KNIME worked again. Not sure how DBeaver works though.
I would try to connect from MSSMS.

2:
Assuming the above does not apply to you, could you share your Preferences/Knime/Databases window and the failing KNIME connector configuration screenshots please?
Trying to compare with mine…

[Daniel_Weikert]

@Doru

at the cloud never seen a connections from my new IP address range. Tried to connect from MSSMS and been asked to add the IP range to trusted connections. After that the connector in KNIME worked again. Not s

I have a similar issue but with my local sql server instance so I am interested in your configuration as well.
As a current workaround I still use scripting nodes

[Bartosz_Cicharski]

Hi, I’m able to connect with MSSMS ot this database, however I cannot connect with KNIME.
I have no additional drivers installed, so there is no point in showing my Preferences → Knime → Databases window. However: this is my failing connector configuration.

The error message is still:
ERROR Microsoft SQL Server Connector 3:6 Execute failed: The TCP/IP connection to the host 192.168.0.40, port 1433 has failed. Error: “Connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

[Bartosz_Cicharski]

Can you tell me more about this workaround? What are scripting nodes?

[Doru]

Hi,

This is a screenshot of my Connection settings:

I have a couple of questions please:

1. what is with the "" in the server definition. You should have a host address there, nothing else. I would remove the slash and anything after that and try again. I believe that the server is incorrect and the error says just that, I cannot server it may be behind a firewall.
2. Just for my clarification: you said you’re using a VPN. Is that company’s (are you logging in remotely) or it is yours. I got the similar error when I had to go through both.

[Doru]

Clearly the backslash is missing from my message. I can see only double quotes. My suggestion was to remove the backslash and everything after that.

[Bartosz_Cicharski]

I removed the backslash and it did not help. I have the same error message.
This is my company’s VPN, I use it to connect remotely. And there is no issue when I connect with this db and credentials with MSSMS, DBeaver or Power Query. Here’s the snapshot of conenction config for DBeaver. as you see - it also has the part after backslash and it connects to a proper db.

[Doru]

Sorry not an expert and don’t know your environment.

Our problem is that KNIME does not see the SQL Server (192.168.0.40).

Are you running KNIME from your local machine (Analytics Platform) or from a KNIME server?
If from a server it may be that the SQL server is not accessible from where your KNIME executor is running while your working connections are from the local machine.

[Bartosz_Cicharski]

This is KNIME Analytics Platform on my local machine. From the same machine with other apps, like MSSMS, I can connect to this server. With KNIME on this machine I can connect with other servers, also running Microsoft SQL Server. But this server and KNIME just don’t seem to work together.

[Doru]

Interesting, so this server in particular gives you a headache.

It seems like a security restriction to me.
Do you have any infrastructure support that can look into what’s different with this particular server?

It may not apply but once to have something running (in the cloud) I had to install a certificate on my machine.

[Doru]

Not sure how @Daniel_Weikert is connecting SQL Server using scripts.
My guess would be that he’s using a Java or Python snippet.

Just looked up how to connect Python to SQL Server and found this:

Python driver for SQL Server - Python driver for SQL Server

You can put the code into a Python snippet and get the result of the SQL in a table.
That’s if you have already Python installed and added to KNIME otherwise it will take you some time.
Been using both Java and Python Snippet Nodes but not to connect to databases.

[Bartosz_Cicharski]

I will contact my IT support and we’ll see. Thank you very much for your help!

[takbb]

Hi @Bartosz_Cicharski ,

Are you using Windows authentication or sql authentication?

Have you seen this post which looks like a very similar issue and was ultimately resolved by using the specific port number for the db instance rather than the standard sql server port number…

SQL server connection issues

After reading some more on google it seems like i can pass the “i01” part (after the backslash) as a property called instanceName, it works in DBeaver at least. Knime is still refusing it though, not sure if it has something to do with this post: And after a quick question to the database expert i got a port number instead of the instance and am able to connect using this connection string: jdbc:sqlserver://nzsqlxxxxxxxxxx.net:<portnumber for the instance, not the standard port number>;d…

[Bartosz_Cicharski]

Hi @takbb and thanks for this hint. I will ask my IT service for the port number, since I cannot check it on my own. And we’ll se if this helps.

BTW: I’m using SQL Authentication.

[Bartosz_Cicharski]

OK, it looks like KNIME doesn’t like instances and needs specific port to connect.
To find the specific port, connect ot a db in Microsoft SQL Server Management studio and run this code:

DECLARE @portNumber NVARCHAR(10);
EXEC xp_instance_regread 
     @rootkey = 'HKEY_LOCAL_MACHINE', 
     @key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll', 
     @value_name = 'TcpDynamicPorts', 
     @value = @portNumber OUTPUT;
SELECT [Port Number] = @portNumber;
GO

This will return proper port number. Then in KNIME connector use this port and just odn’t put the instance name in the host name (anything after the backslash).

[Doru]

Happy to hear the problem is solved.
One day I may need to connect on-premise and I’ll need this, thanks for sharing.