SQL Server Linked Server with tnsnames.ora on network share - ORA: 12154

Question

Having an issue getting a SQL Server linked server to Oracle working while using a tnsnames.ora file on a network share.

If I copy the tnsnames.ora file to the local server, the linked servers work fine. However, we keep the file on a network share. My sql service accounts have read access to the share. I configure TNS_ADMIN system variable to the network share, the linked servers no longer work. I get ora-12154: could not resolve the connect identifier specified. tnsping and sqlplus work on the server. When I use process monitor to investigate further, I see:

Operation: createFile
Result: ACCESS DENIED
...
Impersonating: domain\MyLogin

This seems like an issue, but is maybe a false positive? If a process is trying to impersonate my account and access a remote resource it will fail since we don't have Kerberos configured to handle double-hop.

SQLPlus and TNSPing work just fine with the network share configured.

I've looked at this post and tried the items that seemed relevant, but had no success.

Additional Info:

sqlnet.ora has this: SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

I am able to open a file browser as a service account and open the tnsnames file.

Answer 1

I had this same issue while trying to connect a oracle 10g database via my WCF serivce developed in .NET 4.0 framework.

I was having multiple instances of ORACLE installed in my system. So, I modified the ORACLE_HOME to point to the Oracle 10g and it worked.

Also check the following:

Your service name might have an alias, so Make sure that your listener is listening for the same service name that you are using and check for both local and global entries. Check:

$ORACLE_HOME/network/admin/tnsnames.ora 

Check your global_name setting with this SQL:

select * from global_name;

Also, Please make sure you add the Key TNS_ADMIN in the registry and create a enviroinment variable with name TNS_ADMIN

Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name

Specify the correct path where the oracle is installed for Example

X:oracleproduct32bit10.0.1.0.0NETWORKADMIN

Edit

The below video also looks quite helpful. Please check.

https://www.youtube.com/watch?v=Sec8WG8gQPg