Proper jdbc connection string to connect to impala with kerberos and SSL

[#1]

Hello, 

I am able to connect without a problem to Impala  via ODBC with the following connection string: 

'Driver=Cloudera ODBC Driver for Impala;Host=myserver.mycompany.com;Port=21050;AuthMech=1;SSL=1;KrbRealm=MYCOMPANY.REALM;KrbFQDN=myserver.mycompany.com;KrbServiceName=impala;TrustedCerts=D:/_DATOS/myserver.mycompany.com.pem'

Now I am trying to do the same via jdbc, I am using the JDBC4 driver (latest version 2.5.31.1051, the class is com.cloudera.impala.jdbc4.Driver) .

I am trying to follow the instruccions here: Cloudera JDBC-Driver for Impala Install Guide

To setup the connection jdbc string: 

I have tried several alternatives, such as 

jdbc:impala://myserver.mycompany.com:21050/proceso;AuthMech=1;SSL=1;KrbHostFQDN=myserver.mycompany.com;KrbRealm=MYCOMPANY.REALM;KrbServiceName=impala;TrustedCerts=D:\_DATOS\myserver.mycompany.com.pem

which would be the closest analog to my  working ODBC string. 

When I try to connect I get the following exception: 

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: [Simba][ImpalaJDBCDriver](500164) Error initialized or created transport for authentication: [Simba][ImpalaJDBCDriver](500169) Unable to connect to server: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

Then I noticed that the TrustedCerts property doesn't seem to be there for JDBC. So I tried replacing this by cacerts and also by jssecacerts as this is mentioned on page 18 of the guide above: 

jdbc:impala://myserver.mycompany.com:21050/proceso;AuthMech=1;SSL=1;KrbHostFQDN=myserver.mycompany.com;KrbRealm=MYCOMPANY.REALM;KrbServiceName=impala;cacerts=D:\_DATOS\myserver.mycompany.com.pem

The stack trace is the same as before...

Any ideas on how to properly build the jdbc string given the fully working ODBC string above? 

(By the way I have MIT Kerberos Ticket manager installed and I have been careful to define the  KRB5CCNAME environment variable pointing to the tickets file.

Many thanks in advance.

[#2]

Update: I managed to connect.  

The one thing I did  was to add the SSL certificates I had on my .pem file directly to the "certificate store" used by Java.

It did this via the following command:

keytool –import –alias myserver  -keystore “C:\Program Files\Java\jre1.8.0_40\lib\security\cacerts”  –file  myserver.mycompany.com.pem

when asked I used the default password for the cacerts file which is: changeit

After that I restarted my Java client application and was able to connect. 

The client application I am trying to use is SquirrelSQL.

After connecting, the app hangs for a long while and finally comes back. I am assuming this caused by our database already having around 2000 tables and SquirrelSQL by default fetches information about all the schemas...

It seems there are two ways around this.

One is to configure it to not load any schemas at all. But then, it hangs when writing an SQL statement, not sure why.

Another is to configure it to load and caches all schemas. Then only the first time one opens the schemas list it hangs as before, but the second time it uses the cache and doesn't reload and thus it is very responsive.

However, in this casem  Squirrel-sql still hangs at other places. Not sure whether this is due to the app itself or to the fetching of table metadata from being slow ... 

Does anybody have an idea about this or suggestions about other Java based SQL clients that can connect to Impala (and give you full control of the connection string) and might work better? 

[#3]

I'm getting same error with BO

Though I tried  testing the driver and connectivity from R [though in background that is using JDBC only]. Following code works without any error.

drvH <- JDBC(driverClass = "com.simba.hive.jdbc4.HS2Driver", 
             classPath = normalizePath(list.files("Drivers/BO-Simba/BO_Drivers/hive012simba4server1/", pattern = ".jar$", full.names = T, recursive = T)))

connH <- dbConnect(drvH, "jdbc:hive2://myserver.mycompany.org:10000;AuthMech=1;KrbRealm=MYREALM.COM;KrbHostFQDN=master1.mycompany.org;KrbServiceName=hive")

dbGetQuery(connH, "show databases")

But following code 

drvI <- JDBC(driverClass = "com.simba.impala.jdbc4.Driver", 
             classPath = normalizePath(list.files("Drivers/BO-Simba/BO_Drivers/impala10simba4/", pattern = ".jar$", full.names = T, recursive = T)))


connI <- dbConnect(drvI, "jdbc:impala://slave1.mycompany.org:21050;AuthMech=1;KrbRealm=MYREALM.COM;KrbHostFQDN=master1.mycompany.org;KrbServiceName=impala")
# getting error [Simba][ImpalaJDBCDriver](500164) Error initialized or created transport for authentication: Unable to connect to server

Gives the error

[Simba][ImpalaJDBCDriver](500164) Error initialized or created transport for authentication: Unable to connect to server

Kindly help if you know the reason. I have not enabled SSL in the cluster. I have Kerberos and Sentry in CDH 5.9 [OS RedHat 6]. Client is as of now one of the nodes in the cluster [minial firewall intervention].

Hive works but Impala gives these problem. I have tried with Cloudera Drivers too [again Hive works not Impala].

[#4]

Hello,



I was getting essentially the same error problem very recently.

I finally managed to get it to work like so:


String DRIVER = "com.cloudera.impala.jdbc41.Driver"; // jdbc4 was giving me trouble, switching to jdbc41 solved the problema (I am using the lastest, version 2.5.36, of this )

String jdbcConnStr = "jdbc:impala://myserver.mycompany.corp:21050/default;SSL=1;AuthMech=1;KrbHostFQDN=myserver.mycompany.corp;KrbRealm=MYCOMPANY.CORP;KrbServiceName=impala"


I suppose that if you are not using SSL=1 but only Kerberos, you just drop that part from the connection string and don't worry about setting up SSL certificates in the java key store... which is yet another hassol

However in order to get Kerberos to work properly we did the following:

* Install MIT Kerberos 4.0.1, which is a kerberos ticket manager. (This is for Windows)

* This ticket manager asks you for authentication everytime you initiate a connection, creates a ticket and stores it in a kerberos_ticket.dat binary file, whose location can be configured somehow but I do not recall exactly how.

* Finally, before launching your JAVA app you have to set an environment variable KRB5CCNAME=C:/path/to/kerberos_ticket.dat. In your java app, you can check that the variable was correctly set by doing System.out.println( "KRB5CCNAME = " + System.getenv( "KRB5CCNAME" ) ) // don't just copy and paste this code without fixing the double quotes first, my e-mail client messes them up. If you are working with eclipse or other IDE you might even have to close the IDE,set up the environment variable and start the IDE again.

o NOTE: this last bit is very important, I have observed that if this variable is not properly set up, the connection wont be stablished...


* In Linux, instead MIT Kerberos 4.0.1, there is a program called kinit which does the same thing, although without a graphical interface, which is even more convenient for automation.

I would be very interested in knowing whether it is posible to make this work without installing MIT Kerberos. Maybe Windows just stores the tickets in some other file, whenever you authenticate and you can just use the path to that file for KRB5CCNAME...

I have never used the Simba Driver. Does it offer any advantages over the Cloudera one?

[#5]

Hi ,

We are stuck with the same issue. We have installed

1) CDH 6.1 cluster

2) MIT Kerberos enabled

3) SSL enabled for impala

WE are getting the error

[Simba][ImpalaJDBCDriver](500164) Error initialized or created transport for authentication: [Simba][ImpalaJDBCDriver](500169) Unable to connect to server: GSS initiate failed
Also, could not send response: org.apache.thrift.transport.TTransportException: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

jdbc:impala://Loadbalancer.amazonaws.com:21050/default;AuthMech=1;KrbHostFQDN=master2-impala-146.com;KrbRealm=PRODIMPALA.COM;KrbServiceName=impala;SSL=1;CAIssuedCertNamesMismatch=1;SocketTimeout=10;loglevel=6;logpath=d:\;TrustedCerts=D:\CA-Certs\\cacerts

Also imported the pem file into /jre/lib/security/cacerts

Tried with both impala jdbc 4 & 4.1 driver.

I tried with jks file too. It's throwing GSS initiate error.

Any help would be really appreciated.

Regards,

Balaji