Thursday, February 18, 2010

BI Server & MS Active Directory in 10 minutes

Recently I had the need to connect Pentaho to MS Active Directory for user authentication/authorization. Immediately I asked myself how to connect Pentaho BI Server to Microsoft Active directory and I answered "Oh don't worry... it will take no more than 10 minutes!". Then the "look for a how-to document..." discovery process started.

I found a lot of documentation about this issue (wiki articles, forums thread) but there isn't a well done survival guide on this problem (that's my opinion).

So I'll try to summarize in few lines the steps followed and problems encountered to try to build a sort of survival guide for anyone with the same issue to solve

Have a look at spring configuration files

BI Server security architecture is based on Spring Security so the first guide to be read is the Spring documentation where they talk about LDAP configuration. Better, in case you don't know anything about that, if you came a step backward and have a read at the general architecture of Spring security.

Spring security beans wires together through spring application context and in Pentaho all the needed spring application context files are located in <biserver_home>/pentaho-solutions/system. You'll find a lot of them there but the important things to know are:

pentaho-spring-beans.xml contains the list of imported spring bean files that will be loaded when BI Server will start.
We've two important file groups there named applicationContext-spring* and applicationContext-pentaho*. In each group, you have one file for every available authentication method defined in Pentaho. Usually the beans located in files belonging to applicationContext-spring group contains definitions for spring related beans needed to configure the specified authentication/authorization method. The beans located in files belonging to applicationContext-pentaho group contains definitions of Pentaho's beans involved in the authorization/authentication methods for the specific authentication method (LDAP, Hibernate, jdbc).

So how to configure Pentaho to work with MS Active Directory?

The setup to have Pentaho working with MS Active Directory is really simple if you know exactly what to do and to how. Il try to summarize you everything in the following paragraphs. As detailed above all the files we will mention are located in <biserver_home>/pentaho-solutions/system.

1. Setup an MS Active Directory user to let BI Server connect to. You need to define a user in MS Active Directory so that BI Server can connect to and check if the Pentaho's user is going authenticate exists and is valid. The user you're going to define in MS Active Directory doesn't need to have any special right so a normal user is the more appropriate. Remember to check that the "password never expire" flag is not set for this user.

2. Setup Spring Security files needed to enable LDAP Server authentication/authorization. This is a good point, I think the major one. First of all read the guidelines provided here  about some rules to be followed when editing Spring configuration files particularly regarding white spaces and special characters. Then follow the points detailed here.

2.a) Open the and change the properties accordingly to your needs. The useful thing about this file is that it contains all the properties needed to configure spring beans so that we doesn't need to look for them in eac xml file. They're all in one single place. Following you'll find an example:


Important things to note here are
  • contextSource.providerUrl - LDAP server url
  • contextSource.userDn - LDAP username. This is the user we've talked about in 1) above
  • contextSource.password - LDAP user password
  • the populator properties are needed by Spring DefaultLdapAuthoritiesPopulator. That object is needed to load the set of authorities the user was granted.
  • the userSearch properties configures the attributes needed to fills up the "users" box when assigning permissions to reports etc.
  • the allAuthorities properties configures the attributes needed to fills up the "roles" portion of the permissions box when setting them for a report etc.
The excerpt I gave above is fully working so you can copy and paste it in your properties file changing only the definitions specific to your installation. At this point you completed almost the 70% of the required configuration to have everything working on your system. Be careful to declare the full DN (domain name) when you work with MS Active Directory because, if not, it's almost sure you'll have an error like this (The first time I tried I had such an error for this reason)

