i am a rookie user of KNIME trying to make a very basic connection to a SQL server.
I’ve been through all the community posts and variants but can’t get it to work still.
I am trying to connect to a SQL server to which i have a working connection in DBeaver using JDBC - so i didn’t expect this to be a problem within KNIME.
The database tutorial does also present it like a piece of cake.
I am on a Win 10 machine, trying to use Windows Authentification.
I’ve tried various ways in KNIME to get this up and running, both with the official driver (with the tricks shown in various threads on the forum) and with my own custom setup pointing to the DBeaver drivers.
Both the custom setup and using the official driver gives the same error and i’ve tried with and without quotes around the database ‘address’:
"ERROR DB Connector 4:9 Execute failed: The TCP/IP connection to the host "nzsqlxxxxxxxxxx.net, port 1433 has failed. Error: "“nzsqlxxxxxxxxxx.net. 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.”.
KNIME has full access out through my firewall, like DBeaver - so i think it is something with my setup.
I can also make connections to SAP HANA via JDBC in KNIME without any firewall issues… So i am not sure how to fix this…
Can someone point me in the right direction?
Thanks in advance,
Thank you for contacting KNIME regarding this issue.
A few things jump out at me:
your hostname, does it really have \i01 at the end of it? The backslash () seems out of place in a hostname. This seems obvious, but double check that it is correct. To the best of my knowledge, a hostname should not have a backslash in it.
I would not use the delimiters ("") around the hostname. I tested it in AP 4.2.3 and it failed when I specified the host this way.
I looked up the integratedSecurity=true parameter and found
When false , User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication.
so that looks reasonable.
When I tested it with “;integratedSecurity=true” and Authentication=None in our test environment, it recognized that it is a valid option, though of course our configuration uses user/pass rather than Windows Auth for authentication, so I got the expected error:
ERROR DB Connector 3:1 Execute failed: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. ClientConnectionId:#############
I’d test going back to the default included MSSQL drivers, though yours probably work, too.
Your Database URL should look something like:
If you’re still having difficulty getting it to work, please update us with the following for additional information/troubleshooting:
What operating system is your KNIME software running on?
What version(s) of KNIME Server (KS) and KNIME Analytics Platform (AP) are being used?
Are you able to provide logs for the time period covering this issue? (.metadata\knime\knime.log)
I think it is something with that backslash. It is indeed there, and when removing that part in my Dbeaver connection string I get an error that i cannot reach the server (very similar to the error message in Knime). So i wonder if that part is dropped somehow.
So it might need some escaping to get it to work like mlauber71 is suggesting - however the double backslash does not work.
Here is the output in the log (when using the official driver and a normal backslash):
2020-12-02 12:12:58,830 : ERROR : KNIME-Worker-11-Microsoft SQL Server Connector 0:15 : : Node : Microsoft SQL Server Connector : 0:15 : Execute failed: The TCP/IP connection to the host nzsqlxxxxxxxxxx.net, 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.".
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host nzsqlxxxxxxxxxx.net, 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.".
If you look closely the \i01 seems to be dropped from the server address when looking at it in the log.
The error is exactly the same when trying to escape the backslash with double backslashes…
Here are my versions:
Win 10 Enterprise v 1903
KNIME Analytics Platform 4.2.3
The server is a MS SQL Server 12.00.4237