Storing Oracle database connection strings in OpenLDAP – or – “Oracle Names for the masses”
Typically Oracle stores it´s database connection strings in a file called TNSNAMES.ORA. Among storing every database connection string in this file there are other different naming schemas such as EZCONNECT, Oracle Names “ONAMES” which is deprecated with >= 10g or LDAP.
I´ve seen a lot of users with a centralized tnsnames.ora file placed on a cifs share and access by every oracle client. Using such a technique with unix hosts is not straight forward (i am no friend of having cifs shares mounted on database servers) and often due to security restrictions not possible. Sometimes i have also seen the tnsnames distributed by using rsync or just copy-on-demand.
From my point of view there is a superior solution for this problem: Oracle Names.
Oracle Names as naming method
Oracle Names was introduced with Oracle 8 (if i remember correctly) and stored database connection strings in a database schema.Clients only needed a SQLNET.ORA pointing to the server running the onames service. If a client tried to resolve a database connection string the oracle client queried the oname service which replied with the current connection string. Afterwards the client connected to the database using the received connection string. Changes to connection strings were nearly instantanous. If needed connection strings could be overwritten by specifying them in the local tnsnames.ora file.
But starting with Oracle Database 10g Oracle Names was deprecated and replaced by integrating it in the Oracle Internet Directory OID. The naming resolution in the OID was ldap-beased.If users wanted to use Oracle Names from now on they had to install and license the complete middle tier stack which included OID.
Due to the high costs for ressources and licenses i tried to implement the naming resolution in another LDAP server. I had two choices: openLDAP and Microsoft Active Directory (which was the central ldap server). After reading a bit i discarded storing the objects in active directory due to users complaing about various problems. So openLDAP was left. I found an article somewhere on the net showing how to migrate the needed schema objects from OID to openLDAP. I would have posted the link if i were able to find it again. But maybe you can help me out :-)
Impementation
Implementation on the client
All you need to do is to place the following SQLNET.ORA and LDAP.ORA in the $TNS_ADMIN directory of your oracle client:
SQLNET.ORA
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES)
LDAP.ORA
DIRECTORY_SERVERS=(onames1.regner.de:389,onames2.regner.de:389) DEFAULT_ADMIN_CONTEXT="dc=regner,dc=de"
These two files are completely static. As you may note NAMES.DIRECTORY_PATH specifies where to look for database connection strings. As defined above the LDAP server is primary and TNSNAMES.ORA is secondary. If you want to overwrite a connecting string you just need to exchange the two values for the local TNSNAMES.ORA to gain preference.
The LDAP.ORA contains the comma separated names of the servers to be queried. You can list up to three servers. If one server is unreachable oracle net client will fail over to the second server after one second.
My implemtation aliases the destination servers on DNS level so that the client configuration files are completely static. If a server is migrated or replaced you just update the dns alias and everything works just like before. You do not need to touch the clients configuration files ever again!
Implementation on the server
Configuring openLDAP
Required Ressources
First of all you need an installed openLDAP server, openLDAP utilities (“ldapadd”, “ldapsearch” ships with them) and openLDAP-libraries. Most distributions include openLDAP. Red Hat Enterprise Server 5 and Oracle Enterpise Linux 5 do so.
OpenLDAP for storing oracle connection entries has a extremely small footprint. The ldap database itself is less than 10 MB in size. The ldap server itself requires less than 50 MB memory. Tuning openLDAP should not be required unless you have a few hundred/thousand entries.
Required files
The following files implement the ldap objects needed for storing the database connection entries:
- oidrdbms.schema
- alias.schema
- oidbase.schema
- oidnet.schema
They are taken entirely from the OID schema objects and a little bit converted for openLDAP. You need to place them in /etc/named/schema-oid. Due to wordpress’ file type restriction you can download the four files here in open office format and here in pdf format.
Configuring openLDAP
Add the following lines to the file slapd.conf (alter the paths if needed):
# extension for oracle names include /etc/openldap/schema-oid/oidbase.schema include /etc/openldap/schema-oid/oidnet.schema include /etc/openldap/schema-oid/oidrdbms.schema include /etc/openldap/schema-oid/alias.schema
Configure the ldap base in slapd.conf and make sure you change the password:
defaultsearchbase "dc=regner,dc=de" database bdb suffix "dc=regner,dc=de" rootdn "cn=Manager,dc=regner,dc=de" rootpw changeme
You should also set up replication right now. The following is a simple master-slave-replication. OpenLDAP supportas different types of replication. See the documentation for more information.
# Replicas of this database replica host=onames2.regner.de:389 binddn="cn=Manager,dc=regner,dc=de" bindmethod=simple credentials=changeme replogfile /var/lib/ldap/replication.log
Creating the ldap base
Put the following lines in a file named “add_base.ldif”:
dn: dc=regner,dc=de objectClass: top objectClass: dcObject objectClass: organization o: regner.de dc: regner dn: cn=OracleContext,dc=regner,dc=de objectclass: orclContext cn: OracleContext
Add them with (you will be prompted for the password):
ldapadd -c -x -D "cn=Manager,dc=regner,dc=de" -W -f add_Base.ldif
This will create the ldap base. If everything succeeded you are read to insert your first connection string.
Managing entries
Add an entry
The following sample adds a database connection string for the database named “foo”:
dn: cn=foo,cn=OracleContext,dc=regner,dc=de objectclass: top objectclass: orclNetService cn: foo orclNetDescString: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.regner.de)(PORT=1521))(CONNECT_DATA=(SID=FOO)))
It works best when putting the lines above in a file and add it with:
ldapadd -c -x -D "cn=Manager,dc=regner,dc=de" -W -f add_entry.ldif
We tested the just added entry:
Add an alias
Adding an alias requires the destination to be already inserted. In the following example we will alias “bar” pointing to “foo”:
dn: cn=bar,cn=OracleContext,dc=regner,dc=de objectclass: orclNetServiceAlias objectclass: top objectclass: alias cn: bar aliasedobjectname: cn=foo,cn=OracleContext,dc=regner,dc=de
Testing the alias – the expected result is the same connection string as “foo”:
Delete an entry
Deletion is quite simple. The following command will delete the database connection string “foo” added above:
ldapdelete -D "cn=Manager,dc=regner,dc=de" -w sparc
-x "cn=foo,cn=OracleContext,dc=regner,dc=de"
Required Resources
So looking at the ressources we need:
Pingback: Blogroll Report 30/10/2009-06/11/2009 « Coskan’s Approach to Oracle
Why I always receive the following error:
[oracle@vmoracle admin]$ tnsping pdmdb
TNS Ping Utility for Linux: Version 10.2.0.4.0 – Production on 23-NOV-2009 16:30:02
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
after i configured openldap as required.
Can you post your ldap.ora and sqlnet.ora ?
ldap.ora:
DIRECTORY_SERVERS=(10.2.51.209:389)
DEFAULT_ADMIN_CONTEXT=”dc=itfits,dc=biz”
DIRECTORY_SERVER_TYPE=OID
sqlnet.ora:
NAMES.DIRECTORY_PATH=(ldap)
TNSPING.TRACE_LEVEL=16
TNSPING.TRACE_DIRECTORY=/u01/app/oracle/network/admin
TRACE_LEVEL_CLIENT=support
TRACE_FILE_CLIENT=sqlnet.txt
TRACE_DIRECTORY_CLIENT=/u01/app/oracle/network/admin
this is the output from ldapsearch where you can see that the configuration is correct and complete:
ldapsearch -x -D “cn=Manager,dc=itfits,dc=biz” -w secret -b “cn=OracleContext,dc=itfits,dc=biz” -s sub “objectclass=*”
# extended LDIF
#
# LDAPv3
# base with scope subtree
# filter: objectclass=*
# requesting: ALL
#
# OracleContext, itfits.biz
dn: cn=OracleContext,dc=itfits,dc=biz
objectClass: orclContext
cn: OracleContext
# pdmdb, OracleContext, itfits.biz
dn: cn=pdmdb,cn=OracleContext,dc=itfits,dc=biz
objectClass: top
objectClass: orclService
cn: pdmdb
orclNetDescString: ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.51.209)(POR
T=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=PDMDB)))’
orclOracleHome: ‘/app/oracle/product/10.2.0/db_1’
orclSid: ‘PDMDB’
orclServiceType: ‘DB’
# search result
search: 2
result: 0 Success
# numResponses: 3
# numEntries: 2
yet, this is the output from a plsql script using dbms_ldap:
—————————————
ATTIBUTE_NAME: objectClass = dcObject
ATTIBUTE_NAME: objectClass = organization
ATTIBUTE_NAME: o = ITFITS Network
ATTIBUTE_NAME: dc = itfits
—————————————
ATTIBUTE_NAME: objectClass = organizationalRole
ATTIBUTE_NAME: cn = Manager
—————————————
ATTIBUTE_NAME: objectClass = orclContext
ATTIBUTE_NAME: cn = OracleContext
—————————————
ATTIBUTE_NAME: objectClass = organizationalRole
ATTIBUTE_NAME: cn = mauro
—————————————
ATTIBUTE_NAME: objectClass = top
ATTIBUTE_NAME: objectClass = orclService
ATTIBUTE_NAME: cn = pdmdb
ATTIBUTE_NAME: orclNetDescString =
‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.51.209)(PORT=1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=PDMDB)))’
ATTIBUTE_NAME: orclOracleHome = ‘/app/oracle/product/10.2.0/db_1’
ATTIBUTE_NAME: orclSid = ‘PDMDB’
ATTIBUTE_NAME: orclServiceType = ‘DB’
L_RETVAL: 0
I also include some row from the tnsping trace where you can see that the query against the ldap is unsuccessful even if it is correctly configured:
nnflrne1: Quering the directory for dn: cn=pdmdb,cn=OracleContext,dc=itfits,dc=biz
nnflqbf: entry
nnflqbf: Search: Base: cn=pdmdb,cn=OracleContext,dc=itfits,dc=biz, Scope: 0, filter: (objectclass=*)
nnflqbf: Search: Attrs[0]: objectclass
nnflqbf: exit
nnflgne: entry
nnflgne: exit
nnflfrm: entry
nnflfrm: exit
nnflrne1: exit
nnfldlc: entry
nnfldlc: exit
nnfln2a: exit
nnfgrne: Query unsuccessful, skipping to next adapter
nnfgrne: exit
nnfun2a: address for name “pdmdb” not found
HI Mauro,
strange thing. Please give me some time to analyze this further. At the moment i am short on time. Could you send me a testcase (/etc/ldap and /var/lib/ldap) by email so i can check it myself?
@Ronny Egner
could you integrate oracle security (user and role) with the one belonging to the operating system?
That is, could you replicate the same behaviour as oracle/windows group ?
Hmm…. i dont get your point here. For what reason? Whats the benefit of doing so?
the advantage of this implementation is that I can manage the access to many database or application from the ldap server.
I see. I am afraid but thats not possible because it is not configured like this.
For your name resolution problem please send me your files for vertification.
I found an article somewhere on the net showing how to migrate the needed schema objects from OID to openLDAP. I would have posted the link if i were able to find it again. But maybe you can help me out :-)
Is this the link?
http://oracle-cookies.blogspot.com/2007/01/get-tnsnamesora-from-openldap.html
It does not look like the site i found it..but it is close….
@Kumar Raju
Kumar (and Mauro, perhaps): If you used the examples from “oracle-cookies”, beware that the authentication policy in the slapd.conf may not work.
In my test setup (OpenLDAP 2.2.29 WinXP, Oracle Client 10.2), sqlnet could not authenticate with the server. Leaving the access control settings commented, tnsping worked fine for me.
So far for my €0.02,
Uwe
I have a problem with “orclNetDescName”. The Oracle netmgr will insert a string like:
000:DESCRITION_0.
Result:
tag=105 err=21 text=orclNetDescName: value #0 invalid per syntax
The ldif-file example:
orclNetDescName: 000:cn=DESCRITION_0
Any Idea to fix this problem?
Thanks
Dominik
I have a problem with “orclNetDescName”. The Oracle netmgr will insert a string like:
000:DESCRITION_0.
Result:
tag=105 err=21 text=orclNetDescName: value #0 invalid per syntax
The ldif-file example:
orclNetDescName: 000:cn=DESCRITION_0
Any Idea to fix this problem?
Thanks
Dominik
> I have a problem with “orclNetDescName”.
I dont user Oracle NetMgr to manage my LDAP entries. I wrote a small .NET tool to do so. If you want to use NetMgr you have to debug by checking what commands are executed against the LDAP server to create a testcase.
Hi Ronny,
thanks for the article. I now have a valid installation on my mac. The default installation in macos 10.6.6 did not function properly so I installed a fresh BerkelyDB 4.8.30, combined with openldap 2.4.24, after which all my problems were gone.
:-D
Would it also be possible to make authentications possible using openldap? What would be needed to get that up and running? It would be very nice to have enterprise users served by openldap …
best regards,
Ronald.
Hi Ronald,
i am afraid thats not possible. There is a product from Oracle available (“Global Names” if i remember correctly) but there is nothing you can easiliy implement on OpenLDAP.
Thanks a ton for this guide! This solved a problem I was having, where I was moving a legacy app to the amazon cloud and I needed to store the oracle connections somewhere without going crazy.
You should add a flattr button to your blog, I would totally click it. :-) Keep up the good work!
I’ve gotten my LDAP dtreciory up and running. It’s serving out the dtreciory information, and I’ve been able to login on my Linux machine. Now, I want to get logins and home directories available on my OS X machines. This is some really good information out there on getting this working. Most of what is here is cobbled together from these sources among others: Mac OS X Server Open Directory Adminstration for Snow Leopard BackupCentral’s LDAPand Austofs for Ubuntu and Snow Leopard Rajeev Karamchedu’s excellent writeup for integrating OS X and LDAP
Sorry, can´t help you with that.