On-Premises Data Gateway not using new location for TNS_ADMIN

[DKHUghes55]

Hi Community,

I have an issue with one of my On-Premises Data gateways.  We have a requirement to keep a couple of specific Oracle database entries in the TNSNames.ora file that are not part of the company standard TNSNames.ora file.

So, we created a separate directory location to store our version of the TNSNames.ora file.  In this directory we also have the SQLNet.ora file.

I then changed the TNS_ADMIN environment varialbe (system Level) and executed a setx command  to also set the TNS_ADMIN to the new location.

I then opened the On-premises Data Gateway application.  Executed a restart of the Gateway.

When I try to create a new connection on the gateway, I get an ORA-12154 error.  When I go in to add the TNS entries to the origianl TNSNames.ora file and restart the gateway the connection works.

Is there somewhere I have forgotten to change the location?

Thanks,

DKHughes55

[Poojara_D12 · Accepted solution]

Hi @DKHUghes55 

1. Confirm TNS_ADMIN Path: Ensure TNS_ADMIN is set system-wide to your custom directory and verify using echo %TNS_ADMIN%.

2. Restart System: Reboot to apply changes fully, including for the On-Premises Data Gateway service.

3. Check Gateway Service: Verify the Gateway service inherits the new environment variable:

   • Open services.msc, restart the On-Premises Data Gateway service, or update its settings to ensure it reads the updated TNS_ADMIN.

4. Oracle Registry Check: Verify TNS_ADMIN in the registry (HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE) and update it if needed.

5. Permissions: Ensure the custom TNSNames.ora and SQLNet.ora files have proper read permissions for the Gateway service.

6. Test Oracle Connection: Use tools like SQL*Plus to test connectivity via your custom TNS alias before testing in the Gateway.

If it still fails, the Gateway might still reference the default configuration. Recheck logs and paths for clues.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[DKHUghes55]

Hi Poojara_D12,

Rebooting the server fixed the issue.   Thank you for the detailed list to go through.

DKHughes55

[lbendlin]

check the PATH system variable on the gateway VMs.  Most likely you messed up with multiple Oracle client installs.