Faliure to connect to SQL Server Database with Kerberos

KNIME Analytics Platform
1

Hi All

First off, thanks in advance for any help received. It’s much appreciated.

Second, apologies in advance for the long post. I’ve been investigating a lot, so want to be clear about what I’ve found and tried.

Third, apologies if my knowledge level is low – I’ve come to KNIME from a BI\Tableau\Business Objects\Alteryx background, rather than a Java\Python\R programming background. My focus has been on operations and performance, not on development. So whilst I’m reasonably competent in SQL and dabbled in R, that’s my limit. I’m not an app developer, nor am I a seasoned data scientist.

So, before I start, I should also note that I have read – and found no answer in – the other similar threads relating to SQL server and KNIME.

What am I trying to do? I’m on a Windows 10 machine, trying to connect KNIME to a named database running on a SQL Server 2008, which is running on a Server 2012 remote host. I’m trying to do this with Kerberos enabled, so I can use my windows logon and password.

Currently, I can do the following:

  • Connect KNIME to the database without Kerberos using a dedicated username and account. This works fine, but that’s not the point; I want to use my windows account
  • Connect to the remote server and use SQL Server Management Studio using my windows logon
  • Connect Tableau to the server and database from my local machine pretty seamlessly

So I can use KNIME with a separate account, and use my windows logion to access the database with other programs. It therefore seems to me that it is something to do with Kerberos that is the issue.

What have I done?

  • I’ve downloaded the most recent (as at 2017-05-28; this is v4.2) jdbc driver from the Microsoft website and extracted it to ‘my documents’.
  • Created brand new driver profiles for SQL Server 2008, 2012 and 2014 (for java 1.7 and 1.8, i.e. 6 profiles) using the jar files I got from the above. I did this in ‘Preferences --> Data Management --> Connectivity --> Driver Definitions’
  • Added the whole folder (i.e. ‘Microsoft JDBC Driver 4.2 for SQL Server) to ‘Preferences-->KNIME-->Databases’
  • Checked with the DB admin about the connection logs. Those without ‘integratedsecurity = true’ (and optionally ‘authenticationscheme = javakerberos’) were logged but denied; those with ‘integratedsecurity = true’ (and optionally ‘authenticationscheme = javakerberos’) were not even logged…
  • Tried to create a new connection profile (in ‘File-->new-->Connection Profile-->SQL Server’). This has boxes for the database and server, which fill out the connection string automatically; this is the same as that I put into the database reader manually. (Curiously, these boxes only appear for the SQL Server 2008 driver profile; not the 2012/14 ones; this may be relevant). It also has a tick box for integrated authentication. When ticked, it adds the ‘integratedsecurity = true’ option into the connection string. I can test the connection – which subsequently fails.

I do get an error message this last way; it says:

com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.

I’ve googled this; there are many answers, some more decipherable than others, but the more common one involves putting the 32-bit sqljdbc_auth.dll file into the JRE folders. With the help of my friendly db admin, I put it in the following places:

  • C:\Program Files\KNIME\jre\bin
  • C:\Program Files (x86)\Java\jre1.8.0_102\bin

No dice with either. Hence me here. Does anyone have any suggestions?

Again, many thanks in advance…

Neil

2

Hi Neil,

Have you tried to specify the path to the sqljdbc_auth.dll file via the Java property -Djava.library.path? If not add the property with the path to the correct dll as described in the Connecting with Integrated Authentication On Windows section to the knime.ini file which is located in the KNIME installation directory.

Bye

Tobias

3

Put sqljdbc_auth.dll in  C:\Program Files\KNIME\jre\bin. Or corresponding KNIME installation folder.

4

Hey Neil, I had the same problem. I solved it like this on my local KNIME client:

  1. Register the MS SQL Server JDBC driver (in my case "sqljdbc42.jar") via File > Preferences > KNIME > Databases
  2. Copy sqljdbc_auth.dll into C:\Windows\System32
  3. Restart KNIME client
5

Hi Tobias, izaychik and Ben

First off, apologies for not replying sooner. I didn't realise I had any responses (have checked my notification settings...) so assumed there were none. So I'm very grateful for what you've put.

TLDR: despite the help, wasn't able to get MS-Driver/kerberos running but did manage to get the open source (JTDS) driver running with NTLM logon - so fixed anyway, but still a mystery as to whats going on. Happy with the JTDS though.

Long version:@Tobias - thank you for the suggestion. I wasted a fair few hours on the eclipse forums trying to set the environment variables until I actually read what you'd written, and saw the reference to the .ini file. D'OH! I then tried every combination of location and path, with or without quotation marks, with or without leading '-', every combination of x86 and x64. Alas, all failed.

@izaychik + @ben Thank you very much for all your suggestions. I tried each and every one of them. Drove my db admin mad asking for permission to move DLL's around.

However I did see a reference to the open-source JTDS driver; which I was unaware of until now - I was able to follow the instructions for NTLM... and lo and behold... it worked.

So - full functionality for SQL server. Thank you so much for your help - it is very much appreciated. I now have new issues (completely unrelated)... but those are for a separate thread.

Neil