6

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))
    )
Tom Troughton
  • 3,941
  • 2
  • 37
  • 77
  • What does the connection string look like? – Joachim Isaksson Jan 23 '13 at 18:23
  • 1
    Have you tried opening Process Monitor before running your application, and find out where your application is trying to find the tnsnames.ora file? – Feyyaz Jan 23 '13 at 19:09
  • @JoachimIsaksson "Simply Data source=RMSUSNAP;User ID=uuuu;Password=pppp;" with provider name "System.Data.OracleClient" - the username and password are the same I'm testing the connection with in Net Manager. – Tom Troughton Jan 24 '13 at 09:50
  • @sahs Sorry, not familiar with Process Monitor - is that an Oracle thing? Can't see anything named that in the Oracle start folder. Would appreciate a pointer. As I say, I'm new to Oracle, but an experienced .NET dev. – Tom Troughton Jan 24 '13 at 09:52
  • By the way, the .NET app works fine on other developer machines so I'm assuming it's related to my Oracle setup. – Tom Troughton Jan 24 '13 at 10:06
  • Updated original post with work-around. Still looking for working answer. – Tom Troughton Jan 24 '13 at 12:05
  • Process Monitor shows the file system activities of an application, which will help you identify where your application tries to find tnsnames.ora, whether it succeeds to read it, which registry key its trying to read-write, etc. Google it. – Feyyaz Jan 28 '13 at 07:20
  • By the way, you should use RMSUSNAP.WORLD in your connection string, instead of just "RMSUSNAP" – Feyyaz Jan 28 '13 at 07:21
  • @sahs Thank you, that seems to work. Strange that other dev machines work without the .WORLD postfix. Still, happy to mark you as the answer if you add it below. – Tom Troughton Jan 29 '13 at 09:47

4 Answers4

3

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

Feyyaz
  • 3,147
  • 4
  • 35
  • 50
2

Have you checked this question?

It's been a long time I last used Oracle but I do remember spending at least a couple of days to make it work properly in my Windows 7 x64 machine.

The last thing I remember changing was the environment variables (ORA_HOME, if I recall it correctly) to point to the right folder (x64 in my case).

I'm sorry I cannot remember more (will try to search for any notes) but the link I mentioned might put you in the right path.

Community
  • 1
  • 1
tucaz
  • 6,524
  • 6
  • 37
  • 60
  • Plus one for the pointer @tucaz, but unfortunately still nothing working for me. Feel I've been through every forum on the internet and literally same error every time. I've found I get the same message when I try to connect in Visual Studio's Server Explorer too. As far as I can tell my setup is identical to the other devs here but they work fine. – Tom Troughton Jan 24 '13 at 11:49
0

As I am experiencing (was) the same problem and error... Ensure that your sqlnet.ora file is not prepending additional connection information to the connections stored in the TNSNAMES.ora file. In general, we just remove the sqlnet.ora file or rename to something else (sqlnet.ora.org). Once this file is no longer adding additional information to the connection strings, blammo, everything just works.

FlexibleCoder
  • 69
  • 4
0

Just had this problem: go into IIS and look for the web application under Sites, then click the web application and click on SSL Settings. Under client certificates click the Accept radio button and apply.

Jeff Mergler
  • 1,384
  • 20
  • 27
Eric Piper
  • 11
  • 3