KNOWLEDGE BASE

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


Published: 29 Nov 2016
Last Modified Date: 14 Nov 2019

Question

How to set an Oracle connection to use TNSNames.ora or LDAP.ora.


Benefits to setting up TNSNames.ora or LDAP.ora connections.

The following common Oracle errors can be corrected or avoided by setting up your data connection to use TNSNames.ora or LDAP.ora.
  • ORA-12154: TNS: could not resolve the connect identifier specified

  • ORA-12514: TNS listener does not currently know of service requested in connect descriptor

  • ORA-12541: TNS: no listener

  • ORA-12170: TNS:Connect timeout occurred

  • ORA-12504: TNS listener was not given the SERVICE_NAME in CONNECT_DATA

Environment

  • Tableau Desktop
  • Tableau Server
  • Oracle

Answer

Before performing the below steps, ensure that Oracle Net Service has been installed on the machine hosting Tableau Desktop or Tableau Server.

To install Oracle Net Services, you run Oracle Database Client software installer, and choose the component 'Oracle Net Services' to be installed. Oracle Database Client software is available on installation media or you can download it from the Oracle Technology Network website.

To learn more about Oracle Net Services, see Introduction to Oracle Net Services
 
CLICK TO EXPAND SOLUTION
On Windows

Step 1:Verify whether a TNSNames.ora file exists on your computer. 

By default, the TNSNames.ora file can be found at {oracle directory}\network\admin. For example, C:\Oracle_Client\network\admin
If no TNSNames.ora file is present on your computer, create one using a text editor. Below is a potential template for an entry. Elements in brackets must be replaced by values obtained from your database administrator.

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


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

Step 2: Set the TNS_Admin environment variable.

  1. Select Start > Control Panel > System 
  2. Select Advanced System Settings
  3. In the System Properties dialog box, on the Advanced tab, select Environment Variables. 
  4. Under System Variables, click New. 
  5. 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.
  6. Click OK int he Environment Variables dialog box and the System Properties dialog box. 
  7. Restart your computer to ensure that the new variable is recognized. 

Step 3: Use the Oracle net service name in Tableau. 

  1. Close all open Tableau workbooks and open a new instance of Tableau.
  2. 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
On Mac

Step 1:

  1. Close Tableau Desktop if it is open. 
  2. Ensure that you have downloaded and installed the Oracle drivers for Mac from Tableau's Drivers page.
  3. 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 

  1. 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.

  2. Type your password when prompted.

  3. Type the following:

    launch setenv TNS_ADMIN /etc

  4. Press Ctrl-X, then Y, then Enter to save changes and exit nano.
  5. Restart your Mac.

Step 3: Verify that the TNS_ADMIN variable was set: 

  1. Start Terminal and type the following command:

    export

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

    declare -x TNS_ADMIN="/etc"

  3. 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:

  1. Copy the tnsnames.ora file to a location the tableau user has access to
  2. Set permissions on the file.
  3. 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 versions 2019.3.x and up:
Copy the tnsnames.ora file to the /etc directory.
For versions 2019.2.x and earlier:
By default, the unprivileged user is named 'tableau'. All Tableau Server processes are run with this account, and it must be able to locate the tnsnames.ora file to use it.
If you have already installed the Oracle connector, we recommend you copy the file to the directory /opt/tableau/tableau_driver/oracle.
You can confirm that the directory is accessible by the user by using 'su' to change to the account and changing to the directory. For example:
sudo su tableau
cd /opt/tableau/tableau_driver/oracle
If these steps result in a 'permission denied' error, you need to update the permissions on the directory you are using.

Step 2: Set permissions for the tnsnames.ora file

NOTE: This is not something that needs to be done post 2019.3. 
  1. Navigate to the file path where the tnsnames.ora file is located.
  2. Grant permissions to the file by running the command: chmod 666 tnsnames.ora
  3. Verify file permissions with the following command: ls -l
Note: The environment path should not have a trailing slash; if the format of the path is incorrect, users may encounter an ORA-12154 error.

**Post 2019.3**:
As tabsvc is no longer present on Linux in this version, the above step is no longer applicable.
Instead, place the tsnames.ora file in the /etc directory on the Linux machine. No environmental variable is necessary.

Step 3: Set the TNS_ADMIN environment variable to point to the directory from step (1)

For versions 2019.3.x and up: you can skip this step; no environment variable is required.
For versions 2019.2.x and earlier:
  1. In a text editor, open the file that matches your version of Tableau Server:
    • Tableau Server 10.5.x -- /etc/systemd/system/tabsvc_0.service
    • ​​Tabelau Server 2018.1.x - 2019.2 --  /var/opt/tableau/tableau_server/.local/share/systemd/user/tabsvc_0.service
  2. Add the following line, where "/path/to/file-folder" is the directory you copied tnsnames.ora to in step 1:
Environment=TNS_ADMIN=/path/to/file-folder
  1. Save the changes to the file.

Note: The environment path should not have a trailing slash; if the format of the path is incorrect, users may encounter an ORA-12154 error.

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)
   )
 )

 

Note: If you have SSL configured on Oracle, see additional steps below.

Additional Information

Additional configuration steps if you have SSL configured on Oracle

Configuring ORACLE_HOME and TNS_ADMIN for Oracle (With SSL configured on Oracle)

sudo su -l tableau
      touch /var/opt/tableau/tableau_server/.config/systemd/tableau_server.conf.d/oracle.conf
      echo "ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_1" | tee -a /var/opt/tableau/tableau_server/.config/systemd/tableau_server.conf.d/oracle.conf
      echo "TNS_ADMIN=/u01/app/oracle/product/12.1.0/client_1/network/admin" | tee -a /var/opt/tableau/tableau_server/.config/systemd/tableau_server.conf.d/oracle.conf 
      chmod 744 /var/opt/tableau/tableau_server/.config/systemd/tableau_server.conf.d/oracle.conf
exit

cd /opt/tableau/tableau_server/packages/scripts.near.xx.xxxx.xxxx/
            sudo ./stop-administrative-services
            sudo ./start-administrative-services
source /etc/profile.d/tableau_server.sh


Discuss this article... Feedback Forum
Did this article resolve the issue?