TNSNAMES and Active Directory

It is highly probable you already have MS AD in your company. Probably you use a local tnsnames.ora. Apart from setting a Oracle Internet Directory or Oracle Virtual Directory, there is one more option that you may want to consider : AD.

Ok, here is a bit of a road map :

– Schema Extension :
extending the schema is irreversible and you will have to test this properly and explain why you need this (remove the need of distributing a tnsnames, central administration) to your Microsoft Admin friends. To extend the schema, use Oracle Network Configuration Assistant. The step-by-step guide is there

– Anonymous or authenticated bind
prior to 11g, you needed to allow anonymous bind on the AD server. Your Security Admin friends will probably prefer the 11g approach of setting NAMES.LDAP_AUTHENTICATE_BIND to true. If you set NAMES.LDAP_AUTHENTICATE_BIND to true, the Oracle clients will use your windows credentials to do the tnsnames resolution.

For sql developer, use Connection Type=TNS, Connect Identifier=DB01. connection type=Ldap does not work with authenticated bind

– Import the tnsnames and / or create new entries
all done with Net Manager and pretty intuitively. Except that you will use “Directory –> Export Net Service Names” to import the tnsnames in AD

– Configure the clients
sqlnet.ora
NAMES.DIRECTORY_PATH= (LDAP)
NAMES.LDAP_AUTHENTICATE_BIND=1

ldap.ora
DEFAULT_ADMIN_CONTEXT = “DC=example,DC=com”
DIRECTORY_SERVER_TYPE = AD

– test it!
tnsping first

C:\> tnsping db01

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 10-NOV-2011 14:42:16

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

Used parameter files:
C:\oracle\product\11.2.0\client_3\network\admin\sqlnet.ora

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
_DATA=(SID=DB01)))
OK (20 msec)

I wrote a simple java program to check the connection :

import java.sql.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("oci");
ods.setTNSEntryName("DB01");
ods.setUser("scott");
ods.setPassword("tiger");
ResultSet res = ods.
getConnection().
prepareCall("select 'Hello World' txt from dual").
executeQuery();
res.next();
System.out.println(res.getString("TXT"));
}
}


C:\> set PATH=C:\oracle\product\11.2.0\client_3\bin
C:\> javac -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld.java
C:\> java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar -Doracle.net.tns_admin=C:\oracle\product\11.2.0\client_3\network\admin HelloWorld
Hello World

If you get and issue with ocijdbc11, you either do not have the *ocijdbc11* driver in your PATH / LD_LIBRARY_PATH / LIBPATH or the use the wrong driver. For instance if you compile with java 32bits, you cannot use the oci 64 bit.

If you use a jdbc thin ldap resolution and have no anonymous bind, it will return an error

import java.sql.*;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
ResultSet res = DriverManager.
getConnection("jdbc:oracle:thin:@ldap://example.com:389/db01,cn=OracleContext", "scott", "tiger").
prepareCall("select 'Hello World' txt from dual").
executeQuery();
res.next();
System.out.println(res.getString("TXT"));
}
}


C:\>java -classpath .;C:\oracle\product\11.2.0\client_3\jdbc\lib\ojdbc6.jar HelloWorld
Exception in thread "main" java.sql.SQLException: I/O-Fehler: JNDI Package failure avax.naming.NamingException: [LDAP:error code 1 - 000004DC: LdapErr: DSID-0C0906DC, comment: In order to perform this operation a successful bind must be completed on the connection., data 0, v1db0 ]; remaining name 'cn=db01,cn=OracleContext'
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at HelloWorld.main(HelloWorld.java:5)

As the error message says, the ldap server requires a bind

Let’s try to bind


import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
public class HelloWorld {
public static void main(String[] args) throws SQLException {
OracleDataSource ods = new OracleDataSource();
ods.setDriverType("thin");
Properties prop = new Properties();
prop.put("java.naming.security.authentication", "simple");
prop.put("java.naming.security.principal","CN=Laurent Schneider,CN=Users,DC=example,DC=com");
prop.put("java.naming.security.credentials", "my_ad_pw");

ods.setConnectionProperties(prop);

ods.setURL("jdbc:oracle:thin:@ldap://w2k8.local:389/db01,cn=OracleContext,DC=example,DC=com");
ods.setUser("scott");
ods.setPassword("tiger");
ResultSet res = ods.
getConnection().
prepareCall("select 'Hello World' txt from dual").
executeQuery();
res.next();
System.out.println(res.getString("TXT"));
}
}

This works!

7 thoughts on “TNSNAMES and Active Directory

  1. Sam

    Hi Laurent, information is very useful! One question – if I have following setup
    Lets assume I have *ux Oracle RDBMS server. For simplicity it is 11gR2 and above.
    If I develop java code such as above with following twist

    user enters jsmith (his AD name is (bluebell\jsmith)
    he enters his AD password

    Can I then invoke the java class thru externally (or compiled as java inside db)

    – Main issue is – do I have access to jsmith’s password that I can pass to the utility?
    such as following?

    ods.setPassword(“jsmithtiger”);

    Then if the program succeeds, he has valid user id and his login continues in schema jsmith on the database. I kinda think Oracle wont give access to user password…

    Other less preferred option is let jsmith have local db account and password
    but have java program check if jsmith has active AD account (thus using some admin user id in AD which can check using some AD command if jsmith is active – not sure if java can call such)

    This will at least help one thing – if user account is deactivated on AD, it will not work on Oracle.

    Any thoughts?

    Thanks a lot!
    /S/

  2. Pingback: Configure your Oracle Net Client for AD LDAP Signing | Laurent Schneider

  3. Peter

    Thank you it worked wonderful even 5 years later with Oracle 12c and windows 2012.

  4. Reshma

    Hi Laurent,

    We have edited sqlnet.ora file and erased the word LDAP from it. We did this as our vendor has confirmed we do not need LDAP for database connection. Even then when we erase LDAP from sqlnet.ora file on client side we get connection error. What could we be doing wrong please? How do we resolve the issue?

    Thanks.

Comments are closed.