Skip to main content
Hitachi VantaraPentaho Documentation
Pentaho Documentation

Define JNDI Connections for the BA Server

Add Drivers

The BA Server needs the appropriate driver to connect to the database that stores your data. Your database administrator, Chief Intelligence Officer, or IT manager should be able to provide the appropriate driver. If not, you can download drivers from your database vendor's website. The Supported Technologies section contains a list of drivers.

Once you have the correct driver, copy it to this directory for the BA Server: /pentaho/server/biserver-ee/tomcat/lib/.

There should be only one driver for your database in this directory. Ensure that there are no other versions of the same vendor's driver in this directory. If there are, back up the old driver files and remove them to avoid version conflicts. This is a concern when you are adding a driver for the same database type as your Pentaho BA repository. If you have any concerns about how to proceed, contact Pentaho support.

Driver for Microsoft SQL Server

For Microsoft Windows, most JDBC drivers support Type 2 integrated authentication through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to all machines and directories to which you copied the JDBC driver. You can find this file in this location.

<installation directory>\sqljdbc_<version>\<language>\auth\
If running: Use the sqljdbc_auth.dll file here:
32-bit Java Virtual Machine (JVM) even if the operating system is version x64 x86 folder
64-bit JVM on a x64 processor x64 folder
64-bit JVM on an Itanium processor IA64 folder

Specify JNDI Connection Information

Instructions for adding JNDI vary depending on which web application server you are using.

Tomcat JNDI Connections

  1. Stop the Tomcat and BA servers.
  2. Consult your database documentation to determine the class name and connection string for your database.
  3. Edit the /tomcat/webapps/pentaho/WEB-INF/web.xml file.
  4. At the end of the <web-app> element, in the same part of the file where you see <!-- insert additional resource-refs -->, add this XML snippet.
    <resource-ref>
        <description>myDataSource</description>
        <res-ref-name>jdbc/myDataSource</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
    Change the description and res-ref-name nodes, as well as any others that apply to your situation and fit your database. You may need to consult http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html to see if there are other things to consider.
  5. Save and close the web.xml file.
  6. Edit the /tomcat/conf/context.xml with a text editor. Alternatively, you can modify the /tomcat/webapps/pentaho/META-INF/context.xml file if you want this data connection to be available only to the BA Server. Adding JNDI connections to the context.xml makes them available to all of the webapps deployed to this Tomcat instance.
  7. Anywhere inside of the <Context> element, add this XML snippet. Then save and close the context.xml file.
    <Resource name="jdbc/myDataSource" 
        auth="Container" type="javax.sql.DataSource"
        factory="org.apache.commons.dbcp.BasicDataSourceFactory" 
        maxActive="20" 
        maxIdle="5"
        maxWait="10000" 
        username="dbuser" 
        password="password" 
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://127.0.0.1:5432/myDataSource"
    />   
    This example shows a simple PostgreSQL configuration. Replace the Resource name, username, password, driverClassName, and url parameters, or any relevant connection settings, to match your database connection information and the details you supplied in the web.xml file earlier.
  8. Delete the pentaho.xml filed located in the /tomcat/conf/catalina/directory. The pentaho.xml is a cached copy of the context.xml file you modified. Since the cache is not usually configured to update frequently, you have to delete the pentaho.xml file and let Tomcat recreate it when it starts up.
  9. Start the Tomcat and BA Server.
Tomcat can now properly connect to your data. Please go on to the next stop on the Guide Post graphic.

Install JDBC Driver as a Module in JBoss

In JBoss, JDBC driver information is stored in a module, which is an XML file that you create. You must download the JDBC driver software component to the correct directory, then create module.xml files for each database. You need to create a file for the database that hosts the BA Repository (either PostgreSQL, MySQL, or Oracle).
  1. Navigate to the pentaho/server/biserver-ee/<your jboss installation directory>/modules/system/layers/base/org folder and create one of the following paths for the database on which you are hosting the BA Repository.
    • PostgreSQL: postgresql/main
    • MySQL: mysql/main
    • Oracle: oracle/main
  2. Download the supported JDBC driver for your DI Repository database to the postgresql/main, mysql/main, or oracle/main directories (which ever one you created). See the JDBC Drivers Reference for a list of supported drivers.
  3. In the postgresql/main, mysql/main, or oracle/main (which ever one you created), do the following things.
    1. Use an editor to create a text file named module.xml.
    2. Copy the following code to the module.xml file, then modify it so that the name of the JDBC driver you just downloaded appears.
      • PostgreSQL: If you are using PostgreSQL, copy the following code in the module.xml file. Replace the name of the resource-root path parameter with the name of the JDBC driver you downloaded.
          <?xml version="1.0" encoding="UTF-8"?>
          <module xmlns="urn:jboss:module:1.0" name="org.postgresql">
              <resources>
                  <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
              </resources>
              <dependencies><module name="javax.api"/></dependencies>
          </module>  
      • MySQL: If you are using MySQL, copy the following code in the module.xml file. Replace the name of the resource-root path parameter with the name of the JDBC driver you downloaded.
          <?xml version="1.0" encoding="UTF-8"?>
          <module xmlns="urn:jboss:module:1.0" name="org.mysql">
              <resources>
                  <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
              </resources>
              <dependencies><module name="javax.api"/></dependencies>
          </module>  
      • Oracle: If you are using Oracle, copy the following code in the module.xml file. Replace the name of the resource-root path parameter with the name of the JDBC driver you downloaded.
            <?xml version="1.0" encoding="UTF-8"?>
            <module xmlns="urn:jboss:module:1.0" name="org.oracle">
                <resources>
                    <resource-root path="[Name of JDBC Jar You Downloaded Here]"/>
                </resources>
                <dependencies><module name="javax.api"/></dependencies>
            </module>  
    3. Save and close the module.xml file.

