3 Replies Latest reply on Mar 29, 2016 11:32 AM by Andrew Macey

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

    Su ve

      Hi guys,

       

      We have a Tableau Server 9.2 (64 bit) where oracle connection is working absolutely fine. We didn't install any oracle client on this machine (we don't want to install oracle client).

       

      Now we have a requirement of making use of TNSNAMES on the Tableau server machine (i.e., using tnsnames.ora reports need to connect Oracle DB).

       

      I have followed the steps in this article "Setting an Oracle Connection to Use TNSNames.ora | Tableau Software ".

      Created folders Oracle_Client/network/admin in C drive. Placed the tnsnames.ora and sqlnet.ora files in admin folder. Added a new environmental variable TNS_ADMIN in system variables with the above path. (Copied the tnsnames connection descriptor from a machine where oracle client is running perfectly)

      After these I have restarted the machine. But when I am trying to connect to Oracle db from a report on tableau server, it is throwing the error message

      "ORA-12154: TNS:could not resolve the connect identifier specified".

       

      Am I missing something?

       

      Thanks,

      Su ve

        • 1. Re: "Oracle database error 12154: ORA-12154: TNS:could not resolve the connect identifier specified"
          Hitesh Bhanushali

          Hi Su Ve

           

          Check if the Firewall Port is Open.

           

          Telnet to the server from cmd prompt.

           

          We had a similar issue. Connection to Oracle db was fine, But we were getting same TNS error. Firewall Port opening solved the issue.

           

          Thanks,

          Hitesh B

          • 2. Re: "Oracle database error 12154: ORA-12154: TNS:could not resolve the connect identifier specified"
            Su ve

            Thank you Hitesh for the reply.

             

            Just to clarify: We dont have a separate Oracle client on Tableau Server 9.2. And reports are working fine when we provide full connection details but the we are getting error only when we are using tnsnames from Tableau server to connect to Oracle DB.

             

            I have executed this command "telnet [server name]", I got error message "Connecting to [server name]...Could not open connection to the host, on port 23: Connect failed".

            Then I have opened the port 23 from firewall by creating new rule in inbound rules.

            When I tried the above command again, it shows the same error message again.

             

            Do I need to do anything else or is it a wrong port number(23)?

             

            Thanks,

            Su ve

            • 3. Re: "Oracle database error 12154: ORA-12154: TNS:could not resolve the connect identifier specified"
              Andrew Macey

              tnsnames.ora and sqlnet.ora files are part of the native Oracle client. They serve no purpose unless you have Oracle client installed. It's the Oracle client that looks at your TNS_ADMIN variable and understands the content of tnsnames.ora and sqlnet.ora.

               

              TNSPING it the program (part of Oracle client) to use to test basic Oracle client connectivity. From a command prompt (logged in as the user that Tableau Server runs under) on your server, you would enter:

               

              tnsping JUNK

               

              Ignore the "TNS-03505: Failed to resolve name" in the output. The path displayed is the location that the Oracle client is looking for tnsnames.ora and sqlnet.ora in. If that's not the path you set TNS_ADMIN then fix that.

               

              Now test connectivity to your Oracle database:

               

              tnsping [MY-DB.world]

               

              If success, then you are good to go. The TNSPING output shows the values it found in the tnsanmes.ora for the connect string you specified. The information includes the port that Oracle is trying to connect on (from the tnsanems.ora entry).

               

              If the tnsping fails, but takes several seconds to do so, that often means it timed out waiting for a response (this could indicate firewall blocking).

               

              The telnet command should include the port number too (get the host name and .

               

              telnet [db-host-name] [db-port-num]