Oracle Connection Problem

Question

Hello,

I recently got a new machine at work, and moved from XP 32 Bits to Vista 64 Bits.

Whatever I do I cannot get my .Net projects to connect to the oracle Server... I can connect to the database using oracle sqldeveloper, but not from my projects...

I Get This error (which is puzzling me...)

"An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

My first WTF was the "SQL Server" part as my entire project remains unchanged and I don't use SQL Server at all (in this project)...

My connection string is as follows:

<add name="DefaultDb" providerName="System.Data.OracleClient" connectionString="Data Source=ora11dev;User Id=user;Password=pass;Integrated Security=no;"/>

I have re-installed the oracle client and oracle connection tools for .net but my problem persists....

Can any one point out something I might be missing?

EDIT:

When I open my connection I get this error: ORA-12154: TNS:could not resolve the connect identifier specified

And i have my tnsnames.ora correct...

Answer 1

I would make sure:

1. You can tnsping your DB instance
2. You can use SQL*Plus to log in

I would then make sure you're using a 64-bit client for all your Oracle tools (client and ODAC). Lastly, make sure that, if you have multiple Oracle Homes, that the last one installed is configured to allow connections. ODAC, I believe, uses the last installed Oracle Home to get its configuration data.

Also, I've heard tell that an 11g client has issues connecting to a 10g instance, though I personally have never seen such an issue.

My guess is a configuration setting somewhere in Visual Studio that is doing something, or some firewall rule that isn't letting Visual Studio communicate out. But these are just wild guesses.

Bottom line is if #1 and #2 are successful, chances are it's in your Visual Studio setup.

Answer 2

Try setting the environment variable TNS_ADMIN to point to the directory where your tnsnames.ora file resides. This will tell you if you've got an issue with your client locating the proper Ora Networking files.

Edit: What is the path name to visual studio? Are there parens in it, e.g., "C:\Program Files (x86)\Microsoft Visual Studio\"? If so, there is a known bug that is exposed with this scenario. The fixes are to install a patch, or to reinstall Visual Studio in a location that does not have parens in the path.

Answer 3

I think I just discovered my problem....

looks like oracle has a problem with parentheses in the applications path that use oracle... This explains why Visual Studio wont access Oracle: "C:\Program Files (x86)\Visual Studio..."

http://tiredblogger.wordpress.com/2007/11/21/oracle-data-access-components-odac-with-64-bit-development/

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/ab662d63-6385-4f73-b27f-d526048f601f/