SQL server connection issues

[DanSah]

Hi everyone,
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.

This is how my DBeaver setup looks:

Everything works smoothly.

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.

The custom setup looks like this:

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,
Daniel

[NDekay]

Hello,

Thank you for contacting KNIME regarding this issue.

A few things jump out at me:

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

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

3. 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:#############
   }

4. I’d test going back to the default included MSSQL drivers, though yours probably work, too.

5. Your Database URL should look something like:
   {
   jdbc:sqlserver://nzsqlsomehostname:1433;databaseName=psomedatabasename;integratedSecurity=true
   }

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 look forward to helping you resolve this issue!

Regards,
Nickolaus

[Daniel_Weikert]

@NDekay

integratedSecurity=true

What if my SQL Server has backslashes inside?
DESKTOP-5AI3SDB\SQLEXPRESS

I have exactly the same error message and the only work around so far is using python

[mlauber71]

have you tried Escaping the \ with another \ like

\\

[DanSah]

Thanks for the replies everyone,

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.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:279)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2431)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:656)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2440)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2103)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1950)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1162)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:735)
	at org.knime.database.connection.UrlDBConnectionController$ControlledDriver.connect(UrlDBConnectionController.java:95)
	at org.knime.database.connection.UrlDBConnectionController.createConnection(UrlDBConnectionController.java:308)
	at org.knime.database.connection.UserDBConnectionController.createConnection(UserDBConnectionController.java:249)
	at org.knime.database.connection.AbstractConnectionProvider.createConnection(AbstractConnectionProvider.java:89)
	at org.knime.database.connection.impl.DBConnectionManager.lambda$2(DBConnectionManager.java:501)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

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

[DanSah]

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:

How to connect to SQL Server Express: you must specify the port number and not the instance name. Can this be added to a FAQ and to the documentation?

I present below some information on how to connect to a Microsoft SQL Server Express database; unlike most other software relying on JDBC, Knime requires you to input the port number, not the instance name. I hadn’t found this mentioned anywhere. It would be good to add this in some kind of FAQ and in the official documentation. I am not sure if the same applies to full versions of SQL Server I took the liberty of creating another thread because I think this title is more representative; if th…

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>;databaseName=mydatabase; integratedSecurity=true

across all drivers, also the official one!

Thanks a lot for guiding me in the right direction!