db connect and hive 2

[jbspecht]

We are trying to get a Cloudera Hive 2 connection in DB Connect to work but have so far been unsuccessful. We have tried the following versions...

Splunk v7.3.0 with DB Connect v3.3.1, Java 8

Splunk v8.2.0 with DB Connect v3.5.1, Java 8

The newest version of Splunk and DB Connect gives a somewhat helpful error upon saving the connection...

"[Cloudera][HiveJDBCDriver](500164) Error initialized or created transport for authentication: [Cloudera][HiveJDBCDriver](500169) Unable to connect to server: Failure to initialize security context." 

This however seems to be maybe a somewhat generic error as we have tried various means of authentication and we still receive this error. 

We are using the Cloudera Hive JDBC v2.6.5 driver. It has all the dependencies bundled into one jar as I understand it. We placed this into the DB Connect driver directory. We then created the "db_connection_types.conf" file in the local directory and added a stanza for the new driver. 

[cloudera_hive_2]
displayName = Cloudera Hive 2
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = com.cloudera.hive.jdbc41.HS2Driver
jdbcUrlFormat = jdbc:hive2://<host>:<port>/<database>
port = 20500
ui_default_catalog = $database$

DB Connect recognizes the driver however when we attempt to save the connection we receive a failure. If we try to move past saving the connection and try to retrieve data we receive a connection error. 

Any help would be very much appreciated as we have tried everything we can think of. 

[jbspecht · Accepted solution]

After much trial and error and time spent we've been able to use the following to get it to work. Hopefully this will help someone else.

Things needed for Splunk DB Connect on Windows to work with a Hive 2 Kerberos connection:

• Kerberos MIT - https://web.mit.edu/kerberos/dist/ (kfw-4.0.1-amd64)
• Cloudera JDBC driver - https://www.cloudera.com/downloads/connectors/hive/jdbc/2-6-5.html (HiveJDBC41.jar)
• OpenJDK 8 - https://jdk.java.net/java-se-ri/8-MR3

Other things to mention:

Oracle's Java 8 did not work for us. 

Krb5.conf configuration files placed in %JAVA_HOME%\jre\lib\security directory didn't seem to help DB Connect when verifying the connection.

We tested successfully on Windows servers with Splunk v8.2/DB Connect v3.5.1 and Splunk v7.3/DB Connect 3.3.1.

db_connection_types.conf file needs to exist or be created in the DB Connect local directory with a config that looks like this for the Cloudera driver...

[cloudera_hive_2]
displayName = Cloudera Hive 2
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = com.cloudera.hive.jdbc41.HS2Driver
jdbcUrlFormat = jdbc:hive2://<host>:<port>/<database>
port = 20500
ui_default_catalog = $database$

The more up-to-date versions of DB Connect are more verbose with any errors generated.

If I were to start the process again I would install the newest version of DB Connect compatible with existing version of Splunk. Download OpenJDK v8, extract and copy it into a directory (C:\Program Files\Java\java-se-8u41-ri). Then create a JAVA_HOME system environment variable and place that directory inside as the value. Reboot the server. You may then need to manually update the "JRE Installation Path" field in DB Connects Configuration -> Settings -> General tab and Save. Then reboot Splunk web via Settings -> Server Controls. Once there are no more errors popping up, download the Cloudera driver and move it into the DB Connect "drivers" directory (\splunk_app_db_connect\drivers). Go to the Configuration -> Settings -> Drivers tab and click reload. The driver should now exist on the page and have a green check mark next to it along with the version. Install Kerberos MIT. Create a connection in DB Connect and setup a connection string in the "JDBC URL" field with something like...

jdbc:hive2://dbserverhostname:10000/db_table_name;AuthMech=1;KrbRealm=domain_name_here;KrbHostFQDN=dbserverhostname;KrbServiceName=hive;KrbAuthType=2

Click "Save" and see if the connection is successful. If it is, there should be no errors that pop up. 

[jbspecht]

To add to this...when we switched from Oracle's Java to OpenJDK we lost (stopped working) all of our MS-SQL connections. Turns out the fix for this was to re-download the MS-SQL JDBC driver and put the 32 bit version of the sqljdbc_auth.dll in the C:\Windows\SysWOW64 directory and remove the x64 bit version in C:\Windows\System32 directory that was working fine before. After rebooting the server the MS-SQL connections started working again.