Issue with connecting to MS SQL Server with different driver :JDBC v10.2.0 and jTDS

Hello there!

I’m using KNIME since almost a year for a project where we have to get access to a Microsoft SQL Server 2016 which is provided not by my company. I try to connect via the Microsoft SQL Connector node.
Since an update on the server I can not connect to the database.
The server uses Windows Authentication and I’m running a x64-based W10-OS.
It doesn’t matter if I’m working at the office or at home (VPN connection is always given).
I installed Java 8.
I have KNIME 4.5.1
I don’t have access to the server logs to look up what the errors from the server side are.

I searched related topics in the forum but nothing really helped.
I tried to connect with two different drivers:

1. jTDS Driver of the node

  • After the update I got this message: I/O Error: DB server closed connection.
  • Added to the JDBC Parameter: ssl = request and integratedSecurity = true
  • Got this error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
  • Added to the JDBC Parameter: domain = ‘MyCompanyDomain’ (Start → Settings → Account → Your Info → the combination before ‘\MyWindowsUserName’ under my name). tried this with and without the backslash.
  • Still get the error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
  • No changes with: encrypt = true and trustServerCertificate = true

→ after this steps I don’t know which additional parameters I could set and tried to include the latest JDBC driver of Microsoft like in a different forum posting mentioned.

2. Include JDBC Driver for Microsoft SQL Server v10.2.0
After the unsuccessful attempts of the jTDS driver I included the official JDBC driver for MSSQL v. 10.2.0 using the steps of this forum posting: https://forum.knime.com/t/database-reader-jdbc-sql-server-login-failed/6245/2

My driver URL is: jdbc:sqlserver//‘host’:‘port’;databaseName=[database]
hostname, port number and database name didn’t change.
I can connect with SQL user credentials to another database but not with my Windows credentials.

I added into the knime.ini this lines:
-Djava.library.path=C:\Program Files\Java\jre1.8.0_311\bin (cause I dont have the KNIME/jre/bin path)
-Djava.library.path=C:\Program Files\Microsoft JDBC-Treiber 10.2 for SQL Server\deu\auth\x64

paste the […]auth.dll into the Java\jre1.8._311\bin and rebooted my computer as written in the posting above.

Steps I tried:

  1. trustServerCertificate = true (without I get this: The driver could not connect securely to SQL Server using Secure Sockets Layer (SSL) encryption. Error: ‘PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target’.)
  2. Error with trustServerCertificate = true: Login failed for user ‘’ (No username inside).
  3. added integratedSecurity = true Error: This driver is not configured for integrated authentication.
  4. changed integratedSecurity = true to authentication = ActiveDirectoryInteractive. Eorror: **Login failed for user ‘’
  5. added authenticationScheme = NativeAuthentication with and without authentication = Active… Error: see step 4.
  6. Using Credential Input node with my Windows credentials. Error: see step 4.
  7. Every parameter above + integratedSecurity = true. Error: see step 3.

I tried to find usefull parameter on the documention of Microsoft but it seems that the parameters above are the only usefull params.

Moreover, I found this and tried but without the trustStore parameters without any success.

Now I’m at a point where I don’t know what to do and start being a little desperate.

I would be very happy if anyone can tell what the matter could be or rooting me through the troubleshooting.

Best regards
Andreas

Hi @andi_braun96 , if you are already connected to the network (office or VPN from home) and are on the same domain as the MSSQL server, you should not have to enter any of these parameters, and not even enter credentials.

You should choose no credentials in the connector setting, and that should be it.

“Since an update on the server I can not connect to the database.” Do you mean the db server, or knime server? And are you connecting from a Knime server?

Hi @bruno29a, thanks for responding.
The SQL Server is not on the same domain. Without any parameters I get this error:

The driver could not connect securely to SQL Server using Secure Sockets Layer (SSL) encryption. Error: ‘PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target’

Do you mean the db server, or knime server? And are you connecting from a Knime server?
I’m connecting from the KNIME software not from the server.
The DB server got an update.

Hi @andi_braun96 , I see.

In that case you have to provide user name and password, and you need these 2 JDBC parameters:
domain = MyCompanyDomain
integratedSecurity = true

That should work.

Have you tried to connect to the db server via a db client?

PKIX error rears its head again. :slight_smile: Check here:

May need to coordinate a bit with the IT team hosting the DB server.

2 Likes

Hi @bruno29a,

I got the information both db server and client domain are the same.

I tried your suggestion with both parameters and the error occures:
With jTDS Driver: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
With JDBC: This driver is not configured for integrated authentication.

@ScottF What I can’t understand is before the update I used the jTDS driver without any parameters and it worked well. After the update the issues occure. The JDBC driver was an idea to overcome this issue. But it failed. I can’t add a certificate to Java everytime the certificate changes on server side. This is not a good solution in my optinion.

Hello @ScottF,

I included the certificates but there is still no progress. What I know is that there is a issue with the Windows Authentication. I get this errors:
with integatedSecurity = true:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

with integratedSecurity = false:
Login failed for user ‘’.

What I know is that the provider of the server was changed from SQLOLEDB.1 to MSOLEDBSQL.1. What I don’t understand is that I’m in the same domain as the server and that there is no user provided during login phase.
Do you know how this can be solved or do you have any suggestions what else I could try?

Hello @andi_braun96 ,
Regarding the error message “The login is from an untrusted domain …” I could find the following information which point to a potential DB setup problem:

Regarding the native authentication setup. You should be able to use the jTDS driver as before. However I would suggest in general to use the official driver. TO do so install the official driver extension as described in the database documentation. This includes all necessary files for native authentication. So you don’t need to add any lines to the knime.ini file.
Once installed follow the instruction further down in the documentation on how to configure the connector for native authentication e.g. you need to specify the following entry in the JDBC Parameters tab: integratedSecurity with value true. In addition add the trustServerCertificate = true parameter to eliminate the PKIX path building failed… error as described here.

Bye
Tobias

1 Like

Hello @tobias.koetter,

thank you for your input.
I already installed the Knime JDBC server extension and tried to connect through the official JDBC drivers (v. 7.2 and v 9.4) and I still get the error that the “Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.”

The access settings of server is configurated with SQL User and Windows Authentication.

The 3 links you posted above are all mentioning that this error occures due to Kerberos settings or implies to use Kerberos but the server does not have Kerberos and it isn’t wished for the future to use Kerberos.
I still look for some solutions to get the access to the server without SQL user credentials (what isn’t wished either).

Bye
Andreas