Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

Teradata Studio
Enthusiast

Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

I have been testing the latest build of Teradata Studio 15.10 and I'm having trouble with both JDBC and Knox - I've split this post into two as I'm going to concentrate on the Knox failure here. Currently with some port mapping I can get my local Ubuntu instance to shoot out the following and get a successful JSON object response.

curl -i -k -u user:password -X GET 'https://localhost:8443/gateway/sandbox/webhdfs/v1/?op=LISTSTATUS'

However, the same localhost/8443/sandbox/user/password settings on the Knox config returns a failed ping:

java.lang.Exception: Could not establish connection to jdbc:hive2://localhost:8443/;ssl=true?hive.server2.transport.mode=http;hive.server2.thrift.http.path=gateway/sandbox/hive: javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated

    at com.teradata.datatools.hadoop.hive.connectivity.HiveConnection.openKnoxConnection(HiveConnection.java:330)

    at com.teradata.datatools.hadoop.hive.connectivity.HiveConnection.createConnection(HiveConnection.java:181)

    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)

    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)

    at com.teradata.datatools.hadoop.hive.connectivity.HiveConnection.open(HiveConnection.java:144)

    at com.teradata.datatools.hadoop.hive.connectivity.HivePingFactory.createKnoxConnection(HivePingFactory.java:37)

    at com.teradata.datatools.hadoop.hive.connectivity.PingKnoxJob.createTestConnection(PingKnoxJob.java:30)

    at com.teradata.datatools.hadoop.hive.connectivity.PingJob.run(PingJob.java:42)

    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

Now I don't know why it gives a jdbc/hive.server2.thrift string there, I assume that's what is requried, it's certainly updating the port correctly, don't know why it's JDBC but that's clearly an implementation artifact. Thougths?

8 REPLIES
Enthusiast

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

addendum to that although knox user:password does not successfully ping, WebHCat (values locked in using port 8443) using that user actually is successful. Somehow it must be authenticating using web-hcat setting but not pinging from the knox tab.

Teradata Employee

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

In your curl command, you are using the "-k" option that says to allow insecure SSL connections. Studio doesn't have the ability to allow insecure connections. The connection is being rejected because your Java certificate store doesn't trust the self-signed certificate used by the Sandbox's Knox server. 

You will need to extract the certificate used by the Knox server and install it in your Java certificate store in order to connect through Knox when it is using a certificate that is not signed by a certificate authority trusted by the Java runtime environment.

When you are connecting through Knox, you have to set JDBC to use HTTP rather than binary mode.

Enthusiast

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

Many thanks, this is good information, I'm just a little unsure how to do this. I have setup a .keystore file with the following command with the intent of importing a keystore value from the _default-credentials.jceks file. Is that correct?

$ keytool -genkey -alias myKeyStore -keyalg RSA -keystore .keystore

seems to have created a .keystore file but

$ keytool -importkeystore -storetype JCEKS -srckeystore Downloads/keystore/_default-credentials.jceks -destkeystore .keystore

Enter destination keystore password:  

Enter source keystore password:  

keytool error: java.io.IOException: Invalid keystore format

naturally I don't have the source password because it wasn't generated by me. Are you able to point me to what I need to do here? I've just got a vanilla Sandbox instance, and pulled out the jceks files from the /var/lib/knox/data/security/keystores.

Enthusiast

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

addendum my client is Linux (ubuntu) but I'd probably need instructions for both linux and windows assuming the invocation is different.

Teradata Employee

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

I found a web page that explains how to extract the certificate from a web server at:

http://curl.haxx.se/mail/archive-2011-12/0005.html

Following those instructions, I ran the command:

openssl s_client -connect localhost:8443 > /tmp/server.pem < /dev/null

You'll then need to edit the resulting server.pem file. You need to remove what is before the "-----BEGIN CERTIFICATE-----" and after the "-----END CERTIFICATE-----" lines (keep those lines).

Then you can install the certificate into your Java keystore to allow connections to that server to be trusted by Java applications by running the command:

$JAVA_HOME/bin/keytool -importcert -alias "HDP Sandbox self-signed certificate" -file /tmp/server.pem -keystore $JAVA_HOME/jre/lib/security/cacerts

The keytool will ask for the keystore's password. It is "changeit" (without quotes) unless you have changed it.

Java on Windows has the same keytool command (you have to have the Java JDK installed, not just the JRE).

Enthusiast

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

This is great progress for me Chuckbert!

The following commands were executed:

#pull the certificate from the server
openssl s_client -connect localhost:8443 > /tmp/server.pem < /dev/null
#You'll then need to edit the resulting server.pem file. You need to remove what is before the "-----BEGIN CERTIFICATE-----" and after the "-----END CERTIFICATE-----" lines (keep those lines).
vim /tmp/server.pem
#find which cacerts directory is required (important if you have multiple versions of java
which java
java -version
sudo find / -name "cacerts"
#create a keystore
keytool -genkey -keyalg RSA -alias selfsigned -keystore /home/rupert/.keystore -storepass figjam -validity 360 -keysize 2048
#import the keystore
#use password of changeit for the password value - it's the certificate password
sudo keytool -importcert -alias "HDP Sandbox self-signed certificate" -file /tmp/server.pem -keystore /usr/lib/jvm/java-7-openjdk-amd64/jre/lib/security/cacerts

This now means that JDBC pings and Knox pings, however the tree browser still returns blank, and "show databases;" returns the following error:

