OracleClient + TNSless connection with .NET

Question

We are using System.Data.OracleClient and the abstract base classes DbConnection, DbCommand (etc) to connect to Oracle.

The connection works fine in our development stages. During staging we encounter the error ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

Our goal was to connect without a TNS entry, and providing all the relevant information in the connection string (www.connectionstrings.com/oracle#19)

I can confirm that SQL plus will connect to the desired schema from the staging server (the listener is listening). The TNS entry hooked into SQLPlus matches all the credentials of the query string being built.

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;

Is there an installation on the server we missed? Something we can tweak?

Any help is appreciated.

Answer 1

If I recall correctly, I had the same issue until I switched to using the Oracle providers and OracleDbConnection and OracleDbCommand. The generic ones don't always instantiate the correct provider in all cases.

Answer 2

Microsoft deprecated the System.Data.OracleClient See this article : http://blogs.oracle.com/databaseinsider/2009/07/microsoft%5Fdeprecates%5Fsystemdataoracleclient%5Fnet%5Fdevelopers%5Ffor%5Foracle%5Fshould%5Fmigrate%5Fto%5Foracle%5Fdata%5Fprovider%5Ffor%5Fnet.html

Answer 3

A SID is not a Service Name. A service name is usually a fully qualified database name, while a SID is a short identifier.

A service name might be dbname.company.com while your SID is dbname. An instance can actually have several service names associated with it, but only one SID.

Change your SERVICE_NAME to SID in your connect string, or specify your service name for the SERVICE_NAME option.

Answer 4

If your Oracle client is in version 10+, you could also use EZCONNECT (which stands for Easy Connect naming method). Your connection string would then look like this :

"Data Source=MyHost:MyPort/MyServiceName;User ID=myUserName;Password=myPassword"

Combined with Oracle Instant Client, it makes the use of Oracle feel almost professional !...