Integrating BI Publisher and Microsoft Active Directory

October 23rd, 2009 by

Whilst most BI Publisher installations I’ve worked with use either Oracle BI Server security or Oracle Internet Directory, it might be the case that you want to use Microsoft Active Directory instead. The most common way I see this happening is either with Active Directory hooked into Oracle BI Server, with BI Publisher then using BI Server security, or with Active Directory sync’d to Oracle Internet Directory (OID) and BI Publisher connecting to that. What you should be able to do though, if you don’t have OID or Oracle BI Server, is connect BI Publisher directly to Active Directory.

Interestingly, if you look in the online BI Publisher docs, Active Directory is listed as a certified directory server but there are no specific instructions for it. Active Directory is supposed to connect as with any other LDAP server, but the LDAP instructions in the docs only cover OID and the BI Publisher forum has a few postings mentioning it but with no clear solution. Asking around the company, most people think it’s possible but no-one’s actually done it (because Active Directory isn’t something you just have hanging around), so I thought I’d work through the process.

Active Directory is the LDAP server that ships with Windows Server 2000, Windows Server 2003 and so on, and typically is used within organizations to provide desktop logins, email accounts and so on. You administer it using the Active Directory Users and Computers utility on the Windows Server, where you can set up users, groups, objects, organizational units and so on. The screenshot below shows a typical Active Directory setup, more or less “out of the box” with a few objects created.

Ad1

Taking a closer look, there are two users defined within the main “Users” group:

Ad2

And there is a main group, “Users”, with two organizational units (“Sales”, and “Germany”) that are also under the root of the directory. Now most organzations put all of their organization units, sub-groups, users and so on under the “Users” node, but many don’t, and I’ll come back to the significance of this in a moment.

Ad3

Note also the XMLP_ groups, that you need to create the Active Directory directory so that you can grant access to BI Publisher functions.

Ad4

So far this is all pretty standard stuff, and what you’d do for any LDAP server including Oracle Internet Directory. In a moment I’ll go over to the BI Publisher web interface to start configuring security settings there, but before I do this it’s always nice to check that you can connect (or “bind”) to the Active Directory server from wherever BI Publisher is installed, and you’ve got your credentials correct.

My BI Publisher server is running on an Oracle Enterprise Linux 5 machine, and so I use the ldapbind command to try and connect as the Active Directory administrator, and then a couple of the users.

Ad6

In case you’ve not used it before, ldapbind attempts to bind to an LDAP server using the server details and credentials you pass over. The -D flag passes the username, -h sends the host name (I left off the LDAP port number of 389 as this is used as a default), and -w sends the password. Notice how for the third user, he’s not part of the Users group, he’s part of the Germany organization unit instead, but as far as ldapbind is concerned this is fine.

So, I can connect to Active Directory, both as an administrator and as a user. Now it’s across to the BI Publisher web interface to set up the security settings.

Now the first thing that you should always do when making changes to the security settings in BI Publisher, is set up a superuser password beforehand. When you switch from BI Publisher security to LDAP security, for example, the Administrator password that you usually use won’t work anymore, and you’ll only be able to log in as LDAP users. Now if you’ve actually mucked up the settings and you can’t log in, this superuser password gives you a “back door” so that you can get in and correct things. I set this up and then restart WebLogic so that the superuser account takes effect.

Ad7

Once back in, it’s time to set up the LDAP security settings. Back in the Security Settings page, I switch security mode to LDAP, and then enter the details in for Active Directory. For me, with a pretty much “out of the box” Active Directory setup, they are:

Security Model : LDAP
Administrator Username : CN=Administrator,CN=Users,DC=rittmanmead,DC=local
Administrator Password : password
Distinguished Name for Users : cn=Users,dc=rittmanmead,dc=local
Distinguished Name for Groups : cn=Users,dc=rittmanmead,dc=local
Group Search Filter : (&(objectclass=group)(cn=*))
Group Attribute Name : cn
Group Member Attribute Name : member
Group Description Attribute Name : description
JNDI Context Factory Class : com.sun.jndi.ldap.LdapCtxFactory
Automatically Clear LDAP Cache : yes
Ldap Cache Interval : 1 minute

Ad8

Now these values are going to be different for every organization, but there’s a couple of points to note:

  • The administrator username needs to be the full LDAP name including the group, distinguished name etc
  • For my AD tree, I have users and groups under the same CN. Others may have groups under cn=Groups
  • Note also that the Distinguished Name for users and groups has to name a top-level group, which means that the organization units under the AD root can’t be searched or access by BI Publisher when authenticating. This is a limitation (Bug No. 7596148) that still applies as of the latest version (10.1.3.4.1) of BI Publisher, which means that all of the users and groups you use with BI Publisher need to be under a top-level group such as Users or Groups, and you can effectively only have one organization unit in AD.

Update 4-April-2010: Bug No. 7596148 has now been addressed by patch 9546699, which is available for download on metalink.

So, let’s try it out. Let’s log in as myself first of all.

Ad9

Great, this works, I’ve got in and been granted administrative access, as I’d given myself the XMLP_ADMIN role in Active Directory.

Ad10

Going over to the Roles and Permissions screen, I can see all of the Active Directory roles have been brought into BI Publisher. I can use these to limit access to folders later on, whilst managing membership of the roles within Active Directory.

Ad11

But what about my German colleagues, who are part of the Germany organizational unit in Active Directory but not under cn=Users? Can they log in?

Ad13

No, they can’t. The only way I could get them to authenticate would be to move them into the Users folder under the main rittmanmead.local root, and do away with the idea of multiple organizational units under the root.

