Why do I get Oracle connection errors only in a VS 2008 web app/site project?

Question

Using Visual Studio 2008 on Vista 64 bit, if I create a test web site or web application that looks like this...

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        OracleConnection connection;
        connection = new OracleConnection("User Id=user;Password=userpass;Data Source=dataSource;");
        connection.Open();
    }
}

...and run it, I get the following error.

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

I'm aware of this issue regarding parens... http://duanesbrain.blogspot.com/2007/01/ora-12154-using-c-with-microsoft-visual.html

Visual Studio 2008 is installed in Program Files (x86) but another dev has that same setup and it works fine for him. The project itself is not within a directory with a name that has parens.

I know it's not my tnsnames.ora or my sqlnet.ora files. I have my machine set up the exact same way as all the devs who've been working on this project for a lot longer than I.

I am also 100% certain the connection string I am using is correct.

If I create a console application with the same exact code, it connects fine. I can ping the oracle server and connect directly through command prompt using that connection string info also.

Another odd thing is that if I create a web application or web site with the same code using VS 2005 (also installed in Program Files (x86)) it works fine as well.

It seems to be restricted to web applications and sites only using VS 2008.

EDIT: I should add that my oracle folder has all permissions given to everyone currently.

EDIT: Oracle version is 10.2.0

EDIT: Thanks for the insights, I never could get it to work and the other devs and I decided it would be faster to build an XP vm to work on. In a few hours I had one set up and everything runs fine.

Answer 1

I had problems with oracle home before. Can you check and if required modify he registry to ensure that ORACLE_HOME is setup correctly. the registry path is HKLM\SOFTWARE\ORACLE\

I have a key named KEY_OraOdac11h_home1 and the value for ORACLE_HOME is the file path to where I installed the Oracle client (C:\Oracle\product\11.1.0\client_1)

You can also try adding the system environment variable ORACLE_HOME with value C:\Oracle\product\11.1.0\client_1.

Change the file path to where you have installed oracle client.

Answer 2

The error message you are seeing indicates a problem in one of: your tnsnames.ora file, your sqlnet.ora file, or your Data Source value. You should check the other developer's versions of these files.

Have you tried using the EZCONNECT method to verify connectivity? (see www.connectionstrings.com/oracle) You may need to modify your sqlnet.ora file to enable EZCONNECT.

Data Source=username/password@myserver//instancename

Answer 3

Is it a 64bit/32bit problem? You could try setting the project to build 32bit, then force IIS into 32bit mode.

How to change IIS: http://support.microsoft.com/kb/894435

Answer 4

Does this happen in both debug and release modes? Are you ruunning the website with the WebDev.WebServer.exe (casini) exe web server or with IIS. Try switching to the other one. Does this happen only when you debug or when you are running it as well?

Perhaps this website is running as a different user / process and this account does not have the paths configured for oralce?

Answer 5

I've experienced mixed results and odd issues when attempting to connect to Oracle through .NET and usually it's an odd environment setup issue.

Try this connection string (does not use the tns file but uses the same details inside of it)

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=HOST IP)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SERVICENAME)));User Id=USERID;Password=PASSWORD;

This will get you started to see if it's an issue with you connecting to Oracle or if your TNS file is not resolving appropriately.

You should have something in SOFTWARE\ORACLE\ such as HOME, ALL_HOMES, HOME0, etc because this is how the drivers identify your oracle home and register the TNS files.

I would also look at your firewall on the developement box you're running VS on and check your dll references to make sure you are using the same Oracle Dlls - also are you using the MS oracle dlls or the ones from Oracle?

Answer 6

I got the same issue and after several HOURS!!!.. Found out that the version of the Oracle installation was not updated. I installed 10.2.0.4 and Viola (On 10.2.0.1 it does not work!) Some Oracle Bug.