Database Reader: JDBC SQL Server login failed

KNIME Analytics Platform
1

Hi,

I have managed to resolve most issues myself by following the available guidance, but I am unable to login to my local instance of an SQL database using either 1) windows credentials or 2) using a working SQL Studio user name and password.  Please see attached images.

For the windows credentials example, I have followed the guidance about where to locate sqljdbc_auth.dll (same folder as the JDBC driver), I have also amended the KNIME ini file to point it towards the right location.  Within the Database URL I have also include the <intergratedSecurity=True> statement.

What am I doing wrong?

  • Error using SQL user name:
    • ERROR Database Reader      2:7        Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'Gavin'. ClientConnectionId:9537ef10-4d9c-414b-9821-766fbaae13de
  • Error used windows credentials:
    • ERROR Database Reader      2:7        Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ''. ClientConnectionId:117fd1b7-52ec-4941-9ea7-9d6fec29e660

Thanks

2

Hi,

Have you tried using the SQL Server Connector node rather than the Database Reader?

Cheers,

Roland

3

Thanks Roland - as you can see - now resolved.  I cannot see the SQL Server Connector node - is that it's full name, or is it an add in?

4

Update - a total of 12 hours of experimentation later(!) and I have discovered that the following works.  I am posting this answer so that it might helps others.

1.  Remove all other references to Microsoft JDBC Driver in Knime->preferences->database, and only reference the latest JDBC file, i.e:

C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\sqljdbc42.jar

This is it's default installation directory.

2.  Work out which version of Java you have installed x64 (64 bit) or x86 (32bit) - you can find this out from 'Programs and Features' in Windows (will say 64-bit, if it is 64 bit, after the program name).

3. Copy the correct Authorisation DDL from the relevant folder (in my case 64 bit), i.e.:

C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\auth\x64\sqljdbc_auth.dll

4. Paste the above file from step no. 3 into the following directories:

C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu

and 

C:\Program Files\KNIME

In theory java only needs the authorisation file to be in one location, either the next to the java executable or next to the program requiring authorisation.  It does not matter if the authorisation file is duplicated, so that's basically all I have done, even though it's somewhat cluttered/clumsy.

5. Amend the Knime ini file to include the Java library path:

-Djava.library.path=C:\Program Files\KNIME\jre\bin

6.  Close everything and REBOOT - you must reboot.  I kept changing everything over the last several hours to no effect and much frustration - you must reboot then it works perfectly. (Java issue, rather than KNIME I suspect)

When reboot has completed, open KNIME and Configure the Database reader for the SQL database driver as per other instructions on the web, e.g.:

jdbc:sqlserver://SERVERNAME;databaseName=NAME;integratedSecurity=true;

You do not need to declare the port if it's just the SQL default of 1433.

Leave user name and password blank - I have had no success using a local user name or password, but integrated security (my preference anyhow) now works.

Click fetch metadata, or execute the node and it'll work.

1 Like
5

Hi,

thanks a lot for the detailed description. I had the same problem and also spend hours...

One question: The path that you mention under step 5, shouldn't it be the same as one of the paths that you mention under step 4?

For me first it didn't work, but when I changed the path in the knime.ini file, the problem was solved (even without rebooting)

Anyway, thanks again!
Sofie

2 Likes
6

Thank you @gjhodgson and @Sofie_J. It’s been really helpful.

1 Like
7

I also changed the directory to -Djava.library.path=C:\Program Files\KNIME
It eliminated the error messages, but when using the connection to write to a new SQL table, the process failed and no table was created whilst no error message generated in the console.

8

Although it has been a while since the last post - however, I think it is worth to mention for the community that the process is still valid for KNIME 4.0.1.

For MSSQL use this URL string: jdbc:sqlserver://:;databaseName=

IMPORTANT: DO NOT FORGET TO REBOOT also in KNIME 4.0.1 - I wasted another hour as I did not follow this simple step.