view counter

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

Thanks to John Goodwin for this story
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.
view counter

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 11.1.1.5 but should be valid for 11.1.1.3, there are some differences with 11.1.1.6 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 \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 11.1.1.3 or 11.1.1.5 then there is no example file, in 11.1.1.6+ then there is an sample file.

^ ODI 11.1.1.6 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

\user_projects\domains\\config\fmwconfig

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 epmmsad.com, 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
cn=supervisor,cn=users,DC=epmmsad,DC=com

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.


<?xml version="1.0" encoding="UTF-8" standalone='yes'?>






SecretStore-based CSF provider



LDAP-based IdentityStore Provider



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















user.search.bases

CN=Users,DC=epmmsad,DC=com



group.search.bases

CN=Groups,DC=epmmsad,DC=com






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 \client\odi\bin

To configure it for your own AD you will just need to update the AD information under
 














user.search.bases

CN=Users,DC=epmmsad,DC=com



group.search.bases

CN=Groups,DC=epmmsad,DC=com



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 11.1.1.3 and 11.1.1.5, I will get on to 11.1.1.6 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 - odi_credtool.zip 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 apache-ant-1.7.0-bin.zip

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 11.1.2.1 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.
e.g.
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: \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 11.1.1.6 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 odi_credtool.sh 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 \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

\oracledi.common\odi\lib
\oracledi.sdk\lib

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

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

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 -Doracle.security.jps.config=E:\Oracle\Middleware\OD11g\oracledi\client\odi\bin\jps-config.xml oracle.odi.core.security.JPSContextCredTool

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: oracle.security.jps.service.idstore.IdentityStoreException: java.lang.ClassCastException: [Ljava.lang.String;
at oracle.security.jps.internal.idstore.AbstractIdmIdentityStore.initStore(AbstractIdmIdentityStore.java:156)
    at oracle.security.jps.internal.idstore.AbstractIdmIdentityStore.getIdmStore(AbstractIdmIdentityStore.java:110)

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.

Read the entire article at its source

view counter