poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation.

You probably were discouraged by the long long route to kerberos, where the bugs are fixed in, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that won’t be welcome by your sysadmins / winadmins.

Okay, to partly cover the password expiration issue, you could check in a profile function that the password is the one from AD.

Firstly, without SSL

(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
  sess := DBMS_LDAP.init(
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
  rc := DBMS_LDAP.unbind_s(sess);
    rc := DBMS_LDAP.unbind_s(sess);

alter user lsc profile AD;

When the password expires, the user must change it to its AD Password.

If I try with a dummy password, the profile will reject this

SQL> conn lsc/pw1
ORA-28001: the password has expired

Changing password for lsc
New password:anypassword
Retype new password:anypassword
ORA-28003: password verification for 
  the specified password failed
ORA-31202: DBMS_LDAP: LDAP client/server 
  error: Invalid credentials. 
  80090308: LdapErr: DSID-0C0903A9, 
  comment: AcceptSecurityContext error, 
    data 52e, v1db1
Password unchanged
Warning: You are no longer connected to ORACLE.

I need to enter my Windows password

SQL> conn lsc/pw1
ORA-28001: the password has expired

Changing password for lsc
New password: mywindowspassword
Retype new password: mywindowspassword
Password changed

Secondly, with SSL.

Maybe simple bind without SSL is not possible (check http://support.microsoft.com/kb/935834). And for sure it is better to not send unencrypted plain text password over the network.

Create a wallet with password with the ROOT Certification Authority that signed your AD. You probably could download this in your trusted root certification authorities in Internet Explorer.

Internet Explorer – Tools – Internet Options – Content – Certificates – Trusted root.

Then you create a ewallet.p12 with orapki. No need for user certificate and no need for single-sign-on. Only import the trusted root (and intermediaries if applicable).

Here is the modified code

(username varchar2,
 password varchar2,
 old_password varchar2)
RETURN boolean IS
  sess raw(32);
  rc number;
  sess := DBMS_LDAP.init(
  rc := DBMS_LDAP.open_ssl(
    sess, 'file:/etc/wallet/MSAD', 
    'welcome1', 2);
  rc := DBMS_LDAP.simple_bind_s(
    sess, username||'@example.com', 
  rc := DBMS_LDAP.unbind_s(sess);
    rc := DBMS_LDAP.unbind_s(sess);

If you get SSL Handshake, be prepared, it could be anything! Check your wallet, your certificate, your permission, your wallet password.

One step further could be to expire users as soon as they change their password in AD or when they expire there.

For instance with powershell goodies for active directory

PS> (Get-ADuser lsc -properties PasswordLastSet).PasswordLastSet

Montag, 6. Oktober 2014 08:18:23

PS> (Get-ADuser king -properties AccountExpirationDate).AccountExpirationDate

Mittwoch, 16. Juli 2014 06:00:00

And in the database

SQL> SELECT ptime FROM sys.user$ 
  WHERE name ='LSC';


If PTIME is less than PasswordLastSet or if AccountExpirationDate is not null, expire the account.

In conclusion : if you do not want to use Kerberos, nor Oracle “OctetString” Virtual Directory ovid nor Oracle Internet directory oid, this workaround may help to increase your security by addressing the “shared” and “expired” accounts problematic

There an additional hidden benefit. You could set up a self-service password reset function and send a generated expired password per mail, that the user won’t be able to change without its AD password

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


– test it!
tnsping first

C:\> tnsping db01

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

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

Used parameter files:

Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=srv01)(Port=1521)))(CONNECT
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();
    ResultSet res = ods.
        prepareCall("select 'Hello World' txt from dual").

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

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.<init>(PhysicalConnection.java:536)
        at oracle.jdbc.driver.T4CConnection.<init>(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();
    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");

    ResultSet res = ods.
        prepareCall("select 'Hello World' txt from dual").

This works!

installing OID Preview 1

Download oracle-oid-
Download oracle-xe-univ-

Install the rpm
# rpm -i oracle-*.i386.rpm

In SLES 10, there is no /bin/cut, let’s create a link as root to avoid a mistake when running config-oid.sh
# ln -s /usr/bin/cut /bin/cut

Run the configure script as root
# /etc/init.d/oracle-oid configure
That’s all folks! It created an Oracle XE 10gR2 database, and configured a running database. Excellent!

LDAP Server is running and configured.

$ ldapsearch  cn=orcladmin dn
cn=orcladmin, cn=Users, dc=com

There is a nice video to run on linux : oracleauthenticationservices_demo.vvl
Save the file, set the display, then

$ chmod +x oracleauthenticationservices_demo.vvl
$ ./oracleauthenticationservices_demo.vvl

It shows also how to use Oracle LDAP Server OID to identify your Linux users with the preview of Oracle Authentication Service

Configure OID with SSL

First you need to install OID. Check the Installation Guide, the Doc and download the Software. If you do not need the dbconsole, stop it (emctl stop dbconsole) and remove the oracle_home/hostname_sid directory

Once you have a running OID, test it with ldapsearch. For this workshop, I use two servers and two usernames. Having the client and the server sharing the same wallet is not a good idea. If you have an Oracle Database running as user oracle, prefer using a different user for OID installation. By installing OID, I specified the following staticports.ini (missing from CD, bug 5936042) :

Oracle HTTP Server port  = 44000
Oracle HTTP Server Listen port  = 44001
Oracle HTTP Server SSL port  = 44002
Oracle HTTP Server Listen (SSL) port  = 44003
Oracle HTTP Server Diagnostic port  = 44004
Java Object Cache port  = 44005
DCM Discovery port  = 44006
Oracle Notification Server Request port  = 44007
Oracle Notification Server Local port  = 44008
Oracle Notification Server Remote port  = 44009
Application Server Control port  = 44010
Application Server Control RMI port  = 44011
Oracle Management Agent port  = 44012
Log Loader port  = 44013
ASG port  = 44014
Oracle Internet Directory port  = 44015
Oracle Internet Directory (SSL) port  = 44016
Oracle Certificate Authority SSL Server 
    Authentication port  = 44017
Oracle Certificate Authority SSL Mutual
    Authentication port  = 44018

Ok, let’s do the search from another server

lsc@dbserver01 $ ldapsearch -h oidserver01 -p 44015 -z 1
orclreplicaid=oidserver01_oid1014,cn=replication configuration
ldap_search: Sizelimit exceeded

Now let’s try with SSL. First, with no authentication (-U 1).

lsc@dbserver01 $ ldapsearch -h oidserver01 -p 44016 -z 1 -U 1
orclreplicaid=oidserver01_oid1014,cn=replication configuration
ldap_search: Sizelimit exceeded

Fine. Let’s create the wallets. You need a wallet for your client (lsc@dbserver01), a wallet for your server (ldapusr@oidserver01). Create a certification request for CN=lsc,cn=users,dc=yourdomain,dc=com, export your user certificate from your client wallet (lsc@dbserver01) and import it as a trusted certificate in your oid wallet (ldapusr@oidserver01). It is the same procedure as described in the user identified externally with SSL certificate post

Then, launch oidadmin (you can launch it from a pc client), and configure the OID for ssl.
In Oracle Internet Directory Servers –> cn=orcladmin@oidserver01 –> Server Management –> Directory Server. Right click on Configuration Set1 and chose Create Like. In the Configuration Set2, chose the SSL Settings, specify SSL Client and Server Authentication, SSL only, file://etc/ORACLE/WALLETS/lsc, SSL Port 44019. Apply. Quit

Start a second instance with oidctl where oid1014 is your SID

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    instance=2 configset=2 start

Check the status :

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
oidctl:Checking Oracle Internet Directory Processes ...

    Process oidmon is alive as PID = 16191 

    Checking OIDLDAPD instance 1  ...
    Process oidldapd (dispatcher) is alive as PID = 16197
        port=44015 sslport=44016
            oidldapd (server)     is alive as PID = 16206

    Checking OIDLDAPD instance 2  ...
    Process oidldapd (dispatcher) is alive as PID = 16422
            oidldapd (server)     is alive as PID = 16426

    Checking ODISRV instance 1  ...
    Process odisrv   is alive as PID = 16203

so far not bad!

Let’s pray !

$ ldapbind -h novgaasdv01.eu.novartis.net -p 44019  -U 3
    -P mywalletpasswd -W file://etc/ORACLE/WALLETS/lsc  
    -D cn=orcladmin -w myorcladminpasswd
bind successful

What a satisfaction 😀 I have being failing on this for days. Mostly getting meaningless message like UnKnown Error Encountered. You cannot start anything with that error. It could be a wallet path error, a wallet password error, a non-authorized certificate, a certification authority problem, and many other errors. Really poor error messaging there. One may argue that meaningless error message on unsuccessful login does increase the security, but well, it is a nightmare to debug 😮

Now I need to stop the non-secure part of it :

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    instance=1 stop

Added 29.3.2007
If I want to use a SSL to authentify my user, I must create a user, for example with the Security Console http://oidserver01:44000/oiddas/ui/oideushome, which matches my certificate.

Set up ovid to use tns with your ldap server

I had a question in my mailbox today about using TNS resolution with an unsupported LDAP Server like Sun Java System Directory Server.

Supported in 9i and above are only Microsoft Active Directory and Oracle Internet Directory. In 8i also Novell.

So I have done this once with OVID.

1) download Oracle Virtual Directory
2) Install the OVID on your server (or on a separate server). The latest release is 10.1.4. I used 3.0.3.

$ sh ./ovid303j.bin -i console
Preparing to install...

Enter a uniquely descriptive name for the server. 

used only by ovid, not related to hostname or whatever

Server Name (DEFAULT: Virtual Directory 1): 

Enter the port number on which to provide administrative services. 

a console port, used only by ovid manager

Port Number (DEFAULT: 8888): 

a credential, only for ovid

Root User DN (DEFAULT: cn=Admin): 

Enter a port number to provide LDAP services on (e.g. 389, 636).

this is what will be used by your TNS client, any free port will rule

Port (DEFAULT: 389): 

Please enter the base entry of your directory (e.g. o=YourCompany,c=US) 

not your Server ldap, chose one db domain, here oracle.world.

Directory Base Suffix[dc=YourCompany,dc=com]: dc=oracle,dc=world

3) start the OVID

$ ./vde.sh start
Starting VDE...

4) install the OVID manager (GUI-console) on your PC / workstation. I used Windows. It is available for Windows. If you do not have windows, you can edit the XML files directly (good luck)
5) in the ovid manager, create a new project, add your ovid, and start creating adapters. For each possible DB domain, you must create one adaptor, for example if you try to tnsping LSC01.PROD.DB and you have the description stored as cn=lsc01, cn=oracleContext, cn=PROD.DB, ou=tns, ou=appl, dc=lcsys, dc=ch you will need have

