Tableau

This article explains how to connect and use Tableau with your Exasol database.

Exasol is easily integrated with Tableau and provides Tableau customers the power to visualize and analyze very large live data. You can connect Tableau to your Exasol database using either JDBC (recommended) or ODBC.

To learn how to create content on Tableau, refer to the training videos on the Tableau website.

Exasol takes no responsibility for changes in functionality or terms of use for third-party tools. For more details about the compatibility and use of these tools with Exasol, refer to the respective software vendor’s website.

Prerequisites

  • Your Exasol SaaS database must be running. For more information, see Database Management.

  • The IP address of the host where the tool is running must be in the allowed IP address list. For more information, see Network Security.

  • Tableau must be installed

Install drivers

Install the JDBC driver

The connector for JDBC requires the Exasol JDBC driver to be installed for Tableau Desktop and Server. You can download the latest Exasol JDBC driver for your operating system from the Exasol download portal.

Copy the JDBC driver exajdbc.jar to the Tableau installation directory:

  • Tableau Desktop (see Tableau Desktop documentation for details):

    • C:\Program Files\Tableau\Drivers (Windows)
    • ~/Library/Tableau/Drivers (macOS)

  • Tableau Server:

    • Windows: C:\Program Files\Tableau\Drivers
    • Linux: /opt/tableau/tableau_driver/jdbc

When using Kerberos under Windows, you must download and run the Windows driver installer EXASOL_JDBC-<version>.msi, which will install the JDBC driver in C:\Program Files\Exasol\EXASolution-<version>\JDBC\exajdbc.jar.

Only the JDBC driver for Windows supports Kerberos under Windows.

Install the ODBC driver

The connector for ODBC requires the Exasol ODBC driver to be installed for Tableau Desktop and Server.

Download and install the latest Exasol ODBC driver for your operating system from the Exasol download portal.

  • Windows and macOS: install the driver by executing the installer.

  • Linux:

    1. Unpack the ODBC driver to /opt/exasol/odbc

    2. Create or edit file /etc/odbcinst.ini and add the following entry:

      [EXASolution Driver]

      Driver=/opt/exasol/odbc/lib/linux/x86_64/libexaodbc-uo2214lv2.so

Install and use JDBC/ODBC connectors

The Exasol JDBC Tableau connector can be downloaded from the Tableau Exchange Extension Gallery.

The Exasol ODBC Tableau connector is distributed together with the Tableau Desktop and Tableau Server applications. We recommend using the latest available version of Tableau to access the Exasol ODBC connector.

You can also download the latest connectors for JDBC and ODBC as .taco files from the GitHub release page.

Install the connectors with Tableau Desktop

Download the latest JDBC or ODBC connector from the GitHub release page and copy the .taco file to:

C:\Users\[Windows User]\Documents\My Tableau Repository\Connectors (Windows)

/Users/[user]/Documents/My Tableau Repository/Connectors (macOS)

Starting with version 0.4.2 the connectors are signed, so you can omit command line argument -DDisableVerifyConnectorPluginSignature.

See details in the documentation for Tableau Desktop and the Tableau Connector SDK.

Install the connectors with Tableau Server

  1. Download the latest JDBC or ODBC connector from the GitHub release page and copy the .taco file to:

    /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Connectors/ (Linux)

    C:\Program Files\Tableau\Connectors (Windows)

  2. Restart the server.

Starting with version 0.4.2 the connectors are signed, so you can set option native_api.disable_verify_connector_plugin_signature to false if you have changed it to true before. This will switch on signature verification:

tsm configuration set -k native_api.disable_verify_connector_plugin_signature -v false --force-keys
tsm pending-changes apply

After installing the connectors in Tableau, select the connector EXASOL JDBC by Exasol AG or EXASOL ODBC by Exasol AG. This will open a connection dialog where you can enter details for connecting to your Exasol database.

JDBC connector - TLS certificate validation

The JDBC connector will always create a TLS encrypted connection to the Exasol database. If the Validate Server Certificate checkbox is checked, the connector will try to verify the TLS certificate of the Exasol server. This option is enabled by default and recommended.

If your Exasol server does not have a valid TLS certificate, enter the fingerprint of the server certificate into the text field Server Certificate Fingerprint. This ensures that you connect to the correct server.

For more information about TLS authentication, see Upload TLS Certificate (AWS) and Upload TLS Certificate (on-prem).

JDBC connection dialog

JDBC connector - authenticate with username and password

This authentication method is used for database users created with a password, for example:

CREATE USER my_user IDENTIFIED BY "my_secret_password";

