Easy replacement of tnsnames.ora with LDAP Directory Server

The tnsnames.ora is a configuration file for Oracle database respectively Oracle Net Service Names resolution. It contains network service names that are mapped to connection descriptors for the local naming method. With the help of tnsnames.ora Oracle clients respectively the users can easily access Oracle databases. The connection descriptors provides all relevant information like host, port, service name etc.

For larger environments with multiple Oracle databases and hundreds or more clients, managing and distributing the tnsnames.ora becomes difficult. Especially when Oracle DataGuard or Oracle Real Application Cluster are added, where more complex connection description with failover or load balancing information is needed. A reliable Oracle Network Service and an up-to-date tnsnames.ora are crucial for a highly available access to Oracle databases. A manual copy of tnsnames.ora or a central NFS or Windows share usually does not meet this requirement.

The Solution Approach

The idea of using an LDAP directory to manage Oracle Network Service Names is not new. There are several official and unofficial approaches how this can be implemented:

  • Use of an Oracle Directory for the administration of Oracle Net Service Names, e.g. Oracle Internet Directory (OID) or Oracle Unified Directory (OUD). Whereby with this solution a corresponding Oracle Directory Services Plus license is required. In addition, OID is anything but lean. Ok the license situation has slightly changed see Free Oracle Unified Directory for Oracle Net Services
  • Setup of Oracle Enterprise User Security (EUS). Here, too, an Oracle Directory, i.e. OID or OUD with a corresponding license, is required. With this solution, the authentication and authorisation of the databases is also solved centrally. The setup of EUS is rather complex as it is not only to setup an LDAP server. You also have to define and implement an appropriate user and role concept. In any case, this has an impact on existing applications and use cases.
  • Oracle Network Service Names can be entered directly in MS Active Directory. The database names are then resolved via AD. However, this method requires a schema extension in MS Active Directory. This is usually not so easy to implement in larger environments.
  • Alternative LDAP directory servers like OpenLDAP or 389 Directory Server can be used. Also here a schema extension is needed. Since the LDAP servers are only used for the Oracle Net Service Names resolution this is not critical. Especially since a LDAP schema extension is standard procedure. The advantage of this method is that by using an OpenSource LDAP server the costs remain manageable, although it is not officially supported.

The following figure shows a schematic diagram of the Oracle Network Service Names resolution using an open source LDAP directory as an example.

Oracle Network Service Names Resolution

For the solution presented here, we use the open source LDAP server 389 Directory Server. This is available as an open source variant via Fedora and is also part of RedHat Enterprise Linux as RedHat Directory Server (RHDS). Whereby a corresponding subscription is necessary in order to use the RedHat Directory Server (RHDS). However, the 389 Directory Server from the Fedora project also works perfectly under Oracle Enterprise Linux 8.

The solution presented here is based on the 389 Directory Server from the Fedora project. However, the installation and configuration steps can be applied more or less 1:1 to the RedHat Directory Server (RHDS) as well. Especially since the documentation from the 389 Directory Server is usually any way based on the RedHat documentation or at least references it.

Prerequisites and Requirements

The prerequisites are straight forward. The 389 Directory Server or RedHat Directory Server (RHDS) is modest in terms of system resources. Especially as LDAP server for a few 100 Oracle Net Service Names. The documentation Red Hat Directory Server 11 Release Notes does not show any specific hardware requirements. It is recommended to provide at least 2 CPU cores plus 16GB memory for productive environments. For a simple test setup also less is enough. The base operating system is OEL 8.5 or REL 8.5.

Preparation

First of all we have to make sure, that the Fedora EPEL repository is added and the 389-ds Module is enabled.

sudo dnf -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
sudo yum -y module enable 389-ds

It is also a good idea to open a couple of local firewall ports for LDAP and LDAPS

sudo firewall-cmd --list-all --permanent --zone=public
sudo firewall-cmd --permanent --add-service=ldap --zone=public
sudo firewall-cmd --permanent --add-service=ldaps --zone=public
sudo firewall-cmd --permanent --add-port=9090/tcp --zone=public
sudo firewall-cmd --reload
sudo firewall-cmd --list-all --permanent --zone=public

