Error "ORA-12154" Connecting to Oracle When Not Using TNSNames.ora

Issue

When using an Oracle connection, which was created on a computer that uses LDAP, TNSNAMES, or EZCONNECT or where all three connection parameters (Server, Service, and Port) were configured during the initial connection, the connection will no longer work and the following error might occur:

"Oracle database error 12154: ORA-12154: TNS:could not resolve the connect identifier specified"

When you try to connect to the same Oracle database using the service name from TNSNames.ora file in the Server parameter and leaving the other two parameters blank, the connection is successful.

This behavior can occur when:

Sharing workbooks between computers running Tableau Desktop, one of which has the above configuration file(s), and the other does not.
Publishing a live connection to Oracle from a computer running Tableau Desktop that uses one of the above configuration file(s) to an instance of Tableau Server which does not. (If the connection is published as an extract, it will work, but will not refresh.)

Environment

Tableau Server
Tableau Desktop
Oracle

Resolution

Option 1:

Work with your Oracle database administrator to allow the HOSTNAME connection type for the Oracle client on the computer running Tableau Desktop or Tableau Server.

Option 2:

The following workaround may allow the HOSTNAME connection type:

On the computer running Tableau Desktop or Tableau Server, find the sqlnet.ora file. The folder containing the file should be specified in the TNS_ADMIN variable and should also contain the TNSNames.ora file. For more information, see "Set the TNS_Admin environment variable" in Setting an Oracle Connection to Use TNSNames.ora and Sqlnet.ora at Oracle FAQ's.
Note: If this folder does not contain a sqlnet.ora file, create a sqlnet.ora file.
In the sqlnet.ora file, amend the NAMES.DIRECTORY_PATH line to say the following or add the following line to the sqlnet.ora file:
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)

Note: If this issue occurs in a Tableau Server distributed environment, perform the above steps on all computers in the Tableau Server installation. Include the service name and port number, use the FQDN (fully qualified domain name) in the "Server" text field of the connection dialog.

Cause

The sqlnet.ora file is not allowing the Oracle driver to look for the database using all three connection parameters.

When TNSNAMES.ora or LDAP.ora are in use, they allow Oracle connections and their parameters to be defined outside of the Tableau connector. The "net_service_name" portion will be used as the server field, which when entered along with credentials fetches the service name and port number from the TNSNAMES/LDAP file. Computers that do not use TNSNAMES/LDAP require the service name and port number to be entered for the connection to work, they also require using an FQDN for the server instead of a customized name (ex: "oracle.test.tsi.lan" would be the FQDN, whereas "Oracle_01" could be used as a net_service_name)