Executed as Single statement.  Failed [ 
Elapsed time = 00:00:00.000

STATEMENT 1: GROUP Statement failed.

So I guess I now have another group of things to look at. I do have multiple instances of java available, but I clearly am triggering the right java instance because the above progress was made.




Enthusiast

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

Addendum I just checked the CLI and I didn't get the expected response:

(✓) rupert@x220:~ $ curl -i -u rupert:mypass -X GET 'https://localhost:8443/gateway/sandbox/webhdfs/v1/?op=LISTSTATUS'
curl: (60) SSL certificate problem: self signed certificate
More details here: http://curl.haxx.se/docs/sslcerts.html

curl performs SSL certificate verification by default, using a "bundle"
of Certificate Authority (CA) public keys (CA certs). If the default
bundle file isn\'t adequate, you can specify an alternate file
using the --cacert option.
If this HTTPS server uses a certificate signed by a CA represented in
the bundle, the certificate verification probably failed due to a
problem with the certificate (it might be expired, or the name might
not match the domain name in the URL).
If you'd like to turn off curl's verification of the certificate, use
the -k (or --insecure) option.

(✓) rupert@x220:~ $ keytool -list
Enter keystore password:

Keystore type: JKS
Keystore provider: SUN

Your keystore contains 1 entry

selfsigned, 03/06/2015, PrivateKeyEntry,
Certificate fingerprint (SHA1): 24:70:4C:FD:5B:D8:BE:43:5A:F6:13:5F:02:3B:44:B5:65:6D:CD:50

(✓) rupert@x220:~ $ sudo keytool -importcert -alias "HDP Sandbox self-signed certificate" -file /tmp/server.pem -keystore .keystore
Enter keystore password:
Owner: CN=localhost, OU=Test, O=Hadoop, L=Test, ST=Test, C=US
Issuer: CN=localhost, OU=Test, O=Hadoop, L=Test, ST=Test, C=US
Serial number: f8f90e2df1a6fcb4
Valid from: Tue Apr 14 19:34:30 AEST 2015 until: Wed Apr 13 19:34:30 AEST 2016
Certificate fingerprints:
MD5: 65:46:77:C3:7C:41:44:50:22:9B:22:60:73:2C:CB:69
SHA1: 9B:10:D0:19:27:2F:47:2C:BE:ED:8C:6C:C0:9F:64:5D:FA:95:76:34
SHA256: F9:A7:DC:A0:0C:8D:86:F2:AB:17:5A:B7:87:10:F0:A7:64:66:A6:69:45:ED:7D:CD:C9:10:5E:13:04:E8:D3:04
Signature algorithm name: SHA1withRSA
Version: 3
Trust this certificate? [no]: yes
Certificate was added to keystore

(✓) rupert@x220:~ $ curl -i -u rupert:mypass -X GET 'https://localhost:8443/gateway/sandbox/webhdfs/v1/?op=LISTSTATUS'
curl: (60) SSL certificate problem: self signed certificate
More details here: http://curl.haxx.se/docs/sslcerts.html

curl performs SSL certificate verification by default, using a "bundle"
of Certificate Authority (CA) public keys (CA certs). If the default
bundle file isn\'t adequate, you can specify an alternate file
using the --cacert option.
If this HTTPS server uses a certificate signed by a CA represented in
the bundle, the certificate verification probably failed due to a
problem with the certificate (it might be expired, or the name might
not match the domain name in the URL).
If you'd like to turn off curl's verification of the certificate, use
the -k (or --insecure) option.

(✓) rupert@x220:~ $ ssh hetzner
Last login: Wed Jun 3 01:20:09 2015 from 1.152.97.62

(✓) rupert@CentOS-65-64-minimal:~ $ curl -i -u rupert:mypass -X GET 'https://sandbox:8443/gateway/sandbox/webhdfs/v1/?op=LISTSTATUS'
curl: (60) Peer certificate cannot be authenticated with known CA certificates
More details here: http://curl.haxx.se/docs/sslcerts.html

curl performs SSL certificate verification by default, using a "bundle"
of Certificate Authority (CA) public keys (CA certs). If the default
bundle file isn\'t adequate, you can specify an alternate file
using the --cacert option.
If this HTTPS server uses a certificate signed by a CA represented in
the bundle, the certificate verification probably failed due to a
problem with the certificate (it might be expired, or the name might
not match the domain name in the URL).
If you'd like to turn off curl's verification of the certificate, use
the -k (or --insecure) option.

(✓) rupert@CentOS-65-64-minimal:~ $ exit
logout
Connection to hetzner closed.

(✓) rupert@x220:~ $ keytool -list
Enter keystore password:

Keystore type: JKS
Keystore provider: SUN

Your keystore contains 2 entries

selfsigned, 03/06/2015, PrivateKeyEntry,
Certificate fingerprint (SHA1): 24:70:4C:FD:5B:D8:BE:43:5A:F6:13:5F:02:3B:44:B5:65:6D:CD:50
hdp sandbox self-signed certificate, 03/06/2015, trustedCertEntry,
Certificate fingerprint (SHA1): 9B:10:D0:19:27:2F:47:2C:BE:ED:8C:6C:C0:9F:64:5D:FA:95:76:34

So I'm not sure what keystore it's using - given I use cacerts for the runtime, and ~/.keyserver as two places and neither could be excecuted without "-k" option

Enthusiast

Re: Teradata Studio 15.10.00.201504221135 connectivity to Sandbox 2.2(2.2.4.2-2) via Knox - failure inducates JDBC

Success!

Francine has advised that the ";" at the end of a statement currently causes an error, (which might be related to the lack of a database tree browser working too). But I can definitely get a hive query to work now through knox.

JDBC, although it can ping (as can TDCH and WebHCat) seems to fail due to not being able to connect to WebHCat. which is a port 8443 issue it seems. So use Knox at this point, not JDBC due to this bug.

Thankyou Chuck and Francine