Using KNIME 3.7.1…
I get an error message when using the DB Connector (Labs) to connect to Microsoft SQL Server 2016.
It is important to note that I can connect using the regular Database Connector without any problems.
UPDATE: I wanted to note that I only get an error when trying to use Integrated Security. I do not get an error when using SQL Server security. So it truly is trying to solve how to configure a connection to SQL Server that uses Integrated Security using the DB(Labs) connectors.
Here is what I did to get this error:
- installed the KNIME Extension: KNIME Microsoft JDBC Driver for SQL Server (Preview) v3.7.0
- installed the KNIME Extension: KNIME Database (Preview) v3.7.1
- in KNIME preferences->Databases (Labs)->Add database driver settings (see uploaded screenshot)
- Applied changes in Preferences and Closed
- Shutdown KNIME, rebooted PC
- Configured DB Connector (Labs) node (see uploaded screenshots)
- Executed Node and got the following message: This driver is not configured for integrated authentication.
FYI … I moved the sqljdbc_auth.dll file into the same directory as my jdbc jar file and added the following entry in KNIME.ini file many months ago when first configuring the regular db connections: -Djava.library.path=C:\KNIME\sqljdbc_6.2\enu
Where did I go wrong?
Maybe, it does not help too much, but I do not encounter any problems with MS SQL 2016 and the JDBC-MSSQL-6.4 driver. Maybe, you want to upgrade to the new version of the driver.
I addition, I use the integrated handover of parameter:
Thanks for your input. I updated my driver to 7.2.1 but I still get the same error when trying to use the Integrated Security parameter in the KNIME Labs connectors: both DB Connector (Labs) and Microsoft SQL Server Connector (Labs). I do not get an error when I use the regular connectors. It is also important to note that I do not get an error anywhere when using SQL Server user. However, I have to use the Integrated Security option on our company systems.
So it basically comes down to this: does the Integrated Security parameter work in the KNIME Labs db connectors? If it does work then I am using the Integrated Security parameter incorrectly. I would really like to know if anyone has successfully used the KNIME Labs connectors to connect to SQL Server using Integrated Security and would like to see how their screenshots (with names changed to protect systems of course).
I can confirm that this does not work the same with the new DB nodes compared to the original database nodes. My assumption is with the new DB nodes we changed how our class loading works, and how things are isolated, hence why you can now have multiple driver files with the same file name.
My suggestion is to shift from the DLL based connection to kerberos. Doesn’t require loading DLLs, and will be supported by KNIME with our database and big data nodes. Microsoft has been adding more robust kerberos support within the JDBC driver. I would assume this is to support SQL Server on LINUX.
We published an article on how to setup Kerberos and KNIME AP together, and we have a Cluster security guide available a docs.knime.com that discusses this topic.
Looking forward to our summer release, we bring the kerberos setup into KNIME so these steps become unnecessary. If you want to try the nightly build, this feature is in it.
The URL you use changes slightly
I confirmed this works within KNIME 3.7.1 on Windows 10 x64 connecting to SQL Server 2016 on windows server 2016. Both original database nodes and new db nodes.
The following query will show you your connection type
select auth_scheme from sys.dm_exec_connections where session_id=@@spid