8

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.

Sam
  • 7,543
  • 7
  • 48
  • 62
  • 1
    Try restarting the MSSQL server to initialize the TNS_ADMIN variable for the MSSQL server processes. – Dmitry Demin Sep 05 '19 at 02:28
  • Thanks, but I've tried that. I'm aware through testing that the tns_admin variable change doesn't take effect until a service restart. – Sam Sep 05 '19 at 14:24
  • Is there a sqlnet.ora file in the TNS_ADMIN directory? If there is, show the contents of the sqlnet.ora file. – Dmitry Demin Sep 11 '19 at 09:32
  • Can you read the content of your `tnsnames.ora`, on the network share, via your *sql service accounts*? Did you actually try to printout the contents? – tukan Sep 12 '19 at 08:15
  • In our environment we always use local TNSNAMES files on servers. I have written an Oracle PL/SQL package that writes those files with server specific customizations as needed. That said, because SQL*Plus is working, I think what you are experiencing is a permission problem. Make certain that the service under which SS is running has access to your network share. – Brian Leach Sep 16 '19 at 23:15
  • Thank you all for your comments. I have added some additional information to my question. – Sam Sep 17 '19 at 21:06
  • @Sam Someone asked this as an answer, which could be deleted I guess: "Does the account you are using have permission to create a file on the share?" – kkica Sep 17 '19 at 21:33
  • The account is in a windows global group that has access to the share. I also opened explorer++ as the account and accessed the file successfully. – Sam Sep 20 '19 at 00:35
  • I had a similar problem yesterday and what we did you resolve the issue was to enable Allow Inprocesses in the Oracle providers properties. – Mark Kram Sep 20 '19 at 01:00
  • @Sam, the question asked is does the account have permissions to **create** files on the share, not just read them. I wouldn't expect that to be required myself. – Dan Guzman Sep 21 '19 at 10:18
  • @MarkKram, if out-of-process solved the problem for you, I suggest you elaborate on that solution with an answer. – Dan Guzman Sep 21 '19 at 10:20
  • @MarkKram Allow in-process is checked. Without that enabled, even the local file doesn't work. – Sam Sep 22 '19 at 18:37
  • If it does not work from the network a simple workaround would be to copy it to windows temp location (which should be on the local drive) and read it from there. – tukan Sep 24 '19 at 12:01
  • You accessing it with a UNC path like \\server\share\directory\ and not a drive letter? Normally services don't see drive mappings and Oracle runs as a service. – Brian Sep 25 '19 at 15:00

2 Answers2

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
Sreeram Nair
  • 2,369
  • 12
  • 27
  • Thank you for this advice, I will check it out. We are connecting to 10g - for the next year or so, then all dbs will be on 12. Many of the same troubleshooting tips from a MSFT article I followed, but I will double-check. – Sam Oct 02 '19 at 16:11
0

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 :-)).

o0x258
  • 336
  • 1
  • 10