Home » RDBMS Server » Networking and Gateways » ODBC connection error with TNS-12154 (DB Oracle 10g connecting from WinXP with Oracle 9i client and Oracle 9i DS)
ODBC connection error with TNS-12154 [message #307333] Tue, 18 March 2008 08:57 Go to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Hi All,

I have read several postings related to this problem but non of them seemed to help.

I can connect to my Database from Toad Normally using my TNS name set. I can successfully TNSPING the Service Name and receive 'OK' return. But when I try to set an ODBC connection and create a New DSN, I always get a TNS-12154 error.

I have checked my tnsnames.ora, sqlnet.ora, and listener.ora multiple times and all seem OK. I tried to reconfigure the Service name many times and still cannot create the ODBC connection to the Database.

Attached is Print Screen of the ODBC error and the TNSPING result. Again I can normally access the Database from my machine through Toad and using the same Service Name.

I am assuming the problem is that I have 2 ORACLE DIRECTORIES, one is for the Oracle 9i Client (call it DIR1), and one for Oracle 9i DS (call it DIR2). Although I have configured the TNSNAMES in both, I can only connect using the DS directory (DIR2) settings and TNSNAMES.ora. But the ODBC only recongnizes the TNSNAMES in configured in (DIR1), is there a way to MAP the ODBC to the settings under the second Oracle Directory (i.e. the DS directory), I think if I was able to do this I can get the connection working.

Kindly can you advise.

Regards,
Baz

Report message to a moderator

Re: ODBC connection error with TNS-12154 [message #307335 is a reply to message #307333] Tue, 18 March 2008 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does a file by the name of "SQLNET.ORA" exist?
If so in which directory & what is its contents?

Report message to a moderator

Re: ODBC connection error with TNS-12154 [message #307342 is a reply to message #307335] Tue, 18 March 2008 09:15 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Here are the contents of the SQLNET.ORA directories:

Under the path: C:\oracle\Oracle9i\network\admin

# SQLNET.ORA Network Configuration File: C:\oracle\Oracle9i\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = <Our_Domain>.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (HOSTNAME, ONAMES)


and Under the path: C:\oracle\Oracle9iDS\network\admin

# SQLNET.ORA Network Configuration File: C:\oracle\Oracle9iDS\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = <Our_Domain>.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)



Thanks anacedent.

Hope this helps.

Report message to a moderator

Re: ODBC connection error with TNS-12154 [message #307345 is a reply to message #307333] Tue, 18 March 2008 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Do you see the difference between the two files?

Nice response.

in the directory which does not work, rename the sqlnet.ora file to a different name & then copy the sqlnet.ora file from the directory which works over the top of the one which does not work

Report message to a moderator

icon14.gif  Re: ODBC connection error with TNS-12154 [message #307509 is a reply to message #307345] Wed, 19 March 2008 01:04 Go to previous message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks anacedent, YOU'RE THE MAN Cool

As I suspected and you pointed out the problem, the "NAMES.DIRECTORY_PATH" parameter in my Oracle 9i Directory was causing the problem. Once I replaced it with the other SQLNET.ora (setting the value to '(TNSNAMES)'), the connection went perfectly well and I successfully created my ODBC connection.

Many thanks dude.

Cheers,
Baz

Report message to a moderator

Previous Topic: OID Unique Users Not Guests
Next Topic: ORA-27101: shared memory realm does not exist (connect from unix to window db)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 14:00:04 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.