Categories
Database Link LDAP Oracle

How to create an Oracle Database Link using LDAP authentication

Oracle Database Link
Oracle Database Link

While it is not possible to create a database link using LDAP credentials, it is possible to lookup the TNS details through LDAP, which will allow you to create a database link using the TNS entry. Firstly, we need to successfully connect locally to the database using our known LDAP credentials e.g. below:

Database Service: TEST_DB_SERVICE
Database User: DBUSER
Database Password: SECRETPASSWORD
Directory Servers: (OID.GLB.IN.SAMPLEDOMAIN.COM.AU::3131)
Default Admin Context: “dc=in,dc=SampleDomain,dc=com,dc=au”
Directory Server Type: OID

To do this we need to configure our computer.

1. Configure the Oracle Client files in the $ORACLE_HOME/network/admin directory.

ldap.ora: (create new file)
DIRECTORY_SERVERS = (OID.GLB.IN.SAMPLEDOMAIN.COM.AU::3131)
DEFAULT_ADMIN_CONTEXT = "dc=in,dc=SampleDomain,dc=com,dc=au"
DIRECTORY_SERVER_TYPE = OID

Note: the double-colon before the port number (“::3131”) is not a typographic error. It is required syntax for the Secure LDAP protocol.
sqlnet.ora: (put this line first in the file)
NAMES.DIRECTORY_PATH = (LDAP,TNSNAMES)

2. Check to see if you can log in using the supplied schema, password and connection.

Here’s an example of logging in as DBUSER with SQL*Plus and the provided password:
sqlplus DBUSER/SECRETPASSWORD@TEST_DB_SERVICE

3. If you can successfully connect then open up a command prompt window, and execute the command tnsping TEST_DB_SERVICE to determine the TNS entry that we require:

C:\work\source\svn\dmt>tnsping TEST_DB_SERVICE
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 11-FEB-2
015 11:02:06

Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ssdb0401
n01-oravip.in.sampledomain.com.au)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = ss
db0401n02-oravip.in.sampledomain.com.au)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DA
TA = (SERVER = DEDICATED) (SERVICE_NAME = DF0014D_A_S_O_01.in.sampledomain.com.au)))
OK (90 msec)

4. Now we got everything we need to create the database link:

CREATE DATABASE LINK "TEST_APP"
CONNECT TO "DBUSER" IDENTIFIED BY "SECRETPASSWORD"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ssdb0401n01-oravip.in.sampledomain.com.au)(
PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = ssdb0401n02-oravip.in.sampledomain.com.au)(PORT = 1521))(LOAD_BALANCE = yes)
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DF0014D_A_S_O_01.in.sampledomain.com.au)))'
/