The search order for TNSNAMES.ORA

When reading the headline, you might think the answer is more or less trivial. Or at least it is stated in the docs. But in fact, it isn’t. Let’s have a quick glance at the docs. The “19c Net Services Administrator’s Guide” states the following:

1. The directory specified by the TNS_ADMIN environment variable.
2. If the TNS_ADMIN environment variable is not set or the file is not found in the TNS_ADMIN directory, then Oracle Net checks for the file in the ORACLE_HOME/network/admin directory. For a read-only Oracle home, Oracle Net checks the ORACLE_BASE_HOME/network/admin directory.

So let’s build a testcase to verify that. I created a CDB that hosts a PDB for each type directory, that may hold a tnsnames.ora file. As far as I know, it can reside in the current directory. in a directory pointed to by TNS_ADMIN and in ORACLE_HOME/network/admin. The TNS_ADMIN can be defined as a environment variable or, on Windows, inside the Registry.

SQL> select name from v$database;

NAME
---------
MPMCDB1

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBA                           READ WRITE NO
         4 TNSLOCAL                       READ WRITE NO
         5 TNSREGISTRY                    READ WRITE NO
         6 TNSADMIN                       READ WRITE NO
         7 TNSORAHOME                     READ WRITE NO

For the test, I will create a tnsnames.ora in each of the mentioned locations with one alias in it. The only difference will be the service, the alias refers to.

Let’s check these files and settings first.

C:\Users\marco.mischke>type tnsnames.ora
TNSTEST=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = odax5ha0.support.robotron.de)(PORT = 1521))
   (CONNECT_DATA = (SERVICE_NAME = tnslocal.support.robotron.de))
 )


C:\Users\marco.mischke>reg query HKLM\SOFTWARE\Oracle\KEY_OraClient19Home1 /v TNS_ADMIN

HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OraClient19Home1
    TNS_ADMIN    REG_SZ    C:\TEMP
    
C:\Users\marco.mischke>type c:\Temp\tnsnames.ora
TNSTEST=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = odax5ha0.support.robotron.de)(PORT = 1521))
   (CONNECT_DATA = (SERVICE_NAME = tnsregistry.support.robotron.de))
 )

C:\Users\marco.mischke>set TNS_ADMIN=c:\Users\marco.mischke\TNSADMIN

C:\Users\marco.mischke>type c:\Users\marco.mischke\TNSADMIN\tnsnames.ora
TNSTEST=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = odax5ha0.support.robotron.de)(PORT = 1521))
   (CONNECT_DATA = (SERVICE_NAME = tnsadmin.support.robotron.de))
 )

C:\Users\marco.mischke>set TNS
TNS_ADMIN=c:\Users\marco.mischke\TNSADMIN

C:\Users\marco.mischke>type C:\oracle\client19\network\admin\tnsnames.ora
TNSTEST=
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = odax5ha0.support.robotron.de)(PORT = 1521))
   (CONNECT_DATA = (SERVICE_NAME = tnsorahome.support.robotron.de))
 )

Time for the first test. I simply start SQL*Plus using the one alias defined in all four tnsnames.ora files.

C:\Users\marco.mischke>sqlplus pdbadmin/********@tnstest

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 10 10:47:05 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Wed May 10 2023 09:26:24 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> show con_name

CON_NAME
------------------------------
TNSLOCAL

Obviously my connection gets routed to the “TNSLOCAL” PDB which means, the tnsnames.ora in the current working directory was picked up.

Now I remove this file and try another connection.

C:\Users\marco.mischke>del tnsnames.ora

C:\Users\marco.mischke>sqlplus pdbadmin/********@tnstest

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 10 10:48:34 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> show con_name

CON_NAME
------------------------------
TNSADMIN

Ok, now the tnsnames.ora from the directory pointed to by the TNS_ADMIN environment variable was used, because I am now connected to the “TNSADMI” PDB.

Next step is to unset TNS_ADMIN or, as an alternative, remove that tnsnames.ora file too and try again.

C:\Users\marco.mischke>set TNS_ADMIN=

C:\Users\marco.mischke>set TNS
Die Umgebungsvariable "TNS" ist nicht definiert.

C:\Users\marco.mischke>sqlplus pdbadmin/********@tnstest

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 10 10:49:32 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> show con_name

CON_NAME
------------------------------
TNSREGISTRY

Last but not least, I remove the Registry key for TNS_ADMIN.

C:\Users\marco.mischke>reg delete HKLM\SOFTWARE\Oracle\KEY_OraClient19Home1 /v TNS_ADMIN
Registrierungswert TNS_ADMIN löschen (Ja/Nein)? Ja

C:\Users\marco.mischke>reg query HKLM\SOFTWARE\Oracle\KEY_OraClient19Home1 /v TNS_ADMIN


FEHLER: Der angegebene Registrierungsschlüssel bzw. Wert wurde nicht gefunden.

C:\Users\marco.mischke>sqlplus pdbadmin/********@tnstest

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 10 10:51:55 2023
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> show con_name

CON_NAME
------------------------------
TNSORAHOME

So finally I ended up in the “TNSORAHOME” PDB meaning, SQL*plus used the tnsnames.ora in the ORACLE_HOME.

These four tests reveal the search order for the SQL*Net configuration files, especially tnsnames.ora:

Current working directory
Directory pointed to by TNS_ADMIN environment variable
Directory pointed to by TNS_ADMIN registry value
ORACLE_HOME/network/admin

Keep that in mind, esspecially the first point. If your connection fails or ends up in a database that you do not expect, there might be a tnsnames.ora file in your current directory which gets picked up first regardless of any other settings.

Pingback: Connecting to an Oracle Database – what could possible go wrong | Marco's DBA Blog
Darshana Suresh 10:13 am on December 8, 2023

Hi Marco,
Thanks for this article. It was very helpful. Just wanted to check if you knew of any documentation on how the order of sqlnet.ora files are checked for NTS authentication?
I was experimenting with the same order specified in this article, using SQL *Plus. Here’s what I’ve observed –
– When SQLNET.AUTHENTICATION_SERVICES is set to NONE in a sqlnet.ora file in current working directory, or the one set in the environment variable, it takes precedence over file in the registry variable location. I.e., OS authentication is not possible.
– But when SQLNET.AUTHENTICATION_SERVICES is set to NTS in sqlnet.ora file in current working directory and the the file in the location set by registry variable has it set to NONE, then the registry variable takes precedence and OS authentication is not possible.

Suppose I want to set the TNS_ADMIN location in the connection string so that the NTS authentication works the same way as SQL *Plus, how can I do this?

LikeLike
Marco Pachaly-Mischke 10:18 am on December 8, 2023

Hi,
I would expect the search order is identical. But as you describe it, it isn’t and works in a different way somehow. As of now I have no clue how Oracle interprets the SQLNET.ORA files. Maybe I’ll investigate this in the future.
Thanks for sharing your experience.
Marco

LikeLike
Martin Ba 8:12 am on March 22, 2024

From what I see, it is actually not the *files*, but the *entries* that are searched.

That is, if you have a tnsnames.ora in your CWD, and you have ALIAS_A in there, then this will be used.
But if you also have a tnsnames.ora in one of the other locations, and an ALIAS_B in one of those, but *no* ALIAS_B in the file in the cwd, then tnsping or sqlplus will *still* find ALIAS_B — it will simply search all files in a given order.

This is “only” what I see with sqlplus and tnsping — our oci/occi based tooling does not do this and seems to stop at the first file it finds.

Take this with a pinch of salt – I’m still figuring out the details.

LikeLiked by 1 person