Oracle InstantClient ODBC connection fails in DbProviderFactory when the ODBC Test Connect succeeds

Question

• Installed the InstantCLient 11.2.0.4 Basic Lite and ODBC packages
• Set up the ORACLE_HOME and TNS_ADMIN environment variables and added the path for the installation to the Path environment variable
• Created my tnsnames.ora file in that location
• Set up my DSN using the TNS in the file and did a test connection - succeeded.
• Used C# code I had developed before which uses the DbProviderFactoies generic method of connecting to Data Providers (already works with Teradata, SQL Server)
• Created my Data Source configuration with the following connectionString:
  <?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="DataSource" connectionString="DRIVER={Oracle in instantclient_11_2};Data Source=Test Oracle;Persist Security Info=True;Password=password;User=user" providerName="System.Data.OracleClient"/> </connectionStrings> </configuration>
• Results in "ORA-12560: TNS:protocol adapter error".
• I tried many variants on the connection string and they all lead to an error of some sort. Any help will be much appreciated.
  
  

System Information

• Windows Server 2003 64 Bit
• C# .NET 3.5
• Oracle 11g 11.2.0.3.0 remote database

Answer 1

I am not sure what was wrong, but I discovered that the very simple connection string below worked! It is using the generic System.Data.Odbc too.

<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="DataSource" connectionString="DSN=MyDSN;Uid=user;Pwd=password" providerName="System.Data.Odbc"/> </connectionStrings> </configuration>

Answer 2

It depends on which protocol you are using to connect: TCP or TCPS.
If TCPS, then the .NET Framework version should be above 4.5.
You get "ORA-12560: TNS:protocol adapter error" if the .NET Framework version is below 3.5.