Test connection works, actual connection ends up with ORA-12154

Question

I'm trying to connect Microsoft SSAS with an Oracle database, with no success. I searched and searched before asking here, and I hoped the problem could lie in some typos in tnsnames.ora and sql.ora file. But unluckily not. I report them here:

sqlnet:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames:

DEVELOPS.WORLD =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = xxx.local)(Port = 1521))
     )
     (CONNECT_DATA =
       (SID = DEVELOPS)
     )
   )

Note that, when I try "Test Connection" in SSAS, it does work. But as soon as process a table, for example, here comes ORA-12154. I also tried with ping and tnsping, and they both work: host is up and running and the alias is working, too. Drivers (32bit and 64bit) are both installed. So, I really don't know what to look for. Thanks!

You forgot to mention the database details you use in your client application. — Rene, Oct 12 '17 at 08:31
How did you install Oracle drivers 32 bit and 64 bit? Compare with this instruction: https://stackoverflow.com/questions/24104210/badimageformatexception-this-will-occur-when-running-in-64-bit-mode-with-the-32#24120100 Perhaps the interfere with each other. — Wernfried Domscheit, Oct 12 '17 at 09:12
I didn't install the drivers, I'm working on a server set up by somebody else, so I found'em already in place. Directories seem to be ok. I'm adding this: from the very same SSAS, using the same tnsames/sql, I can connect to another server, load tabs etc. So the problem is just on one db (wth differente name and host obviously). — Crappotronic, Oct 12 '17 at 10:13

score 0 · Answer 1 · answered Oct 12 '17 at 09:11

Most likely SSAS does find your sqlnet.ora, resp. tnsnames.ora file. Looks like each driver has its own method to look for location of these files, see Determining locatation of relevant tnsnames.ora file

Ensure that each setting listed below is pointing to actual location of your sqlnet.ora/tnsnames.ora files. Use Symbolic Links, if required.

Environment varialbe TNS_ADMIN
Registry Key HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN
Registry Key HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN
Folder %ORACLE_HOME%\network\admin
TNS_ADMIN setting of section <oracle.manageddataaccess.client> in your .NET config file (only applicable for Oracle ODP.NET Managed Driver)

Test connection works, actual connection ends up with ORA-12154

1 Answers1