DBeaver is a universal tool to access any database or cloud application that has an ODBC or JDBC Driver such as MySQL, Oracle, Salesforce, Hive, Impala, Teradata, Redshift, Snowflake, etc. SQL Databases like Impala & Hive are secured with Kerberos & SSL. This post discusses how you can access an Impala or Hive database that is secured with Kerberos & SQL security from DBeaver.

Q. What SQL tool do you use to access an SQL database & analyse data as a Data or software Engineer?
A. DBeaver, DBVisualizer, Hue, Oracle SQL Developer, MySQL Workbench, Teradata SQL Assistant, etc.

Step 1: Download the JDBC Driver for Hive or Impala.

You must have the JDBC driver for the database you are planning to access. The Impala or Hive driver can be downloaded from the Cloudera website.

Step 2: DBeaver.ini

This file on a Windows machine will be under “C:\Users\[user_id]\AppData\Local\DBeaver”

DBeaver.ini

....
...
-Dosgi.requiredJavaVersion=11
-Djavax.security.auth.useSubjectCredsOnly=false
-Dsun.security.krb5.debug=true
-Djava.security.krb5.conf=C:/Users/[userid]/DBeaver/krb5.ini
-Djava.security.auth.login.config=C:/Users/[userid]/DBeaver/jaas.conf
....
....

Step 3: krb5.ini

Create a folder “C:/Users/[userid]/DBeaver” and copy the krb5.conf file from one of the nodes as a krb5.ini file. This file contains connectivity details to a Kerberos environment.

krb5.ini

[libdefaults]
default_realm = MYREALM.COM.AU
dns_lookup_kdc = false
dns_lookup_realm = false
ticket_lifetime = 86400
renew_lifetime = 604800
forwardable = true
default_tgs_enctypes = rc4-hmac
default_tkt_enctypes = rc4-hmac
permitted_enctypes = rc4-hmac
udp_preference_limit = 1
kdc_timeout = 3000

[realms]
MYREALM.COM.AU = {
kdc = kdcserver.myrealm.com.au
admin_server = adminserver.myrealm.com.au
default_domain = myrealm.com.au
}

TECH.COM.AU = {
kdc = kdcserver2.tech.com.au
admin_server = adminserver2.tech.com.au
default_domain = tech.com.au
}

[domain_realm]
myrealm.com.au = MYREALM.COM.AU
tech.com.au = TECH.COM.AU
tech.com.au = TECH.COM.AU

Step 4: jaas.conf

com.sun.security.jgss.initiate {
com.sun.security.auth.module.Krb5LoginModule required
debug=true
doNotPrompt=true
useKeyTab=true
keyTab="C:/Users/[userid]/DBeaver/batchuser.keytab"
useTicketCache=false
principal="userid@TECH.COM.AU";
};

By default, any user’s ticket-granting-ticket (TGT) used on the client side is read from the default Kerberos credential cache.

keyTab=”C:/Users/[userid]/krb5cc_[userid]”

The above file will be update by running the kinit command.

c:/somefolder/kinit userid@TECH.COM.AU

Step 5: import the target server certificate into a trust store

Java keytool to the rescue. Get the certificate & import it into a key store.

keytool -import -alias myserver_alias -file mycertificate.pem  -storetype JKS -keystore my-server.truststore 

Step 6: connect to an SQL database that is secured by Kerberos & SSL

Open the DBeaver application and create a Database connection.

MAIN Tab:

JDBC URL = jdbc:impala://mybigdata.server.com.au:21051/default
Host = mybigdata.server.com.au
Port = 21051

Driver properties Tab:

Add the Driver JAR from Step 1 and also set the driver properties as shown below.

AuthMech = 1
KrbHostFQDN = bigdata.server.com.au
KrbRealm = MYREALM.COM.AU
KrbServiceName = impala
SSL = 1
SSLTrustStore = C:/path/to/my-server.truststore
SSLTrustStorePwd = somepassword

Debugging The logs can be accessed from

C:\Users\[userid]\AppData\Roaming\DBeaverData\workspace6\.metadata\dbeaver-debug.log

Learn more about Kerberos security at: 12 Kerberos interview Q&As.