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 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 <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 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

<MIDDLEWARE_HOME>\user_projects\domains\<domainname>\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.

    
    

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

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

      
          
          
                    
                    
                    
                    
                    
                    
      
                    
                    
                        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: <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 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 <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

<ODI_HOME>\oracledi.common\odi\lib
<ODI_HOME>\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 <ODI_HOME>\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.

ODI Series - problems using pre/post maxl option in interfaces

 This problem was brought up on the ODI forum so I thought it is probably more beneficial if I write it up in case anybody experiences the same problem.

I will be running through the issue on a windows OS but the concept is the same if using *nix

If you are integrating with essbase 11.x and using either the PRE_LOAD_MAXL _SCRIPT or POST_LOAD_MAXL _SCRIPT options in any of the interface KM options

It is possible you could encounter one of the following error messages in the operator.

or the following error

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File "<string>", line 89, in <module>
at com.hyperion.odi.essbase.ODIEssbaseConnection.executeMaxl(Unknown Source)
at com.hyperion.odi.essbase.AbstractEssbaseWriter.beginLoad(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)com.hyperion.odi.essbase.ODIEssbaseException: com.hyperion.odi.essbase.ODIEssbaseException: Cannot run program "essmsh": CreateProcess error=2, The system cannot find the file specified
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)

I will just briefly explain what ODI does behind the scenes when you use any Maxl options.

Once an interface is executed the Java code used by the knowledge module checks if one of the Maxl options is populated, if it is it then uses a property file to format and form a command line statement.

essmsh = MaxL shell
-m error = Sets the level of messages returned by the shell. Valid values are: all (the default), warning, error, and fatal.
-s = server name
-l = user and password
0 = Essbase server name
1 = Essbase Port
2 = User name
3 = Password
4 = Maxl Script

An example of the command line generated is

essmsh -m error -s essServer:1423 -l admin password F:\scripts\log.mxl

The code then uses the Runtime Java class to execute the statement.

The reasons behind the errors are a combination of essmsh cannot be found as the location does not exist in the environment path variable.

In ODI this would generate the error message

Cannot run program "essmsh": CreateProcess error=2, The system cannot find the file specified

If the location of essmsh command does exist in the path variable then the following can occur

In ODI this would produce the first error I highlighted at the beginning of this blog.

From version 11 a number of environment variables are not automatically set and the use of the  StartMaxl script is the preferred method to start up a MaxL session.

If have the essbase client installed and edit the startMaxl script then you can see that the variables ESSBASEPATH, ARBORPATH and PATH are set before calling the MaxL shell.

It is similar on the essbase server install except the startMaxl script calls a setEssbaseEnv script which sets additional variables.

The simple solution to fix the issue with OD calling the Maxl Shell is to set the environment variables either at OS user, system or ODI agent level.
By ODI agent level I mean updating the scripts that start up the agent and adding in the variables.

If the ODI agent is using the essbase client then an example variables would be

ARBORPATH -  <MIDDLWARE_HOME>\EPMSystem11R1/common/EssbaseRTC-64/<VERSION>

e.g.  E:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0

The ESSBASEPATH variable should be exactly the same as the ARBORPATH variable

The following locations are required in the PATH variable.

<MIDDLEWARE_HOME> EPMSystem11R1\bin; and <MIDDLWARE_HOME>\EPMSystem11R1\common\EssbaseRTC-64\<V ERSION>\bin;

e.g.  E:\Oracle\Middleware\EPMSystem11R1\bin; E:\Oracle\Middleware\EPMSystem11R1\common\EssbaseRTC-64\11.1.2.0\bin;

If the ODI agent is on the essbase server then just have a look at the setEssbaseEnv script to find out the values to use for the ARBORPATH,ESSBASEPATH and PATH variables.

Once the changes have been made restart all the ODI related components and possibly reboot the machine if using an older OS to make sure the variables have been applied and then the interfaces should be able to execute the MaxL scripts.

ODI Series – tips for improving essbase load times.

I thought I would cover a few tips for speeding up data loading to essbase using ODI.

The first tip is for speeding up data loads to essbase databases and while it may be obvious to many people I often find this setting in the IKM left as default and hear complaints that apparently the essbase adaptor is slow at loading data.

When you first create an interface to load data to an essbase database you will see an option in the IKM called COMMIT_INTERVAL.

The default setting for this option is 1000 which basically means that the data will be streamed using the Java API to the database in chunks of 1000 records.

Now let me go through an example of the difference in load times when using this option.

I successfully loaded over 311,000 records to an ASO database from a RDBMS source, in the operator you will see that it took the worrying time of 309 seconds.

In the log you can see that there is an entry for each of the 1000 records being loaded to the buffer, this is not only slowing the data load by a massive amount but also increasing the log size because for just one load the process is being repeated over 300 times.

So let’s see what happens when the COMMIT_INTERVAL is increased to a size that is bigger than the number of records to be loaded.

The data load is down to 14 seconds which is a huge improvement.

As the ODI agent is now handling loading the data in larger chunks then this means there will be an increase in the amount of the memory required for the JVM.

The graph displays the memory being used by the agent with the default 1000 commit records and then with the increased commit size so be prepared to increase the maximum heap size of the agent.

So if you are loading 5 million records it is not as simple as setting the commit size to that amount, testing is required to get a balance between an acceptable load time and JVM size.

If you are data loading to essbase using the ODI KM then I suggest to always use a load rule as it is the optimal method, if you don’t use a load rule if a record is rejected for any reason eg. Unknown member the load method changes and starts loading record by record.

If you have a large data load with possible error records then you could be waiting for an eternity for the load to finish without using a rule.

There are other optimisation techniques outside of ODI but If are loading data to a BSO database then it is also definitely worth reading the section “Optimizing Data Loads” which I am not going to cover as I believe in this case the documentation does a good job of explaining and if applied can drastically reduce load times.

There is another possible option to speeding up loading but I will be perfectly honest and say it may not actually make that much of a difference when using the Hyperion technologies and has a bigger impact when using RDBMS technologies.

If you look at a Hyperion related data server in the topology you will see “Array Fetch Size” which is set to 30 and cannot be changed.

This basically means is that when the knowledge module Java code retrieves data from the source or staging area it will retrieve 30 rows at a time.

If you edit one of the Hyperion knowledge modules and look at the code for a load step then you will see the fetch array being used.

The code retrieves the value stored in the “Array Fetch Size” and then uses that as the fetch size in the JDBC SQL queries.

If you look in the operator at the step the values is always set to the default of 30.

One way of being able to set the fetch size in the interface KM options is to first create a new option, this is done by right clicking a Knowledge module and selecting “New Option”.

I have created an option called FETCH_SIZE with a type of Value and a default of 30.

Now the load step of the KM is edited and the fetch size is set by retrieving the value in the option using.

stmt.setFetchSize(<%=odiRef.getOption("FETCH_SIZE")%>)

In the interface KM options the FETCH_SIZE should now be available to be set.

If you are going to test out changing the default fetch size then the answer is not just to set the size as big as possible because it will probably have a detrimental effect on the performance or crash the agent JVM, it does need playing around with to find the optimal value.