Optional we pre create the dirsrv group and assign the OS user oracle to be a member of this group. This allows certain administrative activities to be performed as user oracle.

sudo groupadd --gid 520 dirsrv
cat /etc/group
sudo usermod -a -G dirsrv oracle

Install 389 Directory Server

The installation is quite simple. Since we added the Fedora EPEL repository, we only need to install the appropriate packages and dependencies for 389 Directory Server with yum.

sudo yum install 389-ds-base sscg

Configure 389 Directory Server for Oracle Net Service

Once the packages are installed, we can create a Directory Server instance. The easiest way to do this is to use a template. The template can be created directly with dscreate.

sudo dscreate create-template /tmp/oraNet.inf

The template must then be adjusted accordingly. In particular, the following values must be set:

  • full_machine_name the full qualified hostname of the LDAP directory server.
  • instance_name name of the LDAP directory server instance.
  • root_password password for the directory server root user.
  • suffix used as base DN for the directory information tree (DIT) of the directory server

Enclosed is an example to setup an instance named oraNet on ldap1.trivadislabs.com with a base DN dc=trivadislabs,dc=com:

[general]
full_machine_name = ldap1.trivadislabs.com
start = True

[slapd]
instance_name = oraNet
port = 389
root_password = Welcome1
secure_port = 636
self_sign_cert = True
self_sign_cert_valid_months = 24

[backend-userroot]
create_suffix_entry = True
suffix = dc=trivadislabs,dc=com

The directory server instance is then created as root with the command dscreate.

sudo dscreate from-file /tmp/oraNet.inf

Check if the instance is running. dscreate does not only create the instance. It also configure the corresponding start/stop scripts.

sudo dsctl --list
sudo systemctl status dirsrv@oraNet.service

So that we can later also create corresponding Oracle Net Service Names objects in the Directory Server, the schema must be extended. For this we copy the file 90orclNet.ldif into the corresponding instance directory and restart the instance.

curl -Lf https://gist.githubusercontent.com/oehrlis/49767f09c265efc9fc3a74ee16bdfd53/raw/5c71003afe0c38040d317b9a8bc12d3eef113a75/90orclNet.ldif -o /tmp/90orclNet.ldif 
sudo cp /tmp/90orclNet.ldif /etc/dirsrv/slapd-oraNet/schema/90orclNet.ldif
sudo systemctl restart dirsrv@oraNet.service

Check the status of our new Directory Server instanz.

sudo dsctl $(dsctl --list) status
sudo dsctl $(dsctl --list) healthcheck

n the following we will execute several LDAP commands. To avoid having to enter the password interactively each time, we save it temporarily in a local file. This file is passed with the parameter -y to the LDAP command. It makes sense to delete the password file afterwards.

echo "Welcome1" | tr -d '\n' >.oraNetDirectoryManager.pwd
chmod 600 .oraNetDirectoryManager.pwd

The directory server is actually ready now. However, in order to be able to register the corresponding Oracle Net Service Names. We still need to create an Oracle Context. To do this, we simply create an object cn=OracleContext with the class orclContext in the Base DN dc=trivadislabs,dc=com.

ldapadd -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: cn=OracleContext,dc=trivadislabs,dc=com
objectclass: orclContext
cn: OracleContext

EOI

Since the Oracle clients usually execute the resolution of the Oracle Net Service Names with an anonymous LDAP query respectively with an anonymous bind, the ACIs have to be adapted in a way that anonymous searches are allowed in the Oracle Context.

ldapadd -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: dc=trivadislabs,dc=com
changetype: modify
add: aci
aci: (targetattr!="userPassword||authPassword")(version 3.0; acl "Anonymous read access"; allow (read,search,compare) userdn="ldap:///anyone";)

EOI

LDAP Based Oracle Net Services

We now have an empty 389 Directory Server ready to be used for Oracle Net Service Names resolution. It’s about time to add our first entry using ldapadd. In the following example we add a new entry with the name TDB02 and its Oracle Net Description String.

