When you manage a lot of Oracle clients, it can be difficult to manage as much tnsnames.ora files as you have Oracle clients.

In that case, it can be useful to configure solutions to centralize only one tnsnames.ora. Another solution is to use a Microsoft Active Directory to store your TNS Entries (This solution is for computers running on MS Windows).

The job is done in two steps :

• First one is to configure Active Directory (AD) server
• Second one is to configure your Oracle clients to query the AD server

In my lab, I have many boxes:

• a MS Windows 2008 R2 Server (ok ok it’s an old box … but it works fine and I assume it will work fine on a 2012 R2 server). This server acts as a controller domain for the domain example.com. The server name is windows1.example.com. It hosts the Active Directory for the domain example.com and a DNS server.
• a MS Windows where an Oracle Client is installed. (Don’t try to do this configuration on a linux box … it’s not working)

Configure AD to store Oracle TNS entries.

To do that, you first have to install an Oracle Client on the server. I won’t describe how to do that … you’re smart enough to do it yourself :). You just have to install the administrative Client.

Then, create a work directory, in my server I did that in C:\AD.  And copy all the files from $ORACLE_HOME/ldap/schema/ad to this work directory.

PS C:\> md AD
PS C:\> copy C:\app\product\11.2.0\dbhome_1\ldap\schema\ad\* C:\AD
PS C:\> dir C:\AD


    Directory: C:\AD


Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---        09/11/2004     16:44        470 adContextCreate.lst
-a---        09/11/2004     16:44       2122 adContextCreateCommon.sbs
-a---        01/08/2001     04:16        591 adContextUpgradeFrom81600.lst
-a---        01/08/2001     04:17        407 adContextUpgradeFrom81600Common.sbs
-a---        23/05/2001     17:13        676 adDisplaySpecifiersCreate.lst
-a---        23/05/2001     17:13       5106 adDisplaySpecifiers_de.sbs
-a---        23/05/2001     17:13       5163 adDisplaySpecifiers_es.sbs
-a---        23/05/2001     17:13       5385 adDisplaySpecifiers_fr.sbs
-a---        23/05/2001     17:13       5092 adDisplaySpecifiers_it.sbs
-a---        23/05/2001     17:13       5562 adDisplaySpecifiers_ja.sbs
-a---        23/05/2001     17:13       5406 adDisplaySpecifiers_ko.sbs
-a---        23/05/2001     17:13      76035 adDisplaySpecifiers_other.sbs
-a---        23/05/2001     17:13       5453 adDisplaySpecifiers_pt_BR.sbs
-a---        23/05/2001     17:13       5069 adDisplaySpecifiers_us.sbs
-a---        23/05/2001     17:13       5225 adDisplaySpecifiers_zh_CN.sbs
-a---        01/08/2001     04:16        576 adSchemaCreate.lst
-a---        04/08/2003     17:51        219 adSchemaCreateAux.lst
-a---        13/11/2001     16:10        224 adSchemaCreateAux.sbs
-a---        09/11/2004     16:44       5445 adSchemaCreateBase.sbs
-a---        01/08/2001     04:17      11925 adSchemaCreateNet.sbs
-a---        01/08/2001     04:17       7462 adSchemaCreateRDBMS.sbs
-a---        01/08/2001     04:16        570 adSchemaUpgradeFrom81600.lst
-a---        01/08/2001     04:17        585 adSchemaUpgradeFrom81600Base.sbs
-a---        01/08/2001     04:17        509 adSchemaUpgradeFrom81600Net.sbs
-a---        01/08/2001     04:17        690 adSchemaUpgradeFrom81600RDBMS.sbs

Next, we will replace some values into some of these files. But before we need to note several DN (distinguished name).  :

• DN of the root container. Usually this DN represents the domain where we will create what is called the “Oracle Context” (see below). In my case, my domain’s DN is : “DC=example,DC=com”.
• DN of the Users branch in the AD container. In my case, it’s “CN=Users,DC=example,DC=com”.
• DN of the user you are logged in. Basically, I will do that with the domain administrator, and its DN is: “CN=Administrator,CN=Users,DC=example,DC=com”
• DN of the Oracle Context, in my case its DN is: “CN=OracleContext,DC=example,DC=com”. It’s in this Oracle Context that TNS Entries will be created.

I defined all these values in a set of PowerShell variables:

PS C:\> cd AD
PS C:\AD> $rootCont="DC=example,DC=com"
PS C:\AD> $usersBranch="CN=Users,DC=example,DC=com"
PS C:\AD> $userLogged="CN=Administrator,CN=Users,DC=example,DC=com"
PS C:\AD> $oracleContext="CN=OracleContext,DC=example,DC=com"

Then, all the files mentioned below will be “SEDed” to replace patterns with the correct values in new LDIF files (It has been done with Powershell, but you can do this with the tool you want: sed/cygwin, notepad etc).

