All Forums Teradata Studio
rupert160 131 posts Joined 09/10
25 May 2015
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.HiveCo nnection.openKnoxConnection(HiveConnection.java:330)
    at com.teradata.datatools.hadoop.hive.connectivity.HiveCo nnection.createConnection(HiveConnection.java:181)
    at org.eclipse.datatools.connectivity.DriverConnectionBas e.internalCreateConnection(DriverConnectionBase.java: 105)
    at org.eclipse.datatools.connectivity.DriverConnectionBas e.open(DriverConnectionBase.java:54)
    at com.teradata.datatools.hadoop.hive.connectivity.HiveCo nnection.open(HiveConnection.java:144)
    at com.teradata.datatools.hadoop.hive.connectivity.HivePi ngFactory.createKnoxConnection(HivePingFactory.java: 37)
    at com.teradata.datatools.hadoop.hive.connectivity.PingKn oxJob.createTestConnection(PingKnoxJob.java:30)
    at com.teradata.datatools.hadoop.hive.connectivity.PingJo b.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?

rupert160 131 posts Joined 09/10
25 May 2015

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.

Chuckbert 78 posts Joined 07/09
27 May 2015

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.

rupert160 131 posts Joined 09/10
01 Jun 2015

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.
 

rupert160 131 posts Joined 09/10
01 Jun 2015

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

Chuckbert 78 posts Joined 07/09
01 Jun 2015

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).

rupert160 131 posts Joined 09/10
02 Jun 2015

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.

 

rupert160 131 posts Joined 09/10
02 Jun 2015

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

rupert160 131 posts Joined 09/10
02 Jun 2015

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

You must sign in to leave a comment.