ldapadd -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: cn=TDB02,cn=OracleContext,dc=trivadislabs,dc=com
objectclass: top
objectclass: orclNetService
cn: TDB02
orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19)(PORT=1521))
 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TDB02.trivadislabs.com))(UR=A))

EOI

With ldapmodify we can also modify existing entries.

ldapmodify -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd <<-EOI
dn: cn=TDB02,cn=OracleContext,dc=trivadislabs,dc=com
changetype: modify
replace: orclNetDescString
orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19)(PORT=1521))
 (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TDB02.trivadislabs.com))(UR=A))

EOI

Or search for entries using ldapsearch.

ldapsearch -h $(hostname -f) -p 389 -x -LLL -b "dc=trivadislabs,dc=com" -s sub "(&(objectclass=orclNetService)(cn=TDB0*))"

With ldapdelete the entries can be deleted afterwards.

ldapdelete -h $(hostname -f) -p 389 -x -D "cn=Directory Manager" \
-y .oraNetDirectoryManager.pwd \
cn=TDB03,cn=OracleContext,dc=trivadislabs,dc=com

Before we are able to use the LDAP based Oracle Net Service Names we have to configure Oracle Net. To do this, we need to adjust the name resolution order in $TNS_ADMIN/sqlnet.ora with the NAMES.DIRECTORY_PATH parameter and put LDAP first.

NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, EZCONNECT )

In addition, the file $TNS_ADMIN/ldap.ora must be created. In it, the LDAP server configuration must be specified as follows.

DIRECTORY_SERVERS=(ldap1.trivadislabs.com:389:636)
DEFAULT_ADMIN_CONTEXT="dc=trivadislabs,dc=com"
DIRECTORY_SERVER_TYPE=OID

Finally we can do a tnsping and check if the Oracle Net Service Name is resolved via LDAP or tnsnames.ora

oracle@db19:~/ [rdbms19] tnsping TDB02

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-MAR-2022 06:31:06

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

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TDB02.trivadislabs.com))(UR=A))
OK (10 msec)

What’s Next?

This blog post describes a simple configuration of the 389 Directory Server for Oracle Net Service. In principle, nothing stands in the way of productive use. However, it is recommended to consider 2-3 points in more detail.

  • User and role concept for the administration and management of data in LDAP.
  • Toolset for administration e.g. scripts, LDAP browser etc.
  • High available configuration of the 389 Directory Server e.g. multiple LDAP servers with an appropriate replication configuration.
  • Certificates suitable for production.
  • Development and implementation of an operating and security concept. This includes backup and restore tasks, among others.
  • License and subscription clarification, especially when using the RedHat Directory Server instead of the pure 389 Directory Server.

Conclusion

389 Directory Server, just as with OpenLDAP, it is relatively easy to create a central directory for the Oracle Net Service Names or tnsnames.ora. Within a few minutes you have built a stand-alone LDAP server. With a little more effort, configuring secure SSL certificates, extended directory information trees with different suffixes, as well as replication, etc. is also easily possible.

Based on this approach, we have already been able to successfully set up highly available LDAP directory servers with multiple suffixes respectively Oracle Network Service Domain Names within the scope of customer projects. Thus, nothing stands in the way of replacing the cumbersome manual administration of the tnsnames.ora files with a central directory. Especially if only the Oracle Network Service Names are in focus. If, in addition, the authentication and authorisation of the databases is to be set up centrally, there is no way around Oracle Enterprise User Security (EUS) or Oracle Centrally Managed Users (CMU). The advantage of the solution presented here is that it can be combined with Oracle Centrally Managed Users (CMU). I.e. authentication and authorisation is done with CMU via Active Directory Integration, while name resolution is based on the LDAP directory. All with manageable effort and without additional licensing costs. In contrast, a solution with Oracle Enterprise User Security (EUS) is somewhat more flexible, but also more complex and cost-intensive.

Links and References

The following links are useful in the context of this blog post, tnsnames.ora, Oracle Network Services and LDAP Directory Server.