Sunday, 18 March 2012

ODI Series – External authentication with Microsoft Active Directory in ODI 11g

I recently tasked myself to set up external authentication to Microsoft Active Directory (which I will refer to as MSAD from now on) in ODI 11g Studio as it is quite common to be requested to add in this functionality.

External authentication was a new feature in the first release of ODI 11g and I was expecting the configuration to be simple and be built in to the Studio, unfortunately there is no mechanism within the Studio to set up the authentication and to be honest the documentation is not the most helpful.

Before anybody says to me there is an Oracle by Example on the topic I need to point out that yes this is true but it is based on OID and the example is missing lots of key information plus there is no information on setting it up for MSAD.

Once you know how to configure then I agree it is really quite simple but in the process I believe I hit upon a possible bug which threw me for a while and I ended up using an LDAP browser, logging in the AD and watching the packets sent from Studio with Wireshark, mostly because the error messages returned from the Studio were not the most informative.

Anyway I thought that I would write up the process not only for my benefit but for anybody else that has the requirement to set up authentication to MSAD.

If you believe any of the information I provide in this write up is incorrect or you know of an easier method please feel free to get in touch.

I will run through the process in ODI but should be valid for, there are some differences with which I will point out as I go along.

Here is what the Oracle documentation has to say about setting up External Authentication

"Oracle Platform Security Services (OPSS) is a standards-based and portable security framework for Java applications. OPSS offers the standard Java Security Model services for authentication and authorization.
Oracle Data Integrator stores all user information as well as users' privileges in the master repository by default. When a user logs to Oracle Data Integrator, it logs against the master repository. This authentication method is called Internal Authentication.
Oracle Data Integrator can optionally use OPSS to authenticate its users against an external Identity Store, which contains enterprise user and passwords. Such an identity store is used at the enterprise level by all applications, in order to have centralized user and passwords definitions and Single Sign-On (SSO). In such configuration, the repository only contains references to these enterprise users. This authentication method is called External Authentication."

Ok, so ODI 11g is using OPSS to authenticate with an external identity store, let’s read the next section on how to configure and everything should make perfect sense.

"To use the External Authentication option, you need to configure an enterprise Identity Store (LDAP, Oracle Internet Directory, and so forth), and have this identity store configured for each Oracle Data Integrator component to refer by default to it.
Oracle Data Integrator Studio

The configuration to connect and use the identity store is contained in an OPSS Configuration file called jps-config.xml file. See "Configuring a JavaEE Application to Use OPSS" in the Oracle Fusion Middleware Application Security Guide for more information.

Copy this file into the ODI_HOME/client/odi/bin/ directory. The Studio reads the identity store configuration and authenticates against the configured identity store."

Basically when you want full details the documentation goes very vague and sends you off to the fusion middleware security guide.

So it sounds like all that is needed is to find and edit jps-config.xml and configure it to authenticate against the AD, JPS stands for Java Policy Store just in case you were wondering.

 If you open odi.conf in <ODI_HOME>\client\odi\bin then you will see that the Studio uses the jps-config.xml file.

First thing you would expect is an example jps-config.xml file to exist within the ODI installation, unfortunately if you are using or then there is no example file, in then there is an sample file.

^ ODI comes with a sample jps-config file.

The Middleware Security Guide does provide an example jps-config.xml file and if you have deployed any middleware products with WebLogic then you will find a file under


Once you do have a look at the file then you will notice there is a hell of a lot of configuration information and it can be confusing where to start.

There is also no information in the file relating to Microsoft Active Directory.

Most of the elements in the file are not actually required so can be stripped out and as I am kind I will shortly show you an example of the final working file that I am using.

I will first go through the steps of creating a user in the AD and testing the connection through LDAP.

supervisor user was created in the AD, the user doesn’t have to be named supervisor and does not need to be an administrator.

To prove my sanity I installed an LDAP browser on the machine hosting the Studio.

Server = EPMAD, Port = 389, Base DN: DC=epmsad,DC=com

I successfully tested the connection to the AD with supervisor user with a full DN of