<pre  style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> Microsoft Active Directory Error:  
   [LDAP: error code 49 - 80090308: LdapErr: DSID-0C09030B, comment:  
                        AcceptSecurityContext error, data 525, v893 ] 
     at com.sun.jndi.ldap.LdapCtx.mapErrorCode(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtx.processReturnCode(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtx.processReturnCode(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtx.connect(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtx.(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtxFactory.getUsingURL(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtxFactory.getUsingURLs(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtxFactory.getLdapCtxInstance(Unknown Source) 
     at com.sun.jndi.ldap.LdapCtxFactory.getInitialContext(Unknown Source) 
     at javax.naming.spi.NamingManager.getInitialContext(Unknown Source) 
     at javax.naming.InitialContext.getDefaultInitCtx(Unknown Source) 
     at javax.naming.InitialContext.init(Unknown Source) 
     at javax.naming.InitialContext.(Unknown Source) 
     at Source) 

You can find short but useful informations about this issue here

2.b) Two words about the default role. Anytime a user logs into Pentaho, the system assigns the user a default role of "Authenticated". This is a really important point to have all the things working. If that role isn't assigned you're able to connect but mantle witll never show anything to you. My problem was that in my spring LDAP context file the definitions to have that role assigned by default were missed. So I had to add them manually. So be absolutely sure to check that in applicationContext-spring-security-ldap.xml you have the defaultRole property defined with a value of Authenticated in the popolator bean and if missed add it. Below an excerpt of that bean definition with the defaultRole property added so that you can copy and paste if missed in your file.

 <bean id="populator" class="">  
  <!-- omitted -->  
  <property name="defaultRole" value="Authenticated" />  
  <!-- omitted -->  

3) Rework the imports in pentaho-spring-beans.xml to enable the load of LDAP security beans at startup. Below (at lines 8-9) we disabled the DAO/Hibernate security and (at lines 10-11) we imported the new definitions enabling LDAP security

1:  <beans>  
2:   <import resource="pentahoSystemConfig.xml" />  
3:   <import resource="adminPlugins.xml" />  
4:   <import resource="systemListeners.xml" />  
5:   <import resource="sessionStartupActions.xml" />  
6:   <import resource="applicationContext-spring-security.xml" />  
7:   <import resource="applicationContext-common-authorization.xml" />  
8:   <!-- import resource="applicationContext-spring-security-hibernate.xml" />  
9:   <import resource="applicationContext-pentaho-security-hibernate.xml" / -->  
10:   <import resource="applicationContext-spring-security-ldap.xml" />  
11:   <import resource="applicationContext-pentaho-security-ldap.xml" />  
12:   <import resource="pentahoObjects.spring.xml" />  
13:  </beans>  

4) Now its the time to define a set of groups in your MS Active Directory for your pentaho users (if you don't already have such a groups) depending on your authorization needs. For sure you need to have at least one group to contain pentaho admins. In my system I called that group PentahoAdmin.

5) Declare the new admin group in Pentaho configuration to assign that group the admin grant. To do that rework the acl-voter element in pentaho.xml as shown below.

           <!-- What role must someone be in to be an ADMIN of Pentaho -->  

6) Rework acl-publisher definitions in pentaho.xml for all the Pentaho's groups defined in the LDAP server. In my system I defined two roles PentahoAdmin and PentahoUser so my configuration looks like this

                These acls are used when publishing from the file system. Every folder  
                gets these ACLS. Authenticated is a "default" role that everyone  
                gets when they're authenticated (be sure to setup your bean xml properly  
                for this to work).  
                <acl-entry role="PentahoAdmin" acl="FULL_CONTROL" />                    <!-- Admin users get all authorities -->  
                <!-- acl-entry role="cto" acl="FULL_CONTROL" / -->                    <!-- CTO gets everything -->  
                <acl-entry role="PentahoUser" acl="EXECUTE_SUBSCRIBE" />          <!-- PentahoUser gets execute/subscribe -->  
                <acl-entry role="Authenticated" acl="EXECUTE" />          <!-- Authenticated users get execute only -->  
                These acls are overrides to specific file/folders. The above default-acls will  
                be applied and then these overrides. This allows for specific access controls to  
                be loaded when the repository if first populated. Futher changes to acls can be  
                made in the platform GUI tool. Uncomment these and change add or delete to your hearts desire -->                           
                <file path="/pentaho-solutions/admin">  
                     <acl-entry role="PentahoAdmin" acl="FULL_CONTROL" />  

7) Stop and restart you Pentaho server and everything is ready for a try.

I hope I haven't missed anything and that everything is clear enough for everyone who reads these few instructions.  Let me know if you have any problems so that I can keep updated this very brief guide.


  1. Excellent! Thanks, this finally gives me the confidence to "try it at home" :) Much appreciated.

  2. Thanks Sergio, very helpful post. I post a link on my blog

  3. Thanks for this blog post, it saved me quiet some time, especially mentioning the defaultRole.

  4. Great post! Helped me a lot at my work :)

  5. What happen with administration console? It still use old security mode and when I run biserver-ce and try to modified a data source from the site, they don't appear in the list. Additionally, when you created a new connection an exception occur. There is a big problem with security permission between administration console and the bi server.
    Any idea to configure admin console in the same way as biserver to connect to AD?

  6. Admin console security (user/password) is related to Jetty. AD is only for bi-server.

  7. Thanks to that tuturial,

    i have do this with my system, but i have some trouble. The server starts without any error message, the user can connect with their MSAD account, but No User (even the Admin) have any access to the Bussines View. The accounts of the admingroup also have no access to the Toolsmenu in BI-Server.

    The population of the MSAD works fine(http://servername:8080/pentaho/ServiceAction?action=SecurityDetails&details=all)

    I have the newest BI-Server (3.6.0.stable.41852)

    Any idea to look to?

  8. Great post. Some how my was missing a section for allUsernamesSearch. This was causing an error

    #### Error
    org.springframework.beans.factory.BeanDefinitionStoreException: Invalid bean definition with name 'allUsernamesSearch' defined in file [/opt/pentaho/biserver-ce/pentaho-solutions/system/applicationContext-pentaho-security-ldap.xml]: Could not resolve placeholder 'allUsernamesSearch.searchBase'

    ### Solution - Added these lines to the and restarted.


  9. Hi Sergio, have you had any luck connecting MSAD to Pentaho 5 CE?

    1. I don't had that need till now so I don't know but I don't foresee any problem :-)

  10. Hi Sergio,
    Firstly thank you for the great post. Everything worked for me but i got stuck at the end of process(step 5 and 6), assigning admin role to one of the MS Active Directory group. I'm using "biserver-ce-5.0.1-stable" and i followed the above mentioned step 5 and 6. After restarting my server i'm able to login through pentaho user console as a normal user but not as an Administrator though i'm a part of that group. Can you please tell me if i'm making mistake.


  11. Hi Sergio,
    I'm getting this error after login into pentaho user console and i can read the data(viewing charts) but don't have administrational rights to publish reports : Access denied to this data; nested exception is javax.jcr.AccessDeniedException: Access denied.
    at org.pentaho.platform.repository2.unified.jcr.sejcr.PentahoJcrTemplate.pentahoConvertJcrAccessException(
    Caused by: javax.jcr.AccessDeniedException: Access denied.
    at org.apache.jackrabbit.core.NodeImpl.addNode(

    1. @Sam not clear to me what is the problem. Write me privately at Thank you