Hive Connector - Problem with Kerberos Authentication based on jdbc driver 2.6

Dear KNIME Team,

after many trials with the Hive Connector Node on my Windows 10 client I don’t get any further.
We had a successful (!) connection to our Cloudera system with the Squirrel SQL client using the following URL:
jdbc:hive2://si-384l.de.bosch.com:10000/esc6;ssl=1;AuthMech=1;KrbServiceName=hive;KrbHostFQDN=si-384l.de.bosch.com;KrbRealm=RB-AA-SI01.BDPS.BOSCH-ORG.COM;mapreduce.job.queuename=root.default

based on the Hive jdbc driver com.cloudera.hive.jdbc41.HS2Driver

In the Hive Connector Node this looks like:

which (according the KNIME log file) creates the right URL

Additionally I added to the knime.ini file
-Djava.security.krb5.conf=C:/Users/grr2pl/krb5.ini

We use LSA authentication which is enabled by the Windows registry

After node execution the error message is as follows:
Execute failed: “Could not create connection to database: unable to find valid certification path to requested target”

Here is the respective log entries:
rg_knime_kerberos.log (38.9 KB)

For completeness here is the contents of the krb5.ini file:
krb5_ini.txt (1.2 KB)

I have no more idea what could be wrong ???

Any help is highly appreciated - thanks in advance
Regards
Roland

@RolandGrund

You are connecting to Hive using SSL, which requires Hiveserver to present a SSL certificate signed by a CA (certificate authority) that the client (=JDBC driver) trusts. The error you are getting basically means, that the client does not trust that CA.

This is often the case for self-signed certificates or for company-internal corporate CAs.

This can be fixed by providing a so-called “truststore” in JKS format to the JDBC driver. The truststore needs to contain the CA certificate and mark it as trusted.

Step 1: Create a JKS truststore file

If you already a truststore file of course you can skip this step.
To create the file you can use a separate program called “Portecle”:
http://portecle.sourceforge.net/

After installing and starting Portecle do the following to download the certificate:

  • Click Examine > Examine SSL/TLS Connection
  • Enter the hostname si-384l.de.bosch.com and port 10000
  • Click OK
  • Usually you don’t just get one certificate but a whole chain.
  • For each certificate, click on “PEM Encoding” > Save (save each one in its own .pem file)

Now you have the whole certificate chain in PEM format. To make a new truststore:

  • File > New Keystore
  • Select “JKS” and then click OK
  • For each saved PEM certificate:
    ** Click Tool > Import Trusted Certificate
    ** Select the PEM file and then click “Import”
    ** If asked “Do you want to accept the certificate as trusted?” then click “Yes”. Choose a uniqe alias for each one.
  • Save the newly created truststore. Choose a simple password. You only need a password here because the JDBC driver will demand that. Conceptually there is nothing worth protecting in the truststore.

Step 2: Configure the JDBC driver to use the truststore

Append the following JDBC parameters in the Hive Connector node (adjust as necessary):
SSLTrustStore=C:\path\to\truststore.jks;SSLTrustStorePwd=the_password

Hope this helps,
Björn

1 Like

Hi Björn,

perfect, this worked immediately!

Thank you so much !

Best regards
Roland