Dear KNIME team,
I am not sure, where to provide feedback regarding the new DB nodes that have been introduced to the community with KNIME 3.6.0, but I will dispense it here:
The parallel execution of database queries is great! Thank you for this improvement!
I tried to connect to an MS SQL Server with the Microsoft JDBC driver & Single Sign On by using this URL:
jdbc:jtds:sqlserver://SERVER:1433/DATABASE**;integratedSecurity=true**
Whereas the login worked with the old database connector nodes, the login fails with the new nodes:
Execute failed: DB Session 437b9d06-f99b-41c2-9361-411c1270b06d is invalid. Login failed for user āDATABASE_USERā.
The encrypted version of the URL also fails (extend URL by ā;encrypt=true;trustServerCertificate=trueā)
It would be great if you could have a look on the single sign on partā¦
Hi,
thanks for your feedback. So far we do not support integrated security out of the box. This is planned for the December release.
To use it with the current version you need to register the jTDS or Microsoft driver including the NTLM authentication dll files. To do so copy the jar file and all required dlls into a folder. Open KNIME and go to File->Preferences. In the preferences go to KNIME->Databases (Labs). In the database page click on the Add button. In the database driver settings dialog provide a unique name for the driver, select mssqlserver as database type and add the previously created folder to the classpath via the Add directory button. Once this is done click on Find driver classes to select the jDTS driver class.
After registering your jTDS driver with the authentication dlls included you can select it in the DB Connector node instead of the build in driver and should be able to connect to the db.
If you still have problems connecting to the db send me your KNIME log with the error message and I can have a look at it.
Thanks
Tobias
Thanks for the fast response and advice. The initial connection is working now (even with encryption enabled) - I can see all tables of the database in the DB Table selector node but the reader fails to grab the respective data (with and without encryption):
WARN DB Reader 0:225 Output data type mappings are missing for columns: COLUMN1 (NVARCHAR), COLUMN2 (NVARCHAR), COLUMN3 (NVARCHAR), COLUMN4 (NVARCHAR)
Please do not feel rushed to solve the issue as I am aware that I am playing with a beta software and I am fine to use the sql driver with username&password to discover the new possibilites in the final release of the nodes.
Iām glad to hear that it works now. With the new framework we also test a new type mapping framework. The framework allows users to influence the mapping from external e.g. db types to KNIME types and vice versa. The framework comes with default rules for the most common types. However if a none standard type is used the user needs to specify the mapping manually via the type mapping table. So to solve the problem open the dialog of the DB Reader node and add a mapping for the NVARCHAR type (see screenshot).
Bye
Tobias