PS C:\AD> cat adSchemaCreateBase.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaCreateBase.ldif -Encoding UTF8
PS C:\AD> cat adSchemaCreateNet.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaCreateNet.ldif -Encoding UTF8
PS C:\AD> cat adSchemaCreateRDBMS.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaCreateRDBMS.ldif -Encoding UTF8
PS C:\AD> cat adSchemaUpgradeFrom81600BASE.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaUpgradeFrom81600BASE.ldif -Encoding UTF8
PS C:\AD> cat adSchemaUpgradeFrom81600NET.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaUpgradeFrom81600NET.ldif -Encoding UTF8
PS C:\AD> cat adSchemaUpgradeFrom81600RDBMS.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adSchemaUpgradeFrom81600RDBMS.ldif -Encoding UTF8
PS C:\AD> cat adDisplaySpecifiers_us.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adDisplaySpecifiers_us.ldif -Encoding UTF8
PS C:\AD> cat adDisplaySpecifiers_other.sbs | %{$_ -replace “%s_AdDomainDN%",$rootCont} | Out-File adDisplaySpecifiers_other.ldif -Encoding UTF8

PS C:\AD> cat adContextCreateCommon.sbs | %{$_ -replace “%s_OracleContextDN%" ,$oracleContext} `
>> | %{$_ -replace “%s_AdUsersDomainDN%", $usersBranch} `
>> | %{$_ -replace “%s_CurrentUserDN%", $userLogged} | Out-File adContextCreateCommon.ldif -Encoding UTF8
>>
PS C:\AD>

PS C:\AD> cat adContextUpgradeFrom81600Common.sbs | %{$_ -replace “%s_OracleContextDN%",$oracleContext} | Out-File adContextUpgradeFrom81600Common.ldif -Encoding UTF8

Please note that if your AD is installed in another supported language, you have to modify the DisplaySpecifier file related to the installation language (for example,  adDisplaySpecifiers_fr.sbs if it’s installed in French).

After that, a simple bunch of ldapmodify commands, and the entries for OracleContext will be created in the AD:

PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaCreateBase.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaCreateNet.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaCreateRDBMS.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaUpgradeFrom81600BASE.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaUpgradeFrom81600NET.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adSchemaUpgradeFrom81600RDBMS.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adDisplaySpecifiers_us.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adDisplaySpecifiers_other.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adContextCreateCommon.ldif
PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\adContextUpgradeFrom81600Common.ldif

During execution, I had this kind off output … you can safely ignore this issue (probably due to powershell encoding which is made by default in UTF-8-BOM (original file used UTF-8 encoding):

ldapmodify.exe: no attributes to change or add (entry ´╗┐#)

Once done, you can check you have the correct number of objects in your directory. I did this check with a basic ldapsearch command:

PS C:\AD> ldapsearch -D "cn=Administrator,cn=users,dc=example,dc=com" -b "CN=Schema,CN=Configuration,dc=example,dc=com" -w "YOUR_PASSWORD" "(&(objectClass=attributeSchema)(CN=orcl*))" DN | Measure-Object -line

 Lines    Words    Characters    Property
 -----    -----    ----------    --------
    37    


PS C:\AD> ldapsearch -D "cn=Administrator,cn=users,dc=example,dc=com" -b "CN=Schema,CN=Configuration,dc=example,dc=com" -w "YOUR_PASSWORD" "(&(objectClass=classSchema)(CN=orcl*))" DN | Measure-Object -line

 Lines    Words    Characters    Property
 -----    -----    ----------    --------
    14    
          

If the AD MMC users and computers plugin, a new branch appeared: “OracleContext” and three groups related to security management of the Oracle Context:

Then, If you want to add some entries, you have to create an ldap.ora file first, then create entries. TO finish this configuration steps, we have to grant permissions on specific branches to allow anonymous access on TNS entries. This is mandatory because Oracle client doesn’t bind the directory, and it doesn’t have to logon with a specific user to read the TNS Entry.

LDAP.ORA

This file has to be created in the $OH/network/admin. There must be 3 three parameters for the directory server name and port, the durectory server type, and the DN of the parent branch which contains the Oracle Context.
Here’s the content of my ldap.ora file (refer to Oracle documentation : Net Services Reference for more information).

DIRECTORY_SERVERS=windows1.example.com:389
DIRECTORY_SERVER_TYPE=AD
DEFAULT_ADMIN_CONTEXT="DC=example,DC=com"

CREATE TNS ENTRIES IN ACTIVE DIRECTORY

The easiest way to do that is to use Net Manager and add entry in the directory branch.

But, I prefer to import entries from a ldif file. To do that, you have to create a LDIF file which contains all the necessary properties for the TNS entry. Below, a sample of what it can contain:

PS C:\AD> cat orcl.ldif
dn: CN=orcl,CN=OracleContext,DC=example,DC=com
changetype: add
objectClass: top
objectClass: orclNetService
orclNetDescString: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=windows1.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

dn: CN=coucou,CN=OracleContext,DC=example,DC=com
changetype: add
objectClass: top
objectClass: orclNetService
orclNetDescString:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.15)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

And to finish, it’s easy to add them to the directory, the same way we import our ldif during AD configuration:

PS C:\AD> ldapmodify -c -D "cn=Administrator,cn=users,dc=example,dc=com" -w "YOUR_PASSWORD" -f C:\AD\orcl.ldif

adding new entry CN=orcl,CN=OracleContext,DC=example,DC=com

adding new entry CN=coucou,CN=OracleContext,DC=example,DC=com

SPECIFIC PERMISSIONS

Grant the permission “anonymous logon” on the entry we’ve created.

C:\> dsacls "CN=orcl,CN=OracleContext,DC=example,DC=com" /G "anonymous logon":GR
C:\> dsacls "CN=coucou,CN=OracleContext,DC=example,DC=com" /G "anonymous logon":GR

Client configuration

The client configuration is very easy, there’s only two lines to configure in SQLNET.ora file.

NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

A call to tnsping will show you that trying to resolve the given alias will be done through your ldap configuration:

C:\>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 - Production on 12-JAN-2017 15:41:49

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\app\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=windows1.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl
OK (0 msec)

Another way to verify it resolves the alias through LDAP is to enable SQLNET client tracing by addind these lines in the sqlnet.ora file:

TRACE_LEVEL_CLIENT=ADMIN
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
TRACE_DIRECTORY_CLIENT=c:\temp\client_trace
LOG_DIRECTORY_CLIENT=c:\temp\client_trace
DIAG_ADR_ENABLED=OFF

In the trace file, we see this information that prooves LDAP usage to resolve names:

[12-JAN-2017 15:45:21:752] nnflilc:  Opening sync conn to windows1.example.com:389
[12-JAN-2017 15:45:21:752] nnflalc: entry
[12-JAN-2017 15:45:21:752] nnflalc: native bind CN=Administrator,CN=Users,DC=example,DC=com returns 0
[12-JAN-2017 15:45:21:752] nnflalc: bind CN=Administrator,CN=Users,DC=example,DC=com returns 0x0
.../...
[12-JAN-2017 15:45:21:752] nnflrne1: Quering the directory for dn: cn=orcl,cn=OracleContext,DC=example,DC=com
[12-JAN-2017 15:45:21:752] nnflqbf: entry
[12-JAN-2017 15:45:21:752] nnflqbf: Search: Attrs[0]: objectclass
[12-JAN-2017 15:45:21:752] nnflqbf: Search:  Base: cn=orcl,cn=OracleContext,DC=example,DC=com; Scope: 0; filter: (objectclass=*) returns 0x0
[12-JAN-2017 15:45:21:752] nnflqbf: exit
[12-JAN-2017 15:45:21:752] nnflgne: entry
[12-JAN-2017 15:45:21:752] nnflgne:   DN : cn=orcl,cn=OracleContext,DC=example,DC=com
[12-JAN-2017 15:45:21:752] nnflgne: exit
.../...
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NI global area is now 1
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NL global area is now 1
[12-JAN-2017 15:45:21:752] nigini: entry
[12-JAN-2017 15:45:21:752] nigini: Count in the NL global area is now 2
[12-JAN-2017 15:45:21:752] nigini: Count in NI gbl area now: 2
[12-JAN-2017 15:45:21:752] nigini: exit
[12-JAN-2017 15:45:21:752] niqname: Hst is already an NVstring.
[12-JAN-2017 15:45:21:752] niqname: Inserting CID.
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NI global area is now 1
[12-JAN-2017 15:45:21:752] nigtrm: Count in the NL global area is now 1
[12-JAN-2017 15:45:21:752] nigini: entry
[12-JAN-2017 15:45:21:752] nigini: Count in the NL global area is now 2
[12-JAN-2017 15:45:21:752] nigini: Count in NI gbl area now: 2
[12-JAN-2017 15:45:21:752] nigini: exit
[12-JAN-2017 15:45:21:752] niqname: Hst is already an NVstring.
[12-JAN-2017 15:45:21:752] niqname: Inserting CID.
[12-JAN-2017 15:45:21:752] niotns: entry
[12-JAN-2017 15:45:21:752] niotns: niotns: setting up interrupt handler...
[12-JAN-2017 15:45:21:752] niotns: Not trying to enable dead connection detection.
[12-JAN-2017 15:45:21:752] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=windows1.example.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=C:\app\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=clientWin)(USER=Administrator))))
[12-JAN-2017 15:45:21:752] nsgettrans_bystring: entry
[12-JAN-2017 15:45:21:752] nsgettrans_bystring: exit
[12-JAN-2017 15:45:21:752] nsmal: 280 bytes at 0x6baaf0
[12-JAN-2017 15:45:21:752] nscall: connecting...