root: dc=PROD,dc=DB
remoteBase: cn=PROD.DB,ou=tns,ou=appl,dc=lcsys,dc=ch

This should work. Not sure if you will like OViD, the interface is pretty awful.

Also it is not too flexible, you need to add an adaptor for each db domain. Still better than providing a TopDomain for each DB Domain with anonymous access to root.

Yes, one more point, tns client access the ldap anonymously, so OViD does help to improve security, you set anonymous access to your OViD, you disable ACI on your OViD, and you use a credential to log in to your LDAP. It is safer, because you use a remoteBase, so anonymous access is granted only to that branch of your directory.

Migration of tnsnames.ora to LDAP (Sun Java System Directory Server)

In this post, I did show how easy it is to use OID to resolve your network service names.

Apart OID, AD (Microsoft Active Directory) is also supported.

However, I do not want to use such products, as my customer already have a Sun Java System Directory Server running.

It is quite easy. Here are the steps with the SunOne Console.

1) expand the schema
login to the Directory Server as cn=directory manager
click schema in the configuration tab
In the Attributes subtab, click create, and type it orclnetdescstring as attribute name, and select OctetString as Syntax, and uncheck multi-valued, click OK.
In the Object Classes subtab, Create a class named OrclService , add cn as required attribute and orclnetdescstring as allowed attribute. Click OK