To use this authentication method, select Authentication>Username and Password and enter the username and password. This authentication method works on both Tableau Desktop and Tableau Server.

JDBC connector - authenticate with Kerberos/Active Directory

This authentication method is used for database users created with Kerberos authentication, for example:

CREATE USER my_user IDENTIFIED BY KERBEROS PRINCIPAL "my_user@example.com>";

Make sure to enter the Kerberos domain in UPPERCASE.

To use Kerberos SSO, select Authentication>Kerberos. You do not have to enter a username and password, the connector will use the Kerberos credentials provided by your operating system to connect to Exasol.

Prerequisites

These prerequisites are necessary for using Kerberos with both Tableau Desktop and Server:

  • The Exasol database must be configured to use Kerberos Single Sign-On. For more details, see Kerberos SSO.

    To create the keytab file, execute this command on the Windows Domain Controller:

    ktpass -out exasol.keytab -mapuser <user> -princ exasol/<fully qualified hostname>@<REALM> -pass <user password> -ptype KRB5_NT_PRINCIPAL -target <REALM> -crypto All

    For example:

    ktpass -out exasol.keytab -mapuser exasoluser -princ exasol/exasoldb.example.com@EXAMPLE.COM -pass **** -ptype KRB5_NT_PRINCIPAL -target EXAMPLE.COM -crypto All 

    For more information, see also ktpass (Microsoft Learn).

  • Kerberos must use the service name exasol for the database.

  • You must use the fully qualified hostname of the Exasol server (for example: exasoldb.example.com) to connect to the database. You cannot use an IP address to connect.

  • The clocks of all connected machines must be in sync. We recommend to synchronize time by configuring an NTP Server.

When using Kerberos under Windows, you must use the Windows installer EXASOL_JDBC-<version>.msi to install the Exasol JDBC driver for Windows. This will install the driver to C:\Program Files\Exasol\EXASolution-<version>\JDBC\exajdbc.jar.

For information about how to dowload the driver installer, see Install drivers.

Using Kerberos with Tableau Desktop

When selecting Kerberos authentication in Tableau Desktop, the connector will use the credentials of the current Windows/macOS user to log in to Exasol.

Using Kerberos With Tableau Server

With Tableau Server, there are two options for using Kerberos authentication:

RunAs account/service account

This will use the Tableau Server’s Kerberos credentials for accessing the Exasol database instead of the user’s account. For instructions, refer to the Tableau documentation for Linux and Windows.

Kerberos delegation

With delegation, the Tableau Server will forward the user’s Kerberos credentials to the Exasol database. For instructions, refer to the Tableau documentation for Linux and Windows.

To use delegation you need to publish a data source through Tableau Desktop:

  1. Log in to your Tableau Server using Tableau Desktop.

  2. Create a new data source in Tableau Desktop using Kerberos authentication.

  3. Select Server > Publish Data Source > (Your data source).

  4. Click on Authentication > Edit and select Viewer Credentials as the authentication method from the dropdown menu.

    Publish data source with viewer credentials authentication

  5. Click on Publish.

  6. In Tableau Server, create a new Workbook using the data source.

Troubleshooting connection errors

Authentication fails

Can’t connect to Exasol JDBC by Exasol AG
Detailed Error Message
Connection exception - authentication failed.
Unable to connect to the Exasol JDBC by Exasol AG server "exasoldb.example.com". Check that the server is running and that you have access privileges to the requested database.

This is a general error message when authentication fails.

Suggested actions:

When using Username and Password authentication, check that username and password are correct.

When using Kerberos authentication check the following:

  • Verify that the clocks on all machines are synchronized. For the Exasol database you can do the following:

    • Check the current time of Exasol by running SQL query select CURRENT_TIMESTAMP.
    • Configure one or more NTP servers as described in the documentation.
    • Manually trigger NTP synchronization via EXAOperation Cluster Monitoring.
  • Verify that Active Directory is setup correctly by running command dcdiag on the Domain Controller.
  • Make sure to use the JDBC driver jar for the correct platform (Windows/Linux/Unix). The driver for Windows contains an additional library required for Kerberos on Windows.

  • Download cluster logs from Exasol and check files *ConnectionServer* for error messages:

    • Request ticket server exasol/exasoldb.example.com@EXAMPLE.COM kvno 12 found in keytab but not with enctype aes128-cts: The keytab does not contain AES encrypted credentials. Generate a new keytab with option -crypto All (recommended) or disable AES for the Exasol service user in Active Directory via options This account supports Kerberos AES 128 bit encryption and This account supports Kerberos AES 256 bit encryption.
    • Request ticket server exasol/exasoldb.example.com@EXAMPLE.COM kvno 10 not found in keytab; keytab is likely out of date: The Exasol service user's password might have been changed. Generate a new keytab file and and upload it via EXAoperations.

