Connecting to Oracle Autonomous Data Warehouse Using JDBC
Published: 11 Jan 2021 Last Modified Date: 09 Dec 2022
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
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)
Copy the .jar file to C:\Program Files\Tableau\Drivers
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.
Extract the credentials zip file.
Creating the connection
Option 1 - Use a .properties file to configure the client certificate and private key
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.
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.
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
Export the certificates and private key to plain-text PEM format (Base64 ASCII encoding) using a utility such as KeyStore Explorer, Portecle 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.
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.
Thank you for providing your feedback on the effectiveness of the article.