ORA-12154 Windows 7 32bit ORACLE 11g ODBC Driver CONNECT

2682152

Hi All,

I'm having problems connecting to extenal database using Oracle ODBC driver for Windows 7 32bit  .

I have downloaded the instantclient odbc nt 11.2.0.4zip drivers for 32-bit and unpacked it

and run the installer and choose the default location ie c:\app\product\11.2.0\client_1

2.I done all the necessary setting ie set the Path and creating new user variable for tns admin

in the system environment and modified the hostfile on etc sys32 and copied both tsnamesn and sqlnet

to Network\Admin file

3.I then started the Data Sources (ODBC) manager, configured the driver and tested the

configuration then I got a testing connection error SQLstate= 08004... ORA-12154 TNS couldn't connect.

could you please help. Thanks in advance.

Sdhamoth-Oracle

Hi,

For accessing tnsnames,ora in Network\Admin directory, Oracle ODBC driver requires Admin privilege.

So make sure you start ODBC Data Source manager with Admin privilege (ie right click on top of ODBC Data Source manager and select "Run as Administrator").

Thanks,

Senthil

2682152

Hi Senthil,

Thanks for your reply. I tried with all admin rights but I got the same error. Could it to do with that I have two tnsnames files belong to two different applications? here is more info about what I am doing; I am trying to migrate old XP programes into win 7. The old ODBC manager was using the old MS ODBC for Oracle to connect to the Database to view excel in office 10 and now this an Oracle ODBC driver to access a Database to view excel in Office 13.

Sdhamoth-Oracle

I couldn't understand why you need two different tnsnames files. Are they resides in two different directories with filename "tnsnames.ora"? In that case, you need to set the TNS_ADMIN appropriately.

If you haven't named tnsnames files as "tnsnames.ora", then Oracle ODBC driver may not able to access it.

Also if TNS_ADMIN env variable is not pointed to the correct directory where tnsnames.ora file resides, then Oracle ODBC driver won't be able to access it.

In any case, if Oracle ODBC driver unable to access the tnsnames.ora and there is no connection string value stored in the Windows registry, then it throws ORA-12154 error.

Thanks,

Senthil

2682152

Thanks Senthil,

Yes I have two tnsnames.ora in two different directories and I have set both the PATH and the User env to point to the tnsnames.ora in Admin folder eg %OraClient11g_home1%\app\product\client_1\Network\Admin\tnsnames.ora. I need the two applications for different database access proposes.

Sdhamoth-Oracle

Have you set TNS_ADMIN environment variable? TNS_ADMIN should point to only one directory.

2682152

Yes, as I mentioned above: I created new variable name TNS_ADMIN (for the new ODBC driver) in the user environment and set it to  the variable value to %OraClient11g_home1%\app\product\client_1\Network\Admin\tnsnames.ora.

Sdhamoth-Oracle

Directory path is sufficient. You shouldn't include 'tnsnames.ora' in that variable's(TNS_ADMIN) value.

2682152

I removed 'tnsnames.ora' from the path but still have the error!

Sdhamoth-Oracle

Are you able to connect to your database through SQL*Plus with your current configuration?

2682152

No, SQL*Plus ask for a password and when I tried I also got an error ora12203 unable to connect to destination.