Integration Authentication Error Microsoft SQL Server 2017 and Knime

1
votes

There are a number of older posts on integrating a database with Knime, however I haven’t found any discussions regarding the latest versions of Java, Knime, and SQL. I have spent days looking at forums here and on StackOverflow and elsewhere trying to connect to a database with Knime, however nothing has worked.

My computer configuration is as follows: 64 bit Operating System Microsoft SQL Server 2017 (64 bit version) Microsoft SQL Server Management Studio 17 Microsoft JDBC Driver 7.0 for SQL Server KNIME 3.7.0 (64 bit version) Java jre1.8.0_191

So far I’ve tried: Downloading java versions into both Program Files\Java and Program Files (x86)\Java. Currently I have the java version that is stored in the Program Files directory and not in the (x86) directory.

Downloading and unzipping the ‘sqljdbc_7.0.0.0_enu’ JDBC 7.0 Driver. I also tried the JDBC 6.0 Driver, but that didn’t work.

putting sqljdbc_auth.dll in the C\Program Files\Java\jre1.8.0_191\bin and C\Program Files\Java\jre1.8.0_191\lib directories.

Enabling TCP/IP Port in Microsoft SQL Server Management Studio

configuring Knime’s Database Connector, Database Reader, and Microsoft SQL Server Connector nodes to the following settings: Database driver: com.microsoft.sqlserver.jdbc.SQLServerDriver Database URL: jdbc:sqlserver://(localhost); integratedSecurity=true; Authentication: Checked ‘Use credentials’ (for Database Reader this feature is blocked out)

When I execute the Database Connector node I get the following error: ERROR Database Connector 0:2 Execute failed: Could not create connection to database: no sqljdbc_auth in java.library.path

When I execute the Database Reader node I get the following error: ERROR Database Reader 0:1 Execute failed: This driver is not configured for integrated authentication. ClientConnectionId:4906d2f3-e3df-4016-a383-29840cb4dee2

When I execute the Microsoft SQL Server Connector I get the following error: ERROR Microsoft SQL Server Connector 0:5 Execute failed: Could not create connection to database: no sqljdbc_auth in java.library.path

3
javasql-serverjdbcknime
Do you have sqljdbc_auth.dll in a folder that is on the Windows PATH? The JDBC driver needs to be able to find the DLL so it can use integrated authentication. – Gord Thompson

3 Answers

1
votes

It seems an extra setup step is required for the integrated authentication setup. I assume you are using KNIME with 64 bit JVM, so you should copy the C:\Microsoft JDBC Driver 6.4 for SQL Server\sqljdbc_<version>\enu\auth\x64\sqljdbc_auth.dll to the C:\Windows\System32 folder or add the following line to knime.ini (somewhere after the -vmargs line):

-Djava.library.path=C:\Microsoft JDBC Driver 6.4 for SQL Server\sqljdbc_<version>\enu\auth\x64

(I assume <version> should be something fitting your JDBC driver set for KNIME. The path probably also requires further adjustments.)

I think the second option is preferred, so you can have different JDBC drivers, but it requires adjustments to knime.ini for all installation.

0
votes

Microsoft's JDBC driver seems to prefer that sqljdbc_auth.dll be visible on the Windows PATH to make it available for trusted connections (Windows authentication) to a SQL Server instance. Ensure that the DLL is in a folder on the PATH.

0
votes

After many trial and error, here is how I resolve my issue of KNIME JDBC connection to MS SQLExpress with Windows authentication. I using the jTDS for Microsoft SQL Server that comes with KNIME.

Select the Microsoft SQL Server Connector Node and configure the node with the following settings:

  • a) Hostname = localhost (if running remote, replace with hostname or IP of remote server)

  • b) Port = 1433 (default SQLserver port)

  • c) Database Name = PracticeDB (replace with your database name you wish to connect)

  • d) Authentication: None/native authentication

  • In the JDBC Parameters tab from the Microsoft SQL Server Connector, add the following: integratedSecurity for Name and the boolean value of true

KNIME Microsoft SQL Server Connector will generate the necessary JDBC connection string:

Database type: Microsoft SQL Server Connection: URL="jdbc:jtds:sqlserver://localhost:1433/PracticeDB" Driver: jTDS for Microsoft SQL Server (ID=jTDS for Microsoft SQL Server, Version=1.3.0)