hive connection using SSL ( without Kerberos )

[#1]

Hi Do we need to run below both commands for hive -- ssl configuration . We are using LDAP / AD

1) as per the link : https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.4.0/bk_Security_Guide/content/ch_wire-hiveserve...

keytool -genkey -alias hbase -keyalg RSA -keysize 1024 -keystore hbase.jks

and

2) as per the link : https://docs.hortonworks.com/HDPDocuments/HDP2/HDP2.3.2/bk_dataintegration/content/enabling_hs2_for_...

keytool -import -trustcacerts -alias <MyHiveLdaps> -storepass <password> -noprompt -file <myCert>.pem -keystore ${JAVA_HOME}/jre/lib/security/cacerts

I just did just 2nd one and blow are my 3 setting in custom-hive-site.xml

hive.server2.use.SSL=true

hive.server2.keystore.path=/usr/lib/jvm/java-1.8.0-openjdk/jre/lib/security/cacerts

hive.server2.keystore.password=xxxxxxxx

Thanks

Naveen

[#2]

Hi @naveen sangam -- SSL config is tricky at first. Here are some pointers that will hopefully get you on the path:

• The keytool -genkey command above is creating a self-signed certificate. Another option is to follow your company's process to create a company signed certificate, usually thru your IT department, but that can take time. Self signed are good for non-prod environment, but I recommend company signed SSL cert for prod environments.
• Once you have a valid certificate, it must be installed on the server. That's the Hive configuration steps you listed above, but I think you have a mistake. Hive should path to a keystore file (not a truststore) like "hiveserver2.jks". You can use the "keytool -import" command to create the keystore file if needed.
• Think of it this way: a server secures communication using a certificate that's saved in a keystore. The client trusts that certificate using what is saved in the client's truststore. Keystore = SSL server, truststore = SSL client. In our case this will be Hiveserver 2 (server / keystore) and beeline (client / truststore).
• Please note: the files are all considered "jks" or "java keystore" files, but they have different uses and different entries contained within. So there are keystore files that are used as keystores, and there are keystore files used as truststores. Whoever named all this should be taken out back and shot.
• To check if your keystore is properly setup, run the command "keytool -list -v -keystore <keystore-filename.jks>" and enter password. Look for an entry for your certificate, and the entry type should read "PrivateKeyEntry" with valid expiration date.
• Now the Hive client (like beeline) must trust that server certificate. You will need to import the self-signed certificate, or your company's CA certificate (depending on your certificate type) into a truststore, like "cacerts".
• To check if your truststore is properly setup, run the command "keytool -list -v -keystore <truststore-filename.jks>" and enter password. Look for an entry for your certificate, and the entry type should read "TrustedCertEntry" with valid expiration date.
• Java has a default truststore file that it uses, called "cacerts", and this is located within your java installation directory. You can generally sniff it out by running "locate cacerts". If you import into this location, beeline should find the CA trusted cert entry with no problem. I believe the default location is /etc/pki/java/cacerts, and default password is "changeit".

• However, you can also create another truststore file and save it anywhere your beeline user can read the file. Since this isn't the truststore that's built into java, you have to tell beeline where this file lives by adding the path and password to the truststore to your connection string:

• jdbc:hive2://<host>:<port>/<database>;ssl=true;sslTrustStore=<path-to-truststore>;trustStorePassword=<password>
  

• Keytool is your best friend through all of this, so get familiar with it. Here is a link to some of the most common keytool commands: https://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html

Hope this helps

-- Eddie

[#3]

Thank Eddie,

It helped me a lot. I have a followup question.

After we create the java keystore file ( .jsk) using keytool -genkey ...

do we need to export it to the certificate .cer file ,

so that

it can be added to the truststore (/usr/lib/jvm/java-1.8.0-openjdk/jre/lib/security/cacerts) as the trusted certificate ?

**********

I see below error from beeline

Error: Could not open client transport with JDBC Uri: jdbc:hive2://hadoop-node1.sandbox.com:10000/testdb;ssl=true;sslTrustStore=/usr/lib/jvm/java-1.8.0-openjdk/jre/lib/security/hivekeystore.jsk;trustStorePassword=changeit: javax.net.ssl.SSLException: Unrecognized SSL message, plaintext connection? (state=08S01,code=0)

in hiveverver2 log file i see below error.

Caused by: org.apache.thrift.transport.TTransportException: Error validating the login at org.apache.thrift.transport.TSaslTransport.sendAndThrowMessage(TSaslTransport.java:232) at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:316) at org.apache.thrift.transport.TSaslServerTransport.open(TSaslServerTransport.java:41) at org.apache.thrift.transport.TSaslServerTransport$Factory.getTransport(TSaslServerTransport.java:216) ... 4 more 2017-03-08 18:06:28,807 ERROR [HiveServer2-Handler-Pool: Thread-71]: transport.TSaslTransport (TSaslTransport.java:open(315)) - SASL negotiation failure javax.security.sasl.SaslException: Error validating the login [Caused by javax.security.sasl.AuthenticationException: LDAP Authentication failed for user [Caused by javax.naming.AuthenticationException: [LDAP: error code 49 - 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db1^@]]] at org.apache.hive.service.auth.PlainSaslServer.evaluateResponse(PlainSaslServer.java:109) at org.apache.thrift.transport.TSaslTransport$SaslParticipant.evaluateChallengeOrResponse(TSaslTransport.java:539) at org.apache.thrift.transport.TSaslTransport.open(TSaslTransport.java:283) at org.apache.thrift.transport.TSaslServerTransport.open(TSaslServerTransport.java:41) at org.apache.thrift.transport.TSaslServerTransport$Factory.getTransport(TSaslServerTransport.java:216) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:269) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: javax.security.sasl.AuthenticationException: LDAP Authentication failed for user [Caused by javax.naming.AuthenticationException: [LDAP: error code 49 - 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db1^@]] at org.apache.hive.service.auth.LdapAuthenticationProviderImpl.Authenticate(LdapAuthenticationProviderImpl.java:185) at org.apache.hive.service.auth.PlainSaslHelper$PlainServerCallbackHandler.handle(PlainSaslHelper.java:106) at org.apache.hive.service.auth.PlainSaslServer.evaluateResponse(PlainSaslServer.java:102)

Thanks

Naveen

[#4]

Hi @naveen sangam -- yes, since you are using a self-signed certificate you generated with keytool -genkey, you will need to export the certificate into a .cer file, and then import it into your truststore file on the client. All of this can be accomplished with the keytool command. Be sure when you import the certificate into your truststore that it shows up as a "TrustedCertEntry".

[#5]

Hi Eddie,

I am not using self-signed Certificate. But using CA Certificate.

As per the link : https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.3/bk_security/content/ch_obtain-trusted-cert.... : 4.1. Obtain a Certificate from a Trusted Third-Party Certification Authority (CA)

can you please clarify below situation

If we have 6 service accounts ( like hive ,base , hdfs , oozie ...) and say it is 10 node cluster,

1) Do we need to generate 6 X 10 , java keystore file ( .jsk)

2) And for each of this .jsk file , we need create thier respective CSR file . In this example there will be 60 .csr fils

3) Submit these 60 .csr to CA team to get individual (60) , certificate with extension .pem.

4) And all these 60 .pem need to import to truststore , on 10 individual nodes?

Thanks

Naveen.