Linking Pentaho Business Intelligence Server 5.0 Community Edition to Active Directory

So.. You’ve installed Pentaho BI Server 5 CE, and have followed their documentation for the CE and even the Enterprise Edition, looked high and low for solutions and blog and forum posts but it’s simply not working.

Some errors are in the log to throw you off course, like “validation.properties could not be loaded by any means.” from the ESAPI authentication system. This is actually harmless, and probably even to be expected.

Adding extra logging will help you debug as documented on the Pentaho CE Wiki, but this is definitely not as easy as it all should be.

I understand the Enterprise version has a nice Web GUI (like Atlassian’s products, and like Pentaho’s mail server configuration) which makes this a lot easier for you. Alas, for the people still testing this BI solution it’d be nice if this worked, too.

Luckily I have some knowledge of Java and frameworks and was able to find a lot more information by just looking at documentation of the frameworks Pentaho uses, such as Spring, more specifically their LDAP documentation. They seem to have a specific AD authenticator these days, but it’s not included with Pentaho.

So, after a few hours of cursing, here’s the way to do this. It was tested against a Samba4 AD domain, which should be 100% compatible with a Windows 2008 one.

Step one: Roll back everything you broke by following their wiki documentation. It’s all wrong, and will make you break all sorts of stuff which is not needed at all.

Step two: Fill out the appropriate properties files as detailed below.

Set the LDAP connection data. A user to bind to the directory is needed to be able to look up user DNs. I’ve created a group “Pentaho Administrators”; members of this group have administrative rights inside the BI server system. Note that their configuration panel calls this group “Administrator” no matter its name. Somewhat confusing.

The enterprise documentation mentions a special but well-known OID you can use to do recursive group searches, but it doesn’t work against Samba4 (at least the version I’m running) so I have not configured this.

biserver-ce/pentaho-solutions/system/applicationContext-security-ldap.properties

contextSource.providerUrl=ldaps\://yourdc.example.com\:636
contextSource.userDn=cn\=binduser,cn\=Users,dc\=your,dc\=domain,dc\=root
contextSource.password=bindpassword

userSearch.searchBase=cn\=Users,dc\=your,dc\=domain,dc\=root
userSearch.searchFilter=(sAMAccountName\={0})

populator.convertToUpperCase=false
populator.groupRoleAttribute=cn
populator.groupSearchBase=ou\=Groups,dc\=your,dc\=domain,dc\=root
populator.groupSearchFilter=(member\={0})
populator.rolePrefix=
populator.searchSubtree=true

allAuthoritiesSearch.roleAttribute=cn
allAuthoritiesSearch.searchBase=ou\=Groups,dc\=your,dc\=domain,dc\=root
allAuthoritiesSearch.searchFilter=(objectClass\=group)

allUsernamesSearch.usernameAttribute=sAMAccountName
allUsernamesSearch.searchBase=cn\=Users,dc\=your,dc\=domain,dc\=root
allUsernamesSearch.searchFilter=objectClass\=Person

adminRole=cn\=Pentaho Administrators,ou=Groups,dc\=your,dc\=domain,dc\=root
adminUser=cn\=Administrator,cn\=Users,dc\=your,dc\=domain,dc\=root

We can easily switch over to LDAP authentication:

biserver-ce/pentaho-solutions/system/security.properties

provider=ldap

The only thing that is somewhat odd is that it doesn’t see any groups below my Ou=Groups tree, even though searchSubtree is set to true.

Finally, to avoid the heaps of “User admin not found in directory.” (nested) exceptions in the logs, change the “admin” username for the Spring framework to “Administrator”. Somehow Pentaho requires this user to do … I don’t know. Perhaps only to nag about it. I’ve changed it to “Administrator” as I don’t have a user called “admin”. You could create one, of course, but what for… It seemed to work without this change, but the logs were bombarded with error messages.

biserver-ce/pentaho-solutions/system/repository.spring.properties

singleTenantAdminDefaultUserName=Administrator
singleTenantAdminUserName=Administrator
singleTenantAdminDefaultAuthorityName=Administrator
singleTenantAdminAuthorityName=Administrator
repositoryAdminUsername=pentahoRepoAdmin
singleTenantAuthenticatedAuthorityName=Authenticated
singleTenantAnonymousAuthorityName=Anonymous
superAdminAuthorityName=SysAdmin
superAdminUserName=super
systemTenantAdminUserName=system
systemTenantAdminPassword=cGFzc3dvcmQ=