It is important to make sure that there are no problems connecting to the AD using LDAP before configuring ODI to use external authentication because don’t always expect a nice error message telling you exactly where you have gone if there is a problem.


            SecretStore-based CSF provider
             LDAP-based IdentityStore Provider

            This is Jaas Login Service Provider and is used to configure login module service instances

            File Based Credential Store Service Instance

            Identity Store Login Module

            User Authentication Login Module



Above is a fully configured and working jps-config.xml file for MSAD, luckily you don’t have to go through the pain of getting it to that stage, if you are not editing odi.conf then it needs to be copied to <ODI_HOME>\client\odi\bin

To configure it for your own AD you will just need to update the AD information under
I only managed to find one example of using MSAD in a jps-config file and that is what caused the most pain as if you use the example then ODI will generate an error, I will go through it in more detail at the end of this blog.

            File Based Credential Store Service Instance
In the jps-config example I provided you will see the above elements which mean that a file based credential store also known as wallet-based credential store is going to be used, the file will hold encrypted information for the User DN and password to connect to the AD.

The cwallet.sso file has to be created and this was another stumbling block, if you read the Oracle By Example it says to run the  run_credtool script which doesn’t actually exist in ODI and, I will get on to later.

After searching the following article in Oracle Support –
ODI Studio - Unable to Configure LDAP Server Authentication Without Clear Password Information [ID 1319563.1]

The article highlights

“The problem is caused by internal Bug 12398394, were it is documented
"The issue is that ODI does not provide a facility to create credential stores for use with standalone ODI agent and ODI Studio."

It also goes through the steps on how to install the credstore tool, you can follow the document or follow my steps as there were a couple of anomalies and you even get screen shots with my version.

Download the credtool - from here

Expand the zip into any location

You should end up with the above file structure.

Download ANT version 1.7.0 e.g

If you look at the file structure for odi-credtool you will see a directory \lib\org.apache.ant_1.7.0

Open the downloaded ANT  1.7.0 zip file and go into directory \apache-ant-1.1.70  and extract all to odi-credtool\lib\org.apache.ant_1.7.0

You should end up with a structure like above.

If you look at the odi-credtool structure you will see it has \oracle_common\modules and then a number of empty directories, you need to populate these directories with files from a Fusion Middleware 11g installation e.g. OBIEE, SOA, EPM.

I am using an EPM installation; copy the matching directories to the odi-credtool\oracle_common\modules folder and replace/merge

Edit the run_credtool script in the root directory of the credtool, you will probably need update the JAVA_HOME variable and point it to the valid Java location, if you are planning on running the tool from the directory then the other variables should be fine.

From command line change directory to the tool and execute the run_credtool script.

The first two inputs the tool requires are the Key and Alias to create a map for the credentials.

In the JPS-config file I provided you will see there are two properties “security.principal.key” and “security.principal.alias” and the values entered in the tool should match these.
Alias : JPS
Key: msad.ldap.credentials

The next two inputs are the full distinguished name of the user in the AD that is going to be used and the password for the user.

User Name: cn=supervisor,cn=users,DC=epmmsad,DC=com

This is another area that threw me a little as the examples I have seen just used the  cn=username which I didn’t believe would work and found out that it wasn’t binding when watching the ldap packets.

I also realised that I needed to shut down the Studio and reopen it each time I made a configuration change as I am sure it was caching some of the information.

The final input is the full path and filename for the jps-config file e.g.
JPS Config: <ODI_HOME>\oracledi\client\odi\bin\jps-config.xml

If the tool was successful the credential file cwallet.sso will have been generated.

If you are running ODI then you can still use the above method or use a much simpler method to create the cwallet.sso file, in the bin directory there is a script called which has been created for Unix type operating systems.

If on a windows OS then you will need to edit the file.

Now what I expected you would be able to do is just copy from java onwards, open a command prompt, change directory to <ODI_HOME>\oracledi\client\odi\bin and paste the statement.

Unfortunately this generates an error as it can’t find a required Java class odi-core.jar which exists in the following locations