2) start adding services
either with your prefered ldap GUI (like Siemens DirX Manager) or with command line
dn: ou=intranet, dc=lcsys, dc=ch
ou: intranet
objectClass: top
objectClass: organizationalunit

dn: ou=applications, ou=intranet, dc=lcsys, dc=ch
ou: applications
objectClass: top
objectClass: organizationalunit

dn: ou=TNSnames, ou=applications, ou=intranet, dc=lcsys,dc=ch
ou: TNSnames
objectClass: top
objectClass: organizationalunit

dn: cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch
cn: OracleContext
objectClass: top
objectClass: orclservice

dn: cn=lsc01, cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch
cn: lsc01
objectClass: top
objectClass: orclservice
orclnetdescstring: (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=blade01.lcsys.ch)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = lsc01.lcsys.ch)))

which I can add with

ldapadd -h blade01 -p 34001 -D “cn=Directory Manager” -w *** -f lsc01.ldif

adding new entry ou=intranet, dc=lcsys, dc=ch

adding new entry ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry cn=OracleContext,ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

adding new entry cn=lsc01, cn=OracleContext, ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch

Configuring sqlnet.ora and ldap.ora is the last step :


DIRECTORY_SERVERS= (blade01:34001)
DEFAULT_ADMIN_CONTEXT = "ou=TNSnames, ou=applications, ou=intranet, dc=lcsys, dc=ch"

try to tnsping, it should work. If it does not, check /tmp/tnsping.trc

$ tnsping LSC01

TNS Ping Utility for Solaris: Version - Production on 09-OCT-2006 15:50:42

Copyright (c) 1997, 2006, Oracle Corporation.  All rights reserved.

Used parameter files:

Used LDAP adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = blade01)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = lsc01.lcsys.ch)))
OK (0 msec)

note that using something else than OID or AD is not supported

no more tnsnames

with netca, it is easy to configure your sqlnet.ora to use LDAP instead of tnsnames.ora. The ldap.ora and sqlnet.ora are updated… than it works, sqlplus user@db is correctly looking in the ldap oracle content

ldap day 2

what can I do with LDAP?
what is the difference between LDAP and Oracle Internet Directory?

Well, there is quite a lot of interresting documents, pictures and faq on otn :

  • OTN Directory homepage
  • Directory Admin guide
  • Identity Management ReferenceLDAP is a directory server, the info are stored in an Oracle database.

    When you download application server (about 2Gb), you get a fully functionnal database preconfigured and an ldap server running.

    To start the admin tool, just type oidadmin in the command line.

    The password to use is the same as ias_admin and the username is orcladmin

  • ldap server

    My ldap server is up and running on my notebook with SLES9.

    Next, next, next, install. That is it.

    Oracle Application Server creates a database and start the Oracle Internet Directory – understand LDAP server – automatically.

    It can then be configured with the web interface.