So, there’s two takeaways from this story. Firstly, though it’s not explicitly documented and there’s not much information on the forums, Active Directory is supported as a directory for BI Publisher. However, you’ve got to set your Active Directory tree up in a certain way, with only one organizational unit, which won’t be a problem for green-field sites but might cause an issue for established Active Directory users. Seeing as this is registered as a bug I presume there’ll be a fix or an enhancement at some point, but bear this in mind if you’re looking to use Active Directory directly and especially if you can work out why certain users can’t authenticate.

Thanks to Shinji and the rest of the BI Publisher development team for the tips in relation to this posting and the one before on Weblogic.

Comments

  1. Jim Willeke Says:

    Try this change:
    “Distinguished Name for Users : dc=rittmanmead,dc=local”

    If the BI application is doing a Scoped Search of Sub(tree) this shoudl find all users from the dc=rittmanmead,dc=local container down.

    You might also try/have to also move the groups DN to dc=rittmanmead,dc=local also.

    -jim

  2. Mark Rittman Says:

    Hi Jim,

    You’re right, using that shorted DN should work, but BI Publisher doesn’t let you use that, hence the bug mentioned above. As you say, if we were able to do that then we’d be able to pick up the other organizational units.

    Regards

    Mark

  3. Rohit Says:

    Hi Mark,

    As Jim pointed out, we are able to use the shorted DN and it works. Users are being authenticated and they can see folders assigned to them.

    Problem we are running into is that people assigned to group “XMLP_ADMIN” are not able to see admin controls on user interface.

    Regards,
    Rohit

  4. Dario Says:

    Hi, i’ve followed your notes and i was able to connect my BIP with MS AD, now i need to know if there is a way to use saMAccountName instead of cn.
    Thanks

  5. John Hollifield Says:

    Hi

    Thanks for helping get this sorted. I, like Dario, would like to know if you can use saMAccountName instead of cn.

    Regards

    John

  6. Daniel Says:

    Hi John,
    I was able to use sAMAccountName by inserting it into the field named “attribute used for RDN”.

    Regards,

    Daniel

  7. Dario Says:

    Daniel, i’ve just now read your suggestion and it works, thanks a lot!!

  8. John Hollifield Says:

    Hi Daniel

    Thanks for the tip, all set up now.

    John

  9. João Paulo Says:

    Hi,

    Is it possible to integrate one BIP against 2 diffs AD servers?

    Thanks,
    João Paulo

  10. Mark Rittman Says:

    Hi Joao

    I’m not sure in 10g, at least not directly (you may be able to link BIP to OID, for example, and then have OID sync with two different AD servers). In 11g, my understanding is that you’d link BIP to WebLogic Server, which itself would then link to one or more AD servers, which would be a cleaner solution (albeit reliant on BIP 11g)

    Mark

  11. Nam Says:

    Hi Mark,

    I followed your instruction and it could be connected to ldap server. But I can’t connect from BI as administrator. How can we do it, please?

  12. Dodge Says:

    Looking for a clarification: does the “Administrator Username” you have to provide in the LDAP security model prompts have to be the LDAP Administrator? Not the BI Publisher Administrator name? It is a little confusing because BIP uses “Administrator” as its Administrator name. Our LDAP admins are uneasy about providing this info. Thanks.

  13. Dharmi Says:

    Hi, i have followed the above instruction but after adding user to XMLP_ADMIN group still unable to see folders and admin tab. do we need to give any specific permission to XMLP_ADMIN group?

    also wanted to know that can it work with Kerberos authentication method?

    thanks in advance
    Regards,
    Dharmi

  14. Dharmi Says:

    Scheduling is not working after AD integration. its giving following error

    070912_065630863][oracle.apps.xdo.delivery.DeliveryManager][STATEMENT] initConfig(): loading default properties :{}
    [070912_065634172][][EXCEPTION] javax.naming.AuthenticationException: [LDAP: error code 49 – 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db1.SecurityManagerImpl.getLDAPPrincipal(SecurityManagerImpl.java:825)
    at oracle.apps.xdo.servlet.security.SecurityManagerImpl.impersonate(SecurityManagerImpl.java:474)
    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.scheduleJob(SchedulerServlet.java:1134)
    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.doPost(SchedulerServlet.java:293)

    Any help will be appreciated.

    Regards,
    Dharmi

  15. Suvir Says:

    Hi Dharmi,

    Did you get the resolution? I am getting similar error and need help. Anyone plz help

    oracle.apps.xdo.servlet.scheduler.ProcessingException: Job scheduling failed because the user has no permission to access this report. [REPORT_URL]=[], [USERNAME]=[]

    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.scheduleJob(SchedulerServlet.java:1127)

    at oracle.apps.xdo.servlet.ui.scheduler.SchedulerServlet.doPost(SchedulerServlet.java:293)

  16. Richard Yang Says:

    Hi Mark,

    Many thanks for the sharing. We have set up the OBIEE 10g integration with Windows 2008 AD, and basic authentication of OBIEE thru AD works fine. But we found one issue is like below:

    We have configured domain security policy e.g. lock user after 3 times failed login, but the policy doesn’t work if the user login from OBIEE (if the user login from windows workstation OS, the policy take effective).

    do you have any experience around that? Appriciated if you can provide some guidance on that! Many Thanks.

    Regards,
    Richard Yang

  17. Dharmi Says:

    Hi Suvir,

    there was bug 7596148 for LDAP authentication, I upgraded my current version to 10.1.4.2 and it started working.

    Anybody has idea about authentication method BI publisher uses to connect to MS Active directory?

    Regards,
    Dharmi

Website Design & Build: tymedia.co.uk