HDP 2.3.4.7
hive-odbc-native-2.6.1.1001-1.x86_64.rpm
I have a working setup using this ODBC driver on CentOS 7.2 via Knox. Now we have added an F5 proxy in front of Knox. I can successfully connect to Hive using beeline through the F5 as well as connect with other JDBC apps (dbvisualizer, for example works fine). The F5 is using a Geotrust CA for it's SSL connection. My odbc.ini file looks like this where the [Knox] section is for directly connecting via Knox and the F5 section is for going through the F5 then through Knox:
[ODBC Data Sources]
Knox=DSN for access to the production cluster using Beeline via Knox
[Knox]
Description=Hortonworks Knox Production DSN
Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
Host=api01.prod.quasar.nadops.net
port=8443
HttpPath=quasar/jupstats/hive
schema=default
ServiceDiscoveryMode=0
HiveServerType=2
AuthMech=3
ThriftTransport=2
SSL=1
TwoWaySSL=0
ClientCert=/users/mpetronic/keystore/keystore.jks
trustStorePassword=######
AllowSelfSignedServerCert=1
UseNativeQuery=1
[F5]
Description=Hortonworks Knox Production DSN (F5)
Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
Host=api.prod.quasar.nadops.net
port=443
# The knox/ part get stripped by an F5 rewrite rule then forwarded to Knox
HttpPath=knox/quasar/jupstats/hive
schema=default
ServiceDiscoveryMode=0
HiveServerType=2
AuthMech=3
ThriftTransport=2
SSL=1
TwoWaySSL=0
ClientCert=/users/mpetronic/keystore/keystore.jks
trustStorePassword=######
AllowSelfSignedServerCert=1
UseNativeQuery=1
I have a valid Geotrust CA in the OS certificate store proven by this test:
[mpetronic@vmwhnsqsrclnt01 ~]$ openssl s_client -connect api.prod.quasar.nadops.net:443 -state
CONNECTED(00000003)
SSL_connect:before/connect initialization
SSL_connect:SSLv2/v3 write client hello A
SSL_connect:SSLv3 read server hello A
depth=2 C = US, O = DigiCert Inc, OU = www.digicert.com, CN = DigiCert Global Root CA
verify return:1
depth=1 C = US, O = DigiCert Inc, OU = www.digicert.com, CN = GeoTrust RSA CA 2018
verify return:1
depth=0 CN = api.prod.quasar.nadops.net
verify return:1
SSL_connect:SSLv3 read server certificate A
SSL_connect:SSLv3 read server key exchange A
SSL_connect:SSLv3 read server done A
SSL_connect:SSLv3 write client key exchange A
SSL_connect:SSLv3 write change cipher spec A
SSL_connect:SSLv3 write finished A
SSL_connect:SSLv3 flush data
SSL_connect:SSLv3 read finished A
---
Certificate chain
0 s:/CN=api.prod.quasar.nadops.net
i:/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=GeoTrust RSA CA 2018
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIEuTCCA6GgAwIBAgIQDFoE4dcFI+tHxeZBx3DCKDANBgkqhkiG9w0BAQsFADBe
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3
d3cuZGlnaWNlcnQuY29tMR0wGwYDVQQDExRHZW9UcnVzdCBSU0EgQ0EgMjAxODAe
Fw0xODAxMTcwMDAwMDBaFw0yMTAxMTYxMjAwMDBaMCUxIzAhBgNVBAMTGmFwaS5w
cm9kLnF1YXNhci5uYWRvcHMubmV0MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIB
CgKCAQEAvzSCRUpL3S3XF82i7A/qZPOgwAbGagJCZUss5ZAU2Xb0bNi2FFGT5rhq
6sC59tAVF15BoY24KfMvxbp86B0n7OCvvVCJ4IY21/i2MNrCOM2t09UOMFdSuKhD
IdKLcYNrw8xhvMqGPeyFgHYZhPFfvB2XrwO9K8hjwgq01AHB490W6PylU+S0cedW
J1pqC57gXne3sQd20+DRvLZ9dP/phMofeMoO5zrcUZIMZ1cjP32LEVjwQ93CEgbG
BZ+cdHNJcyDtoP5t4Z2of8+f5mp2WK583vQVpKdZYw/AT1ZdEQrcePDVt+wAROHJ
x2SRtk2ouIcBUCcwMH4goIFparN4lQIDAQABo4IBqjCCAaYwHwYDVR0jBBgwFoAU
kFj/sJx1qFFUd7Ht8qNDFjiebMUwHQYDVR0OBBYEFJwKxfT5OhncAC2KcYLtJVnu
0ajNMCUGA1UdEQQeMByCGmFwaS5wcm9kLnF1YXNhci5uYWRvcHMubmV0MA4GA1Ud
DwEB/wQEAwIFoDAdBgNVHSUEFjAUBggrBgEFBQcDAQYIKwYBBQUHAwIwPgYDVR0f
BDcwNTAzoDGgL4YtaHR0cDovL2NkcC5nZW90cnVzdC5jb20vR2VvVHJ1c3RSU0FD
QTIwMTguY3JsMEwGA1UdIARFMEMwNwYJYIZIAYb9bAECMCowKAYIKwYBBQUHAgEW
HGh0dHBzOi8vd3d3LmRpZ2ljZXJ0LmNvbS9DUFMwCAYGZ4EMAQIBMHUGCCsGAQUF
BwEBBGkwZzAmBggrBgEFBQcwAYYaaHR0cDovL3N0YXR1cy5nZW90cnVzdC5jb20w
PQYIKwYBBQUHMAKGMWh0dHA6Ly9jYWNlcnRzLmdlb3RydXN0LmNvbS9HZW9UcnVz
dFJTQUNBMjAxOC5jcnQwCQYDVR0TBAIwADANBgkqhkiG9w0BAQsFAAOCAQEAqv5b
CxKeHDV8+j86I9zvXoPwMq+a+SpjQHeTgNjU3zsnQWxyDVH6gqnA9xmt/c36AcTd
mqeLZMsdDNMAimeEk7hCCaQcuOp0mLlxqlxNnPas6kTzvpM3KpBKUeFbCW77jkFZ
Q1WU/oq9jZZCtvl5SZadwmSzXAp9PxDuRIJEYtpHs/Mo6RQWZJcOmIN1aGfUOuav
+xOLhUK/FQ+e4riUcuLiTcxLBHtqkMORHP4t3GWCNv1Sj/i8qwI/SpU3DBvryp3Z
RXKA4okXFPQ8fKfq5HHyhpmGRhWlCUIHSnyjo9QzKvH0A18f+DDtQ/1KWU9lQHcO
BaZOw378zMc0PHHceg==
-----END CERTIFICATE-----
subject=/CN=api.prod.quasar.nadops.net
issuer=/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=GeoTrust RSA CA 2018
---
No client certificate CA names sent
Peer signing digest: SHA1
Server Temp Key: ECDH, P-256, 256 bits
---
SSL handshake has read 1718 bytes and written 415 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-GCM-SHA384
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol : TLSv1.2
Cipher : ECDHE-RSA-AES256-GCM-SHA384
Session-ID: 4F057AE288401B87506EF68BA0F2AFC3B3182E20100F5E0778493198BD2C6984
Session-ID-ctx:
Master-Key: 04CE701B1414899E854E136F858D7907BDA2ED52BDDB41EBA65F48A7102847B222AC0CAA1B3E733D6945139A752E0331
Key-Arg : None
Krb5 Principal: None
PSK identity: None
PSK identity hint: None
Start Time: 1542064692
Timeout : 300 (sec)
Verify return code: 0 (ok)
---
I can successfully do this directly against Knox using the [Knox] section configuration of the odbc.ini:
[mpetronic@vmwhnsqsrclnt01 ~]$ echo "show tables" | isql -d, -b -v knox mpetronic $(cat ~/.pw.dat)
cro_capacity_extract_tmp
cro_capacity_ranked_tmp
cro_capacity_report_final
cro_efficiency_extract_tmp
cro_efficiency_hourly_tmp
cro_efficiency_report_final
j1_total_user_counts
my_vsat_info_ht1100
san_data_2
test
But this fails using the [F5] section:
[mpetronic@vmwhnsqsrclnt01 ~]$ echo "show tables" | isql -d, -b -v f5 mpetronic $(cat ~/.pw.dat)
[S1000][unixODBC][Hortonworks][DriverSupport] (1100) SSL certificate verification failed because the certificate is missing or incorrect.
[ISQL]ERROR: Could not SQLConnect
I have also added the Geotrust CA into the keystore being used but no difference. Here you can see the Knox certificate (alias=mykey) and the Geotrust CA (alias=geotrust):
(test) [mpetronic@vmwhnsqsrclnt01 keystore]$ keytool -list -keystore keystore.jks
Enter keystore password:
Keystore type: jks
Keystore provider: SUN
Your keystore contains 2 entries
geotrust, Nov 12, 2018, trustedCertEntry,
Certificate fingerprint (SHA1): 7C:CC:2A:87:E3:94:9F:20:57:2B:18:48:29:80:50:5F:A9:0C:AC:3B
mykey, Oct 9, 2017, trustedCertEntry,
Certificate fingerprint (SHA1): F1:75:91:59:8D:BE:B1:79:AC:02:7F:66:FE:0A:53:E2:89:98:75:BA
At this point, I am stumpted. I am unclear as to what cannot find the certificate or what certificate it is looking for. I know that some applications do not look for CA certificates in the OS system CA bundles so maybe isql is not looking there? I also get the same error with a test Python app using pyodbc so I doubt that is the issue.
Any help would be greatly appreciated!
