How to connect to a Kerberised CDH Hadoop Cluster from Oracle SQL Developer

Oracle SQL Developer 4.0.3 and above allow you to connect to database in Hadoop via the Hive JDBC drivers. This pots describes how to configure SQL Developer to use the Cloudera provided Hive JDBC drivers and connect to Kerberised clusters. 

Step 1: Download SQL Developer

Download the latest version of SQL Developer with the JDK from (4.0.3 or higher required)  http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

Step 2: Download the Hive JDBC drivers

 Download the Hive JDBC drivers from http://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-4.html. At the time of writing SQL Developer requires that the JDBC4 drivers are used. Unzip these files to a directory on your laptop.

Step 3: Allow applications to request Kerberos session keys

On Windows 7/2008 R2 and higher by default applications are not permitted access to the Kerberos TGT Session Key to submit its own Kerberos Ticket requests is not permitted. (refer to https://support.microsoft.com/en-us/kb/2627903). To enable this the allowtgtsessionkey registry key (DWORD-32) must be created under the path:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters

Step 4: Configure SQL Developer for Kerberos support

Add the krb5.conf with the same content as used on the your Hadoop cluster nodes under /etc/krb5.conf for the cluster that you are connecting to, and place it under jdk/jre/lib/security in the SQL Developer install directory.

You also need the JCE jars from http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.html and place these under jdk/jre/lib/security.

Step 5: Start SQL Developer and add the JDBC drivers

Start SQL Developer and go to Tools > Preferences, then Database > Third Party JDBC Drivers and add each of the Hive JDBC jar files individually.

Step 6: Configure the JDBC connection to Hive

Create a new connection:

Connection Name: enter a descriptive name for the connection

Username: Enter the username you will authenticate with 

Password: Enter the password the username you entered

Select the Hive tab

Host name: enter the server running Hive

Port: For Hive the default port is 10000

Database: Enter a connection string in this format: database;AuthMech=1;KrbRealm=<AD domain/ Kerberos Realm>;KrbHostFQDN=<fqdn of host running hive server>;krbServiceName=hive

Connecting with a Service Account

If you need to connect with a different service account (e.g. an account other than the one you are logged into the AD domain with) you will need to download and install the MIT Kerberos software from http://web.mit.edu/kerberos/dist/kfw/4.0/kfw-4.0.1-amd64.msi

Then open the Kerberos Ticket Manager and authenticate as the service account in question. In the text box labelled principal enter the service account as <service account>@<AD Domain/ Kerberos Realm>.

You should now be able to use these credentials with SQL Developer instead.