I hope this helps you, if you’re stuck on this. LDAP/AD shouldn’t just be for the paid edition – in Observium we fully support this in the CE as well 😉

It took a few hours to debug all this, it’s a shame that their documentation sends you in the worst direction possible, as in the end, getting it to work is very easy and just means you need to fill out a few properties with the correct data.

Unfortunately I didn’t really keep track of all the weird errors I got when trying to get this to work – I would add them as extra Google bait if I did.

Update: it may seem unimportant, but it certainly is not: make sure Administrator (or whatever user you chose to use above) is part of the Pentaho Administrators group you configured! Otherwise the application seems to work OK at first sight, but storing/clearing recent files, creating data sources, etc will not work correctly and produce the following (or similar) error in your logs:

ERROR [BackingRepositoryLifecycleManagerAuthenticationSuccessListener] Access denied to this data; nested exception is javax.jcr.AccessDeniedException: Access denied.
org.springframework.security.AccessDeniedException: Access denied to this data; nested exception is javax.jcr.AccessDeniedException: Access denied.

Writing informative technical how-to documentation takes time, dedication and knowledge. Should my blog series have helped you in getting things working the way you want them to, or configure certain software step by step, feel free to tip me via PayPal (paypal@powersource.cx) or the Flattr button. Thanks!
  • Dave

    Thanks for the article – I have been scratching my head for days on this issue. I could get the LDAP authentication working but could not get any user to have admin rights on the server.

    Followed your examples on a clean biserver-ce installation. However I am getting an error on startup. “Error while trying to execute startup sequence for org.pentaho.platform.repository2.unified.BackingRepositoryLifecycleManagerSystemListener”

    Do you know where I could look to resolve this?

    • Tom Laermans

      Hi Dave,

      Odd – as far as I know these changes don’t break anything. However, the clue as to what is causing your issue is probably in the lines before or after that log line (exception name+error message+backtrace).

  • Dave

    Thanks Tom. My problem is less about getting LDAP to work because I have got authentication and BI Server logins working. My issue is about assigning users the Administrator role.

    It would appear version 5.0 has switched to a JackRabbit repository and that is configured quite differently from the old repo. For the life of me I cannot work out how to use LDAP/Jackrabbit and allow users Admin rights.

  • Guido Legemaate

    Thank you very much Tom. Been trying to get LDAP to work on my 5.0.1 CE installation and got stuck half way – indeed been following the EE manual and some information I found in various fora. Your approach was actually easier than I anticipated.

    At first I was greeted by a Tomcat 404 error, but this was do to a typo in biserver-ce/pentaho-solutions/system/security.properties

    Nevertheless, there is an issue that wasn’t resolved after following your tutorial. My Administrator user was correctly logged in as such, but was unable to install/upgrade marketplace components. This I resolved by changing pentaho-solutions/system/marketplace/settings.xml to:

    http://marketplace.pentaho.com/marketplace-plugins.xml
    http://marketplace.pentaho.com/telemetry-servlet/telemetry
    Administrator,Admin,YOUR_LDAP_ADMINISTRATOR_GROUP_HERE

    Credits go to user alucard1626 in this post http://forums.pentaho.com/showthread.php?153221-LDAP-configuration-migration-to-Pentaho-5-0-1-CE&p=367497#post367497

  • Mathew

    Hi Tom, thanks for the article. The admin rigths doesn’t work. Where i can map ldap users and roles?

    Thanks in advance

  • Tom Laermans

    Mathew, Not sure why your admin role doesn’t map – all I have done is in the blog post.

    adminRole=cn\=Pentaho Administrators,ou=Groups,dc\=your,dc\=domain,dc\=root

    This is a group my admin users are in, people in this group automatically become administrator in my Pentaho BI server when they log in.

    I can’t immediately tell if the marketplace works, I think it does, if it doesn’t, I’ll update the post again, thanks Guido!

  • Abhishek

    It still ain’t workin for me I have tried a lot of stuffs

  • Pingback: LDAP Abfragen unter Linux - Noch ein Blog ...()

  • Michele

    If had experienced this issue: “java.util.concurrent.ExecutionException: javax.jcr.LoginException: LoginModule could not perform authentication: Unprocessed Continuation Reference(s); nested exception is javax.naming.PartialResultException: Unprocessed Continuation Reference(s); remaining name ‘dc=mydomain,dc=local'”

    I resolved adding the ‘referral’ property by editing the file applicationContext-spring-security-ldap.xml as follows:

    —–

    <== ADDED THIS

    —-

  • Tom Laermans

    Hi Michele, I think something stripped away your XML (possibly WordPress doing some HTML filtering?) 🙁

    Well, or you forgot to paste it of course 😉

  • Michele

    Yes… It seems that wordpress just stripped down the xml tags. However what I did is trivial: just created a new entry in applicationContext-spring-security-ldap.xml like in the “Referrals” available here: http://infocenter.pentaho.com/help/index.jsp?topic=%2Fsecurity_guide%2Fconcept_active_directory_tips.html

    (hope urls won’t get filtered as well….) 🙂

  • Kishan Khatanhar

    I have Active Directory setup on Windows 2008 R2. I created an Organizational unit (OU) called “Pentaho” in “Active Directory Users and Computers”. I then created two groups within this OU named “Admins” and “Devs”. After that, I added myself (administrator) and a normal user into “Admins” and “Devs” groups respectively.

    I followed Pentaho help page at https://help.pentaho.com/Documentation/5.2/0P0/150/010/030

    The sixth step expects us to delete two Jackrabbit repository folders which I did, but getting errors because of missing repository folders.

    Error:
    Pentaho Initialization Exception
    org.pentaho.platform.api.engine.PentahoSystemException: PentahoSystem.ERROR_0014 – Error while trying to execute startup sequence for org.pentaho.platform..repository2.unified.BackingRepositoryLifecycleManagerSystemListener

    I know it’s possible to run Pentaho without Jackrabbit repository using Pentaho 5.2 CE. I digged a lot on google but no help.

    Any help is highly appreciated. Thank you in advance.

  • Puneet

    I have MS Active Directory setup on Windows 2008 R2. I created two groups Admins and Devs under the default group Users that comes in default. I added two users in Admins group with Administrator rights and one user in Devs group. I am trying to login with admininstrator user ‘kishank’ but I am getting the following error:

    [LDAP: error code 49 – 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db1 ]

    I googled upon this error which says invalid user or password. Here’s the link: It gives description about error code 49 DSID 0C0903A9. It says that the error only occurs on MS AD servers.

    I also read somewhere that I should uncheck “Password never expires” checkbox for users but no luck.

    My configuration files are as below:

    applicationContext-security-ldap.properties

    enter code herecontextSource.providerUrl=ldap://host:port/cn=Users,dc=domain,dc=com enter code herecontextSource.userDn=domain\kishank enter code herecontextSource.password=pass

    enter code hereuserSearch.searchBase=CN=Users,DC=domain,DC=com enter code hereuserSearch.searchFilter=(sAMAccountName={0})

    enter code herepopulator.convertToUpperCase=false enter code herepopulator.groupRoleAttribute=cn enter code herepopulator.groupSearchBase=cn=Devs,cn=Users,dc=domain,dc=com enter code herepopulator.groupSearchFilter=(memberof:1.2.840.113556.1.4.1941:=({0})) enter code herepopulator.rolePrefix= enter code herepopulator.searchSubtree=true

    enter code hereallAuthoritiesSearch.roleAttribute=cn enter code hereallAuthoritiesSearch.searchBase=cn=Devs,cn=Users,dc=domain,dc=com enter code hereallAuthoritiesSearch.searchFilter=(objectClass=group)

    enter code hereallUsernamesSearch.usernameAttribute=sAMAccountName enter code hereallUsernamesSearch.searchBase=cn=Devs,cn=Users,dc=domain,dc=com enter code hereallUsernamesSearch.searchFilter=objectClass=person

    enter code hereadminRole=cn=Admins,cn=Users,dc=domain,dc=com enter code hereadminUser=sAMAccountName=kishank,cn=Users

    repository.spring

    enter code heresingleTenantAdminDefaultUserName=kishank enter code heresingleTenantAdminUserName=kishank enter code heresingleTenantAdminDefaultAuthorityName=Administrator enter code heresingleTenantAdminAuthorityName=Administrator enter code hererepositoryAdminUsername=pentahoRepoAdmin enter code heresingleTenantAuthenticatedAuthorityName=Devs enter code heresingleTenantAnonymousAuthorityName=Anonymous enter code heresuperAdminAuthorityName=SysAdmin enter code heresuperAdminUserName=super enter code heresystemTenantAdminUserName=system enter code heresystemTenantAdminPassword=cGFzc3dvcmQ=

  • Puneet

    Please ignore “enter code” in previous comment.