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

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

As an Oracle DBA I sometimes have to work with Windows. Maybe you can adopt from my experiences with Oracle on Windows.

Scenario:

An Oracle DB runs under a domain user. I want to restore a database from a backup which is located on a Windows share (sounds like "read" but it obviously isn't). I (or let's say the windows team) did not manage to find the proper way to grant the required permissions.

After many tries, the admins grant "everything" to the entire Oracle server.

Even though the Oracle process runs in a user context we did not find a set of permissions for the user only. Only the permissions for the entire server enabled the restore process to access the data.

From security point of view this is a horrible solution! But maybe it will help you to come closer to a solution (and if so, please share :-)).