KNOWLEDGE BASE

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


Published: 29 Nov 2016
Last Modified Date: 25 Sep 2017

Question

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

Environment

Tableau Desktop
Oracle

Answer

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.

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. 
 

Additional Information

 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


How to use ping on Mac: Pinging Websites, Domains, or IP Addresses
OS X: About Network Utility

Did this article resolve the issue?