Unable to obtain password from user

An unexpected error occurred. If you continue to receive this error please contact your Tableau Server Administrator.
Session ID: ABC123-1:0
Unable to obtain password from user
Unable to connect to the Exasol JDBC by Exasol AG server "exasoldb.example.com". Check that the server is running and that you have access privileges to the requested database.

This error may occur on Tableau Server when the keytab file is not configured correctly.

Suggested action:

Check that the correct keytab file is configured by executing the following commands:

tsm configuration get -k native_api.datasource_runas_keytab_path
tsm configuration get -k native_api.datasource_impersonation_runas_keytab_path

Missing certificate on Tableau Server

Can’t connect to Exasol JDBC by Exasol AG
Detailed Error Message
Unable to connect to the Exasol JDBC by Exasol AG server "exasoldb.example.com". Check that the server is running and that you have access privileges to the requested database.

This error may occur on Tableau Server when certificate validation is enabled but the Exasol database uses a self-signed TLS certificate.

Suggested action:

Enter the certificate fingerprint in the connection dialog.

Missing certificate fingerprint on Tableau Desktop

java.io.IOException: TLS connection to host (exasoldb.example.com) failed: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. If you trust the server, you can include the fingerprint in the connection string: exasoldb.example.com/ABD591342466880A16A4443DEEFF44A78A26E47514BE4D5E1C4CB712345F69CA:8563. 
Unable to connect to the Exasol JDBC by Exasol AG server "exasoldb.example.com". Check that the server is running and that you have access privileges to the requested database.

This error may occur when certificate validation is enabled but the Exasol database uses a self-signed TLS certificate.

Suggested action:

Enter the certificate fingerprint in the connection dialog.

GRANT SESSION privilege missing

Connection exception - insufficient privileges: CREATE SESSION.
Unable to connect to the Exasol JDBC by Exasol AG server "exasoldb.example.com". Check that the server is running and that you have access privileges to the requested database.
Connector Class: exasol_jdbc, Version: 1.0.0
For support, contact Exasol AG.
Suggested action:
GRANT CREATE SESSION TO "<user>"

Outdated JDBC driver

SQLInvalidAuthorizationSpecException: No LoginModules configured for exasol

This error may occur when using an outdated version of the Exasol JDBC driver with Tableau Desktop.

Suggested action:

Use the latest driver version.

Get ODBC driver and connection Details

To connect to your Exasol SaaS database from a tool you need a valid connection string. You must also allow network access to the database from the tool, and make sure that the latest Exasol ODBC driver is installed.

  1. On the Databases page in the web console, click on Connect via tools on the cluster that you want to connect to.

    connect via tools

  2. In the wizard, enter the IP address of the client or server that will connect to the database and click Add IP, then click Next. This will add the IP address to the list of IP addresses that are allowed network access to the cluster. The allow list is found on the Security page in the web console. For more information, see Network Security.

    If the IP address is already present in the allow list, just click Next.

    enter ip address

  3. If the latest ODBC driver for your operating system is not already installed, select the driver type and operating system and click on Download to download the latest driver to your local system. For more information about how to install the driver, see ODBC Driver.

    download driver

  4. Click on Next to view your connection details:

    connection details

  5. Copy the Connection string, Port, and User name values from the Connect details dialog. You will need to enter this information in the tool that should connect to the Exasol SaaS database.

  6. To generate a new Personal Access Token (PAT) to use for authentication in the client, click on the Password row, then click on Copy to copy the PAT to your clipboard. For more information, see Personal Access Token.

    generated pat

    The generated PAT is only shown once and cannot be retrieved after you have closed the wizard.

  7. Click on Done to close the wizard.

Connect Tableau to Exasol

  1. Start Tableau, in the left side menu on the Tableau Desktop, under To a Server select More.

  2. From the list select Exasol.

  3. In the dialog box, enter the connection information you copied in the Get ODBC driver and connection Details procedure: 
    • Server: Enter the Host string you copied from the web console.
    • Port: The default port is 8563.
    • Username: Enter the user name you copied from the web console.
    • Password: Enter the Personal Access Token you copied from the web console.
    • Click Sign In.

  4. On the Data Source screen, select your Schema from the drop-down list.
  5. Under Table, drag and drop the desired table to the right side of the screen. Once you have all the required tables, you can create worksheets and dashboards.

    6. Note: We recommend you to keep a live connection to the database and avoid creating any extracts.

  6. To create a new worksheet and start your analysis, click Sheet 2.