Home > Oracle in general > Storing Oracle database connection strings in OpenLDAP instead of OID

Storing Oracle database connection strings in OpenLDAP instead of OID

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:

ldap1

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”:

ldap2

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:

Categories: Oracle in general Tags:
  1. mauro bagazzi
    November 23rd, 2009 at 19:05 | #1

    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.

  2. Ronny Egner
    November 26th, 2009 at 10:23 | #2

    Can you post your ldap.ora and sqlnet.ora ?

  3. mauro bagazzi
    November 26th, 2009 at 12:24 | #3

    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

  4. mauro bagazzi
    November 26th, 2009 at 12:34 | #4

    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

  5. Ronny Egner
    November 27th, 2009 at 09:56 | #5

    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?

  6. mauro bagazzi
    November 27th, 2009 at 13:22 | #6

    @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 ?

  7. Ronny Egner
    November 27th, 2009 at 14:07 | #7

    Hmm…. i dont get your point here. For what reason? Whats the benefit of doing so?

  8. mauro bagazzi
    November 27th, 2009 at 16:38 | #8

    the advantage of this implementation is that I can manage the access to many database or application from the ldap server.

  9. Ronny Egner
    November 30th, 2009 at 10:03 | #9

    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.

  10. Kumar Raju
    January 20th, 2010 at 22:44 | #10

    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

  11. Ronny Egner
    January 26th, 2010 at 17:28 | #11

    It does not look like the site i found it..but it is close….

  12. April 8th, 2010 at 16:57 | #12

    @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

  13. Anonymous
    June 23rd, 2010 at 08:23 | #13

    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

  14. Dominik Wieczorek
    June 23rd, 2010 at 08:26 | #14

    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

  15. Ronny Egner
    June 23rd, 2010 at 09:34 | #15

    > 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.

  16. February 15th, 2011 at 00:07 | #16

    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.

  17. Ronny Egner
    April 11th, 2011 at 10:52 | #17

    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.

  18. January 5th, 2012 at 19:51 | #18

    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!

  1. November 10th, 2009 at 20:17 | #1