ODP .NET TNS:no listener

Question

I'm currently trying to connect to an Oracle database. I can connect fine on my development machine, but when I deploy to my Win2k3 sp2 server I get the following error.

Faile to open connection... Oracle.DataAccess.Client.OracleException ORA-12541: TNS:no liestener at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object scr, String procedure, Boolean bCheck)

Any thoughts, ideas, instructions would be helpful. I'm using Oracle.DataAccess.dll version 4.112.1.2.

Thanks

Answer 1

The actual issue was a firewall.

Apparently my local development machine had access to the server hosting Oracle, but my development Server did not. Once there was an open connection between the two servers, the TNS:no listener error went away.

This appears to have been a bogus error message returned by the Oracle Client. The listener was there and my configuration (connection string) was valid, but there was no available connection what-so-ever between the two machines.

Answer 2

I had the exact same issue. For some reason, it seems that using the "alias" was not working.

My tnsnames.ora file had something like this:

oracle =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = myoracleserver.mydomain.com)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SID = MYORACLESERVER)
    )
  )

I then changed my connection string's Data Source from (using the alias):

User Id=scott;Password=tiger;Data Source=oracle

To (not using the alias) :

User Id=scott;Password=tiger;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=myoracleserver.mydomain.com)(PORT=1525)))CONNECT_DATA = (SID = MYORACLESERVER)))

Everything works flawlessly with ODP.NET now. It was and is still working with the alias when I use an old ADODB connection.

UPDATE:

ODP.NET cannot read your tnsnames.ora file directly from your app. You need to tweak like I said in my answer or you can also specify where your tnsnames.ora file is in your Web.Config or App.Config file.

See my other answer for more details about all options you have.