and not  ../../jdev/extensions/oracle.odi.navigator/lib/odi-core.jar  as specified in the odi_credtool script.

plus jps-manifest.jar is in <ODI_HOME>\modules\oracle.jps_11.1.1 in my fresh install of

So I updated  the classpaths and this is the final working command line

java -classpath E:\Oracle\Middleware\OD11g\oracledi.common\odi\lib\odi-core.jar;E:\Oracle\Middleware\OD11g\modules\oracle.jps_11.1.1\jps-manifest.jar\Oracle\Middleware\OD11g\oracledi\client\odi\bin\jps-config.xml

The inputs are exactly I explained earlier except there is no need to supply the path to jps-config.xml, there is an xml validation warning but that can be ignored as the cwallet.sso file is correctly generated.

So now we are ready to set up the master repository to use external authentication, it is possible to switch an active master repository to use external authentication within the Studio by going to ODI > Switch Authentication Mode but for this example I am going to create a new repository.

In the Studio you follow the normal process for creating a master repository by first providing the database connection information.

Next select “Use External Authentication” and enter the AD user you want to authenticate with.

I am using the ODI repository for password storage but don’t worry the AD password is not stored in the repository

Now once you click OK if everything has been configured correctly you will see the master repository being created in the log window otherwise you will be hit with an error message which is sometimes meaningful and sometimes not depending on what is wrong.

If you log into the master repository and go to users you will see the user has an External GUID which is a unique identifier for the user in the AD.

To add a new a new AD user into the Studio just select “New User” and enter a user that exists in the AD.

If clicking Retrieve GUID returns the identifier then you are pretty much assured that all is well.

You then should be able to log into the Studio using the AD user.

Going back to the issue that threw me for a while because I was not sure if the JPS-config file was not configured correctly or I managed to not set up the wallet in the right way.

When I was configuring the AD details in the jps-config file I only came across one example on the internet using MSAD which was in the Oracle document “Oracle® Fusion Middleware Application Security Guide 11g Release 1 (11.1.1)

In the oracle example it uses the following for the username and group properties.

Seeing as it is an Oracle security document I could only assume that ODI would accept these elements.

Caused by: java.lang.ClassCastException: [Ljava.lang.String;

Once I configured using the extended property element Studio would throw the above error which as you can imagine does not point to where the problem could be.

If the jps-config file is updated to the above format then ODI allows the configuration, I have raised an SR with Oracle and I am awaiting a response from the development team to acknowledge whether it is a bug or not.

Just as I finish the write up I get an update on the SR, it has been logged as “Bug 13855998 - SUPPLIED JSP-CONFIG.XML FILE IS INCORRECT RESULTING IN FAILED AUTHENTICATION

I think I will end it there but hopefully you have found this useful.


Rodrigo said...

Hi John, great post as usual! I copied your XML, changed the serviceInstances part and created the cwallet.sso file successfully (at least no error was shown). But when I go to ODI/Switch Authentication Mode and select my master repository I get the following error:

oracle.odi.setup.AuthenticationConfigurationSwitchingException: target name XML:NAMESPACE of processing instruction at line {1}, column {2} is reserved

Do you know what it means? I googled for a solution but nothing helped until now.


John Goodwin said...

Hi Rodrigo,

I am not too sure on the error message, it could be an issue with the format of jps-config.xml

Ekaterina Belinskaya said...

Hi John!
i have in my AD more than 20 thousands users. And when i configure jps-config.xml like

, i get ldap error: LDAP: error code 4 - Sizelimit Exceeded.
I need to set filter on only members of one particular group. How do i can do this?
Thank you!

Anonymous said...

I had run into the same problem as the previous poster, but found that refining my filter by using an object class filter helped.

<-- snip -->



<-- snip -->

For more details.


Anonymous said...

Hi John,
Thanks for this post.
It always pointing real customer questions!!

So I created the cwallet.sso file successfully.
But when I try to Switch Authentication Mode I get the following error:

at oracle.odi.setup.SwitchToExternalAuthenticationService.listMatchedSupervisorUsernames(

Do you have an idea? I can't find any solution.