issues connecting to ms sql server with integratedsecurity = true

The new 4.1 Microsoft SQL Server connector fails to connect to DB on the local system. I have added the following JDBC Parameters:
integratedsecurity = true
instancename = “Name of SQL Server instance”
in the Connection Settings I have entered:
Hostname: “Name of my Computer”
Database name: “Name of DB”
Authentication: None

All other settings are unchanged.
The SQL Browser service is active.
The sqljdbc_auth.dll is copied to Windows\System32

Using the general DB Connector works like a charm.

What I’m doing wrong?
Any hints are welcome.
Regards,
Karl

1 Like

Hi there @karlfloh,

what (error) message do you get? You are using same driver in both connector nodes?

Br,
Ivan

Hi Ivan,

the error message is:
ERROR Microsoft SQL Server Connector 2:72 Execute failed: Fehler beim Herstellen der TCP/IP-Verbindung mit dem Host ‘NB03’, Port 1433. Fehler: ‘connect timed out. Überprüfen Sie die Verbindungseigenschaften. Stellen Sie sicher, dass eine SQL Server-Instanz auf dem Host ausgeführt wird und am Port TCP/IP-Verbindungen akzeptiert. Vergewissern Sie sich, dass am Port keine TCP-Verbindungen durch eine Firewall blockiert werden.’.
I am using the same driver:
Microsoft JDBC Driver 7.4 for SQL Server [ID: MSSQL_jdbc_741]

Regards,
Karl

I’ve got below recommendation from Tobias.
For the old framework nothing has changed. So here is what you need to do to connect to MS SQL Server with
native authentication for the old framework using the official Microsoft driver:
Install the available “” plugin in KNIME via File->Install
Close KNIME and open the knime.ini file which is located in the KNIME installation directory with a text editor
such as notepad
Download the latest Microsoft SQL Server JDBC driver from here.
Extract the files and copy the path to the sqljdbc_auth.dll which is usually located in the following
directory: sqljdbc_7.4\enu\auth\x64 for 64 bit Windows and sqljdbc_7.4\enu\auth\x86 for 32 bit Windows.
Append the following line to the end of the file: -Djava.library.path=


With the new framework this is now much simple since we now ship all required DLLs with the driver. So to connect
to your database using the new framework please follow the instructions in the database documentation.

Hello karlfloh,
as izaychick63 already stated starting with KNIME 4.1 we include the necessary dlls for the new database framework. We now also have a detailed section in the database documentation that describes how to connect to a SQL Server database using native authentication. So you no longer need to copy the sqljdbc_auth.dll file into the Windows folder but it also doesn’t hurt :slight_smile:

You say that the DB Connector node works but not the Microsoft SQL Server Connector node. So this indicates to a problem with the generated JDBC URL. The Microsoft SQL Server Connector node uses the following JDBC URL template:

jdbc:sqlserver://<host>:<port>;databaseName=[database]

where host will be replaced by the value you enter in the hostname field, port will be the value from the port field and database will be the value from the database name field but is optional. For further details about the JDBC URL template see the documentation.
Bye
Tobias

1 Like

Thank you for clarification. But what I am missing is: where should I place the SQL-Server instancename? Because there are 5 or 6 instances running different versions of SQL Server on the system. In the Mircrosoft SQL Server Connector I have created a JDBC Parameter (named “instancename”) with the name of the instance as value, whereas in the DB Connector the instancename ist part of the URL-String: <host>;instancename=<instance>;databasename=<databasename>;integratedsecurity=true

It should work if you put the instanceName into the JDBC parameter tab e.g.


For further details about the available parameters see the this Microsoft article.

Hi Tobias,
that is exactly what I have done, but the connection could not be established …

Hi karlfloh,
this is strange. DB Connector node and the Microsoft SQL Server Connector node use mostly the same logic to connect to the database so there still seems to be a problem with the generated JDBC URL and the parameters. Can you please change the KNIME log file log level to DEBUG by opening the preferences settings via File->Preferences and then going to the KNIME section.

Once this is done please execute the DB Connector node that works and then the MS SQL Connector node that doesn’t work. You can then have a look at the KNIME log file (View->Open KNIME Log). With in the log file you can search with key combination Ctrl+f for “Database session information:” which will contain the URL that KNIME uses to connect to the database e.g.

url=jdbc:sqlserver://mysqlserver:1433;databaseName=knime

Bye
Tobias

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.