ASP.NET webapp - ORA-12154: TNS:could not resolve the connect identifier specified

Question

I'm a new (ASP.NET) developer starting on an existing project, part of which connects to an Oracle database. I've not used Oracle before.

At the point the web app trys connecting to Oracle it's throwing the above exception.

Plenty of other posts refer to this exception but I feel I've followed all advice to no avail.

Here's an over view of my setup:

1. I've installed Oracle client version 11.2.0 to home dir "C:\app\TroughT\product\11.2.0\dbhome_1"
2. I've added a tnsnames.ora file from another developer machine (where this all works) into "C:\app\TroughT\product\11.2.0\dbhome_1\NETWORK\ADMIN"
3. I've changed the APP_BASE variable in sqlnet.ora to "C:\app\TroughT\product\11.2.0\dbhome_1\log"
4. I have (for now) given Everyone full control over the entire dbhome_1 directory and subdirectories
5. In the Oracle Net Manager I've tested the relevant connection with the same security credentials used in my ASP.NET connection string and it tests OK
6. I've added a TNS_ADMIN system variable with value "C:\app\TroughT\product\11.2.0\dbhome_1\NETWORK\ADMIN"

We must have missed something but I just don't know what. Please help!

UPDATE:

I have found that replacing the SID in the connection string with the full definition from my tnsnames.ora file actually works. Example below. Must be something to do with ASP.NET failing to locate my tnsnames.ora file, but I know the file is valid and as far as I can tell all my system variables are pointing to the right place. So at least I'm working now, but I'd like to understand why my tnsnames.ora file isn't working.

Failing connection string:

"Data source=RMSUSNAP;User ID=uuuu;Password=pppp;"

Working connection string:

"Data source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = replaced.host.name)(PORT = 1521))(CONNECT_DATA =(SID = rmsusnap)));User ID=uuuu;Password=pppp;"

Relevant part of tnsnames.ora:

RMSUSNAP.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = replaced.host.name)(PORT = 1521))
    (CONNECT_DATA =(SID = rmsusnap))
    )

Answer 1

You should use RMSUSNAP.WORLD in your connection string, instead of just "RMSUSNAP".