Oracle (0x80004005)ORA-12154: TNS:could not resolve the connect identifier

Question

I'm trying to connect to an oracle db from an ASP classic application, however I keep running into the ORA-12154 error.

1. TNSNAMES.ORA is configured properly

   DBSOURCE.A.B.com = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = C.D.B.com)(PORT = 1231)) ) (CONNECT_DATA = (SERVICE_NAME = DBSOURCE) ) )

2. I can TNSPING

   C:\Documents and Settings\USERID.A>tnsping DBSOURCE

   TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 09-MAR-2 011 09:12:31

   Copyright (c) 1997, 2007, Oracle. All rights reserved.

   Used parameter files: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora

   Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = C.D.B.com)(PORT = 1231))) (CONNECT_DATA = (SERVIC E_NAME = DBSOURCE))) OK (30 msec)

3. I can create an ODBC connection and I've tested the connection which is successful.

4. I can connect to the oracle db through toad.

5. I've checked my sqlnet.ora file

   NAMES.DEFAULT_DOMAIN = A.B.com

   SQLNET.AUTHENTICATION_SERVICES= (NONE)

   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

6. I've also checked that the TNSNAMES.ORA as one of my system paths.

7. I can connect to the DB with SQLPLUS

Here is the code that throws the error

dim CnnStr

Set Cnn = Server.CreateObject("ADODB.Connection")

CnnStr="Provider=MSDAORA.1;Password=pass;User ID=user;Data Source=DBSOURCE"

Cnn.Open CnnStr

I am running windows XP Pro 32bit SP3

I've tested this on a co-workers machine and the asp app has no issues, am I missing a configuration somewhere or did I mess up my config?

Thanks for the help everyone!

Answer 1

It seems all the settings have been checked but here is my suggestions for some trouble shooting:

• How about restrictions on the account IIS is running under? Does it have the file permissions to read the TNSNAMES? Have you tried to give the user more rights. Even run IIS with Local As System Account? Remember to put it back the way it was.
• You are sure there is not 3rd party app such anti virus or firewall that could influence / block access. (Disable them to test but do not forget to re-enable :)

(What did you finally do to solve it?)

Answer 2

"this works on a co-worker machine but not mine."

ORA-12154 is usually a configuration issue, and it would certainly seem to be so in your case. The short answer is to compare your machine with your colleague's one and figure out what the difference is.

Things to check include:

• the TNANAMES.ORA file
• the SQLNET.ORA file
• the LISTENER.ORA file (if you're using a local database)

Answer 3

Do you have multiple Oracle homes? Check that IIS doesn't point to the other ORA_HOME (and thereby not finding your tnsnames.ora entry). I'm not very familiar with IIS configuration, so I would test this by adding the TNS entry to all tnsnames.ora on my PC.

Answer 4

If you are running a 64-bit OS and oracle 10 try installing all the patches for oracle. Something similar happened to me, SQL plus and all else worked, except my .NET program. I was running a .Net program out of the "Program files (x86)" folder. Installing patches fixed it.

Answer 5

The likely problem is the lack of read permission upon the Oracle directories by the User IIS is running-as.

As @Derick says, temporarily set the permissions wide-open on the $ORACLE_HOME directory (Everyone = Full Control), restart IIS, and test. When that works, give the IIS User read permission against that directory.

You can also confirm this issue using Process Monitor from Microsoft SysInternals, and seeing the failure message during the CreateFile(...) API call to open the DLLs or read the TNSNAMES.ORA file. Given the way temporary files work when selecting through cursors, you may also need to give the IIS user write permissions into some directories.

Answer 6

Try setting the value of the environment variable TNS_ADMIN on the machine with the problem to /network/admin (or wherever your tnsnames.ora file lives) and see if that helps.

Answer 7

I had the similar problem and found a simple solution. You don't have to modify any config files. Hope this can help you:

CREATE DATABASE LINK server2_db
  CONNECT TO dbuser IDENTIFIED BY pwd USING
 '(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = server_2)(PORT = port_num))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = server2_db)
   )
 )';

The solution is from here:http://oraclequirks.blogspot.com/2008/11/ora-12154-tnscould-not-resolve-service.html