Integrating BI Publisher and Microsoft Active Directory

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.

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

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.

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

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.

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.

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

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.

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

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.

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?

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.