Hi all,
I have seen various posts floating around relating to the connection between SQL server and KNIME. I have tried implementing those solutions, to no avail. I am pretty sure the JDBC driver is set up correctly…
The error:
Execute failed: Windows logins are not supported in this version of SQL Server. ClientConnectionId:db8a7826-5a17-4479-9c49-0bffd58501c5
The set up:
Any help is super appreciated, it would be huge if we could figure this out at our site.
Hi @solcani2, if you switch to the JDBC parameters tab, have you got a setting there for IntegratedSecurity?
If so, you will need to remove it as this tells it to use Windows Authentication.
Alternatively in your dB connection string, or again in jdbc parameters, does it say
Trusted_Connection=True
If it does, try removing it.
I can’t at the moment think of any other reason why it would try to use Windows authentication, although there might be something else.
So I do have that parameter, because at work we use windows to log into SQL server
I tried following the parameters work suggested, but they aren’t working. For reference:
Hi @solcani2 , if you want to use windows to log into the server, keep the setting IntegratedSecurity, but use None/native authentication:
You can’t pass your windows credentials via the Username & password fields. This is for a db user credentials.
I tried that, but it says i need the log in information which makes sense. I’ve also tried using the DB connector node, to no real success there either. I’ve found the jdbc link from our azure account directly and entered that with my username and password, but then I get this error: Execute failed: Failed to load MSAL4J Java library for performing ActiveDirectoryPassword authentication. I saw a post about that but it was relating to the JDBC driver install, which I think I have set up correctly?
Hi @solcani2 , it does NOT make sense that it requires you to pass credentials if you are using windows login, that of course assuming that you have authenticated into windows.
Again, entering the username & password is for a db user.
Also, you should not have to access Active Directory at that point. The SQL connector should be able to check for your windows credentials locally.
I did a few search on the forum, and may be this thread can help:
It looks most likely that it could be a driver issue
Not a direct solution I know. We faced also a lot of trouble connecting Knime to various backend systems. Most likely problems are driver related. The annoying and tedious task is to find the right settings for the right driver version. That fits to the needs (driver and security) for your target backend you want to connect to.
@solcani2, which version of KNIME AP are you using? Version 4.4 comes with the jTDS driver for sql server, and as you will see from a post I made on a related issue, I found the official Microsoft drivers were unreliable when it came to using Windows authentication whilst the jTDS driver just worked.
Have you tried using jTDS?
I have not. I am a real novice when it comes to all this, so apologies if I am slow to understand. I am using KNIME 4.2.5 (what our company currently supports) on a local machine, which I saw in your post also poses some problems. I was using the jdbc 9.4.0 from the microsoft site, so I don’t think I have tried the jTDS driver. How would I go about getting that, I am a bit lost there.
@bruno29a I guess again this is where my lack of knowledge comes through. I assumed I would have to pass credentials because when connecting to our SQL server (not through KNIME) I always log in (via our company issued windows account). I also figured it would function like other connector nodes (ie the sharepoint node that you log into as well). I did go through and follow the steps in that forum (copying the .dll into the 3 locations) but that didn’t seem to do anything
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:
https://sourceforge.net/projects/jtds/files/jtds/1.3.1/jtds-1.3.1-dist.zip/download
… this should download a zip file called jtds-1.3.1-dist.zip
Copy/move that file to to a folder of your choosing on your disk, e.g.
c:\jdbc-drivers
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 “jtds-windowsauthentication”
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 jtds-1.3.1-dist.
If you are on 64 bit windows, you will navigate into the x64 folder.
Go down the subfolders and find the file ntlmauth.dll
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:
ID: jTDSDriver
Name: SQL Server jTDS Driver
Description: jTDS 1.3.1 Driver for MS SQL Server
URL template: jdbc:jtds:sqlserver://<host>:<port>/[database]
(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!).
Press OK
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:
select 0
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:*
jdbc:jtds:sqlserver://takahenas:1433/AdventureWorks2017;instanceName=TAKDEV
i.e.
jdbc:jtds:sqlserver://<host>:<port>/<database>;instanceName=<instance>
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!
