KNOWLEDGE BASE

Connecting to Oracle Autonomous Data Warehouse Using JDBC


Published: 11 Jan 2021
Last Modified Date: 18 Jan 2021

Question

How to connect to Oracle Autonomous Data Warehouse (ADWC) using the JDBC driver

Environment

  • Tableau Desktop 2020.2 and newer
  • Oracle Autonomous Data Warehouse (ADWC)

Answer

Prerequisites
  1. The Oracle JDBC driver (ojdbc8.jar) can be downloaded from https://www.oracle.com/database/technologies/appdev/jdbc-ucp-19c-downloads.html (An Oracle account is required)
  2. Copy the .jar file to C:\Program Files\Tableau\Drivers
  3. Obtain the Credentials Zip File (instance wallet) and the encryption password for the wallet from your Oracle Autonomous Data Warehouse database administrator. See this document.
  4. Extract the credentials zip file.

Creating the connection

Option 1 - Use a .properties file to configure the client certificate and private key
  1. Create a oracle.properties file, and save it to Documents\MyTableau Respository\Datasources. See this document for details. Note: The value for both javax.net.ssl.trustStorePassword and javax.net.ssl.keyStorePassword is the encryption password for the wallet. 
  2. For connection details to use with the Oracle connector in Tableau Desktop, open tnsnames.ora (in the folder created from extracting the credentials zip file) in a text editor. 
  3. Select the database service name (high, medium or low) depending on the desired consumer group (see Database Service Names for Autonomous Data Warehouse), and use the host value for Server, service_name value for Service and the port number, and select 'Require SSL' when signing in.

To enable a .properties file for Tableau Server, it must be placed on all Server nodes in the following directory: 
  • tabsvc/vizqlserver/Datasources for Windows
  • /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/ for Linux

Option 2 - Embed client certificates and private key in the data source
  1. Export the certificates and private key to plain-text PEM format (Base64 ASCII encoding) using a utility such as KeyStore ExplorerPortecle or OpenSSL. The SSL CA file should contain all the certificates extracted from truststore.jks, while the Client Cert and Client Key files are derived from the keystore.jks file.  
  2. Embed the files from Step 1, when creating the connection. See Steps 2 and 3 from Option 1 above and this document.

Additional Information

Beginning with version 2020.2, Tableau recommends the use of the JDBC driver with the Oracle connector. See JDBC driver versus OCI driver for more information.
Did this article resolve the issue?