Configure JDBC clients to Work with Your Kerberos-Enabled Vertica Cluster: DbVisualizer, DBeaver, and Others

Posted August 1, 2017 by Sarah Lemaire, Manager, Vertica Documentation

Many customers use JDBC-based tools like DbVisualizer and DBeaver to connect to Vertica for SQL development purposes. It is easy to use these tools on a non-Kerberos enabled Vertica cluster, but connecting to a Kerberos-enabled Vertica cluster is not straightforward because there is no native support for Kerberos on tools like DbVisualizer and DBeaver.

This blog post provides step-by-step guidance for setting up JDBC clients like DbVisualizer and DBeaver to work with your Kerberos-enabled Vertica cluster. The examples used in this post are based on DbVisualizer and DBeaver, but you can use these steps for any JDBC-based client tool.

The information in this blog post applies to both the Vertica Enterprise and SQL on Hadoop editions.

JDBC clients use the Java Authentication and Authorization Service (JAAS) for Kerberos authentication. JAAS is a pluggable API that permits Java applications to remain independent from underlying authentication methodologies. The authentication method and its associated implementation details are specified in a JAAS configuration file. The JDBC client uses this file at startup to determine which authentication protocol to use.

Before you configure the JDBC clients, make sure the following is true:For example, the domain realm is assumed to be in the VERTICA Kerberos realm. If that is not the case, for example, if that host is in the COMPANYCORP Kerberos realm, you need a domain_realm mapping like the following:

• DbVisualizer/DBeaver must be configured correctly with the Vertica JDBC driver that matches the server version. The built-in JDBC driver that comes with the JDBC client might not be compatible with the Kerberos options for the Vertica server. Copy the JDBC driver from /opt/vertica/java directory of any Vertica server node.
• You have a krb5.conf file for the client that is consistent with the vertica server and the Key Distribution Center (KDC) you’re using. Oftentimes, the krb5.conf from the Vertica server can be reused by the client. However, the client may require different settings if the network environment differs. For example, in a Windows environment, if you are using JVM 1.9++, you will need to comment out “includedir /etc/krb5.conf.d/” in the krb5.ini file. This is because the newer JVM 1.9++ can’t interpret the Linux style path. In particular, the following settings should be consistent between client and server:

o Encryption algorithms
o An appropriate mapping in the [domain_realm] section, if the Vertica server’s realm cannot be discovered based on the server’s FQDN name using the client’s DNS settings.

For example, the domain realm is assumed to be in the VERTICA Kerberos realm. If that is not the case, for example, if that host is in the COMPANYCORP Kerberos realm, you need a domain_realm mapping like the following: [domain_realm] = COMPANYCORP = COMPANYCORP Without this mapping, the Kerberos client does not know which KDC to request a service ticket for, given a Vertica service principal, which is typically specified by the -K argument in vsql.

Consult the MIT Kerberos documentation for the appropriate settings.

• You have a valid Kerberos user principal that matches a user in Vertica database.
• You have a valid JAAS configuration file. In the example in the blog post, we call this vertica.jaas.
• The Vertica server you want to connect to must already be configured for Kerberos authentication.

The following is a sample JAAS configuration file. This file tells DbVisualizer to use Krb5LoginModule with the user principal vertuser. The default JAAS configuration name for Vertica is verticajdbc.

The available configuration settings depend on the LoginModule you specify in the configuration. The following example uses Krb5LoginModule. For additional information about the Krb5LoginModule settings, see Class Krb5LoginModule.

Note: You specify the password in the connection string. If the JAAS framework needs to ask for a password (because you have no cached ticket), it asks the JDBC driver for the password, which the driver gets from the URL.
verticajdbc { required useTicketCache=false renewTicket=true useKeyTab=false principal="vertuser\@COMPANYCORP.COM" doNotPrompt=false debug=true; }; A valid user can authenticate using Kerberos by typing in their password or by using an appropriate keytab file. The preceding example is for using a password.

The following is another variation of the same JAAS configuration file where the user uses a keytab for authentication.
verticajdbc { required useTicketCache=false renewTicket=true useKeyTab=true keyTab="C:\\Users\\vertuser\\Kerberos_stuff\\vertuser.keytab" principal="vertuser\@COMPANYCORP.COM" doNotPrompt=false debug=true; }; Save vertica.jaas, krb5.conf, and the keytab file (if applicable) in a directory on your Windows machine. After the files are copied, the following Java Opts should be added to the dbvis.vmoptions file, which can be found in the DbVisualizer installation directory (usually Program Files/Dbvisualizer).
-Dsun.locale.formatasdefault=true\Users\vertuser\Kerberos_stuff\jaas.config\Users\vertuser\Kerberos_stuff\krb5.conf For the new settings to take effect, you need to restart your JDBC client. After the client is up and running, your users should be able to connect to the Kerberized Vertica cluster. If you are using non-default values for the JAASConfigName property, verify that you use the correct value in your JDBC connection string. The preceding examples used the default value verticajdbc for the JAASConfigName property.

To make this work for DBeaver, modify the dbeaver.ini file (found in installation directory) with the same in dbvis.vmoptions file changes described earlier in this blog post.

The versions used for these examples are:
• DbVisualizer Pro 9.5.7
• DBeaver 22.0.2

For more information, see the Vertica Kerberos documentation:
Configure Vertica for Kerberos Authentication
Configure Vertica clients for Kerberos Authentication