JBoss JNDI Connections

  1. Stop the JBoss and BA servers.
  2. Consult your database documentation to determine the class name and connection string for your database.
  3. Use a text editor to open the pentaho/server/biserver-ee/jboss/standalone/configuration/standalone.xml file.
  4. Append the file to include a definition for each of your data sources. Note that you should consult your JBoss documentation for proper setup information.
                    <datasource jndi-name="java:jboss/datasources/Hibernate" pool-name="hibpool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/hibernate
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    hibuser
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
                        <datasource jndi-name="java:jboss/datasources/Quartz" pool-name="quartzpool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/quartz
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    pentaho_user
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
                        <datasource jndi-name="java:jboss/datasources/Audit" pool-name="auditpool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/hibernate
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    pentaho_user
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
     <datasource jndi-name="java:jboss/datasources/Operations_Mart" pool-name="Operations_Mart" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                            <connection-url>
                                jdbc:postgresql://localhost:5432/hibernate
                            </connection-url>
                            <driver-class>
                                org.postgresql.Driver
                            </driver-class>
                            <driver>
                                org.postgresql
                            </driver>
                            <pool>
                                <prefill>
                                    false
                                </prefill>
                                <use-strict-min>
                                    false
                                </use-strict-min>
                                <flush-strategy>
                                    FailingConnectionOnly
                                </flush-strategy>
                            </pool>
                            <security>
                                <user-name>
                                    pentaho_user
                                </user-name>
                                <password>
                                    password
                                </password>
                            </security>
                        </datasource>
  5. If your environment (e.g. port numbers, IP address), solution repository, or database password and user name information differs from the code in the above example, modify it to match your specifications.
  6. Add the driver definition in the driver section of the file. Here is an example of the PostgreSQL driver definition. If you are using MySQL or Oracle, modify the driver name, module, and data source class accordingly.
     <driver name="org.postgresql" module="org.postgresql">
      <xa-datasource-class>
          org.postgresql.xa.PGXADataSource
          </xa-datasource-class>
      </driver>
  7. Close and save the standalone.xmlfile.
  8. Start the JBoss and BA Server.

Remove JNDI Resource References in JBoss

Because JBoss has its own mechanism for referencing JNDI data sources, the resource-references in the web.xml file located in the pentaho.war are not needed. You must remove these resource-references for the BA Server to operate properly.
  1. Navigate to the pentaho/server/biserver-ee/<your jboss installation directory>/standalone/deployments directory.
  2. Use a zip extraction utility (such as 7-Zip, Winzip, or Archive) to view the contents of the pentaho.war file. Do not unzip or extract the contents of the file.
  3. Navigate to the WEB-INF directory and open the web.xml file in a text editor.
  4. Delete all <resource-ref> tagged entries including everything between the <resource-ref> and </resource ref> tags.
  5. Save and close the file.
  6. The zip extraction utility that you used might show a prompt that asks whether you would like to update the file in the pentaho.war archive. If this happens, confirm that you would like to do this.

Update JNDI Data Source Reference to Conform to JBoss Standards

Update these files so that referenced JNDI datasources conform to JBoss standards.

  1. Use a text editor to open the pentaho/server/biserver-ee/pentaho-solutions/system/quartz/quartz.properties file.
  2. Change the org.quartz.dataSource.myDS.jndiURL value to jboss/datasources/Quartz, then save and close the file.
  3. Use a text editor to open the pentaho/server/biserver-ee/pentaho-solutions/system/audit_sql.xml file.
  4. Change the JNDI value to jboss/datasources/Hibernate, then save and close the file.
  5. Use a text editor to open the pentaho/server/biserver-ee/pentaho-solutions/system/data-access/settings.xml file.
  6. Change the data-access-staging-jndi value to jboss/datasources/Hibernate, then save and close the file.
  7. Open the pentaho/server/biserver-ee/pentaho-solutions/system/audit/dialects/ folder for your appropriate database. Use the text editor to open each file and make the following changes, using your appropriate database:
    • Change <database>Audit</database> to <database>jboss/datasources/Audit</database>.
    • Change <database>Hibernate</database> to <database>jboss/datasources/Hibernate</database>.
JBoss can now properly connect to your data. Please go on to the next stop on the Guide Post graphic.