Hi @solcani2 ,
This will be a bit of a marathon post, but I’ve been meaning to write something like this for some time. It pulls together information that can be found elsewhere on the forum, and in the KNIME documentation, but I’ve made it specific with examples for jtds.
(None of this should be necessary for KNIME 4.4, (nor 4.3 I think!) as it is preinstalled.
Hopefully it will show how to get the jTDS driver working. I downloaded and perform this on KNIME AP 4.2.5, so it should work for you.
The jTDS driver is available from sourceforge, and is opensource so there won’t be any licensing/cost issues.
If you go to the following link, after a brief wait of a few seconds it should download automatically:
… this should download a zip file called
Copy/move that file to to a folder of your choosing on your disk, e.g.
Right-click on the file, and you will hopefully have an option to “Extract All”
(You may have an alternative/preferred method to extract the zip file contents), but you should then see the jtds-1.3.1-dist folder in you chosen folder location:
Manually create a new folder called something like “
Now open this folder jtds-1.3.1-dist, you will see the jdbc driver jar file jtds-1.3.1.jar
Copy this file into the
jtds-windowsauthentication folder you just created.
Now navigate into one of the subfolders of
If you are on 64 bit windows, you will navigate into the x64 folder.
Go down the subfolders and find the file
Copy this file to the
jtds-windowauthentication folder created earlier.
The jtds-windowsauthentication folder should now look like this:
This folder now contains the required files to make the jtds driver work with Windows Authentication. (As mentioned earlier, on later versions of KNIME e.g. 4.3 and 4.4, the jtds driver is already installed, and none of this is necessary), but it is needed for version 4.2.5
If you now head to KNIME, and open the
Preferences option, from the
File menu, then in the Preferences window that opens, select
KNIME and then beneath this click
Databases and you should see a window something like this:
Click the “Add” button
Fill in details, as shown:
SQL Server jTDS Driver
jTDS 1.3.1 Driver for MS SQL Server
(select “mssqlserver” from the Database type dropdown)
and click the “Add directory” button, then navigate to your jtds-windowsauthentication folder from earlier and click “select folder”
Click the “Find driver classes” button
and it should then reward you with the driver class name and version number (which oddly doesn’t appear to match the file name in this case! No matter!).
By adding the folder containing both the jar file and the dll file, it ensures that the file required for windows authentication is in the “classpath” and loadable by the jdbc driver. If you weren’t using windows authentication, you could simply use the “Add file” button and only include the jar file. The method detailed above therefore works for both
windows authentication and
sql server authentication mechanisms.
On the Preferences window now press Apply and Close.
That’s the jdbc driver registration completed.
Connecting to the database
Create a new workflow.
Add a “Microsoft SQL Server Connector” node and a DB Query Reader Node. Join them together as follows:
Open the config for the Microsoft SQL Server Connector
Fill in the Hostname and Database name specific to your database environment. I’m showing my demo environment details here.
If you are using Windows Authentication, set Authentication to None/Native Authentication.
If you are using SQL Server authentication (where you have a dedicated username and password on sql server, choose “Username & Password”
If your SQL Server has a named instance you additionally need to head to the JDBC Parameters tab, and add an entry there:
In my case the instance name is TAKDEV. (You might not have an instance name, in which case you may ignore that step)
Close the config window for the connector, and then attempt to execute the db connection. If it goes green, great. It might not, It didn’t for me… so no reason why you shouldn’t also endure the same pain…
This is what I got…
That had me scratching my head, but fortunately an old post on the KNIME forum came to my rescue. This occurs when the jdbc driver doesn’t have an isvalid method (or something like that). In which case, we have to head to the config for the connector node again and open the Advanced tab, and in the “Validation query” option, type the following:
then press Apply and OK.
SIDE NOTE - if using DB Connector node, instead of Microsoft SQL Server Connector
*If you choose to use the generic “DB Connector” node instead of the Microsoft SQL Server Connector, you also have to perform this step if you get that error on connecting. (In KNIME 4.4, the pre-installed jtds driver already has “select 0” set in its options!)
Also, if you use the DB Connector, you would give the database connection as a single string of the form:*
Right… Back to the plot…
Re-execute the Microsoft SQL Server Connection node:
et voila! … hopefully
Open the config for Query Reader, and enter the following SQL statement:
select 'Hello World' as message
Press the Evaluate button. Your efforts will hopefully be rewarded with the ubiquitous greeting
Press OK, and go get yourself a drink!
I hope that works. If you try those steps out and you still get errors, then post back with specifics, as there may be additional steps because of your setup.
full disclosure: I don’t have Active Directory, and my pc on my home network isn’t set up for windows authentication so for this demo I was using a direct db connection without Windows Authentication. So I was actually cheating a little, but I did get Windows Authentication working on a different KNIME version when I tried it. It’s just I can only test that by installing KNIME AP directly on my SQL Server host machine, and I’ll only be doing that again if I really need to test that part out. I’m hoping that the above will give you everything you need, and will work for you!