Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora

Answer

CLICK TO EXPAND SOLUTION

Tableau Desktop and/or Server On Windows, OCI Client

Step 1: Set the TNS_Admin environment variable.

Select Start > Control Panel > System
Select Advanced System Settings
In the System Properties dialog box, on the Advanced tab, select Environment Variables.
Under System Variables, click New.
In the New System Variable dialog box, enter the following, then click OK:
- Variable name: TNS_ADMIN
- Variable value: the directory containing the TNSNames.ora file.
Click OK in the Environment Variables dialog box and the System Properties dialog box.
Restart your computer to ensure that the new variable is recognized.

Step 2: Use the Oracle net service name to connect to Oracle in Tableau.

Close all open Tableau workbooks and open a new instance of Tableau.
In Tableau Desktop, select Connect to Data > Oracle.
- For Server, enter the oracle 'net_service_name' recorded in the TNSNAmes.ora file.
- Enter username and password as appropriate.

The rest of the connection details from the TNSNames.ora file are communicated through the TNS_ADMIN system variable. Note that you should leave the optional service name and port information empty, otherwise it may interfere with the connection by duplicating information.

CLICK TO EXPAND SOLUTION

Tableau Desktop and Server on Windows, JDBC Driver

Create an oracle.properties file containing the following text (Example):

oracle.net.tns_admin=C:\\Oracle_Client\\network\\admin

Change the path if necessary to reference the location of the tnsnames.ora file within the host file system. The double backslashes \\ are intentional and required even though the \\ paths will not work if copied and pasted directly into windows file explorer. The oracle.properties file will only function properly with double backslashes in this context.

Place the file in the following location:

Desktop: Documents\My Tableau Repository\Datasources
Server: C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources (or equivalent path if alternate install location employed). Be sure to create the folder if it doesn't already exist.

Server only: Once the files and folders have been created, ensure the run as user is provided with appropriate permissions to the file and restart Tableau Server.

Further information regarding JDBC properties files may be found here: https://kb.tableau.com/articles/howto/Customizing-JDBC-Connections

CLICK TO EXPAND SOLUTION

On Mac

Step 1:

Close Tableau Desktop if it is open.
Ensure that you have downloaded and installed the Oracle drivers for Mac from Tableau's Drivers page.
Copy an existing LDAP.ora or TNSNames.ora file containing connection information to /etc:

In Finder, select Go > Go To Folder, and then type /etc.
Copy the .ora file to /etc.

Step 2: Configure the TNS_ADMIN environment variable

Start Terminal and type the following command:

sudo nano /etc/launchd.conf

Note: Nano is a text-based editor that is always available on Mac computers.
Type your password when prompted.
Type the following:

launch setenv TNS_ADMIN /etc
Press Ctrl-X, then Y, then Enter to save changes and exit nano.
Restart your Mac.

Step 3: Verify that the TNS_ADMIN variable was set:

Start Terminal and type the following command:

export
You will see a list of all system variables. Look for:

declare -x TNS_ADMIN="/etc"
Confirm that DNS resolution is working for the host name listed in the .ora file by pinging the host name in Terminal or Network Utility. Use a fully-qualified domain name, such as servername.domain.com, instead of a simple server name.

You should now be able to start Tableau Desktop on your Mac and connect to the Oracle database, providing only the server name from the TNSNames.ora or LDAP.ora file.

CLICK TO EXPAND SOLUTION

On Linux

Summary of Steps:

Copy the tnsnames.ora file to a location the tableau user has access to
Set permissions on the file.
Update the environment TNS_ADMIN variable, if needed, to point to the directory from step (1).

Step 1: Copy the tnsnames.ora file to a directory the unprivileged user (tableau) can access

For all versions:

Copy the tnsnames.ora file to the /etc directory.

Step 2: Set permissions for the tnsnames.ora file

2020.4 and later:

Create an /var/opt/tableau/tableau_server/data/tabsvc/vizqlserver/Datasources/oracle.properties file with below text:

Example: oracle.net.tns_admin=/etc

Change the /etc if necessary to point to the path of the tnsnames.ora file within the file system on your Server.

Further information regarding JDBC properties files may be found here: https://kb.tableau.com/articles/howto/Customizing-JDBC-Connections

CLICK TO EXPAND SOLUTION

All environments

Optional steps

Finding tnsnames.ora files

If the Oracle client is installed on your server, the tnsnames.ora file will be located in the following directory: $ORACLE_HOME/network/admin.
You can verify if this file exists with the following commands:

echo $ORACLE_HOME
will print the file path.
sudo find / -iname tnsnames.ora
will list the locations of any tnsnames.ora files in your file system.

Note: It is not necessary to have the Oracle client installed to use tnsnames.ora with Tableau Server. This step is simply to help you work with any existing installation.

Creating new tnsnames.ora file from scratch

If no tnsnames.ora file is present on this computer, and you do not have one to copy up from a client workstation, you can create one using a text editor. Keep in mind the following restrictions:

The tnsnames.ora file name is case sensitive, and must be in all lower-case letters.
The file should not use tab-spacing.

Below is a potential template for an entry. Elements in brackets must be replaced by values obtained from your database administrator.
Note: Although a TNSNames.ora file on a Windows or Mac computer may not require the ADDRESS_LIST entry, the tnsnames.ora file on a Linux computer requires this variable.

[net_service_name]=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=[protocol_address_information])
)
(CONNECT_DATA=
(SERVICE_NAME=[service_name])
)
)

For example:

Production =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod.corp.com)(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)