Don’t hardcode host names, use JNDI

I got a e-mail earlier this week, asking for some help in migrating reports.

Hey Thomas,

I have a poop load of reports with hard coded 
ip addresses (yeah I know – but they didn't 
have DNS entries until now) that I'd like to 
globally change.  I'm thinking of something 
along the lines of "zcat | sed | gzip" but 
something like this would only work on 
compressed files, not an archive of files.  

So – I was wondering if you had any ideas, 
maybe even a utility based on code we already 
have to create and open these bad boys.  
We could even have some known locations to 

Like>  prpt-replace –dbConnectionHost "" ""

Or maybe use file and xpath to denote the 
attribute to change.

(The names involved were changed to protect the guilty.)

Well, hard-coding database connection information in every report is never a good idea in no environment. If your database host or credentials changes you are truly and well .. in trouble.

So how can you avoid this trouble in the first place?

Use JDNI to hold database connections in a external place

JDNI is a technique to hold database connections outside of the report definition. By holding this information in a central spot, you only have to change one place and all your reports get updated. The JNDI stored connection information is stored on the computer where your report runs. This also allows you to have separate definitions based on where the report runs. Your report designer can access a local copy of your production database, while your production server uses the real database server.

As a side note: On the BI-Server, this system of abstracting database information is called “Pentaho Datasources”, not JNDI. In this posting, I will forgo the branding exercise and simply continue to call it JNDI.

You can use JNDI in the following datasources:

  • SQL
  • Pentaho Analytics (Mondrian)
  • OLAP4J
  • Pentaho Metadata

For all of your production reports, you should always choose JNDI defined datasources. Manually defined datasources are great for testing out ideas, but really a nightmare to maintain and by storing the database credentials they potentially pose a security risk if you choose to share report definitions with others.

JDBC Connection definition basics

A database connection in Java requires four mandatory pieces of information:

  • driver: The name of the driver JDBC class.
  • url: The JDBC URL. This URL contains all information needed to connect to the database like the host name and port. The format of this URL is specific to a particular database. Consult your database manual for details on your particular database.
  • user: The username for the database.
  • password: The password for the accessing the database.

In addition to this, each driver allows you to specify additional properties. Again, these properties are specific to the database and (hopefully) documented in the manual. Most databases alternatively allow you to encode the properties onto the URL.

Defining data-sources in the Pentaho Report Designer

Within the report designer, there is no user interface to define the JNDI connections. The connections are defined in a file called "$HOME/.pentaho/simple-jndi/".

The file looks somewhat like this:

# Copyright 2008 Pentaho Corporation.  All rights reserved. 
# This software was developed by Pentaho Corporation and is provided under the terms 
# of the Mozilla Public License, Version 1.1, or any later version. You may not use 
# this file except in compliance with the license. If you need a copy of the license, 
# please go to The Original Code is the Pentaho 
# BI Platform.  The Initial Developer is Pentaho Corporation.
# Software distributed under the Mozilla Public License is distributed on an "AS IS" 
# basis, WITHOUT WARRANTY OF ANY KIND, either express or  implied. Please refer to 
# the license for the specific language governing your rights and limitations.

# NOTE: If you would like to access the running HSQLDB server, comment
#       out the above line and uncomment the following line 
# SampleData/url=jdbc:hsqldb:hsql://localhost/sampledata


# NOTE: If you would like to access the running HSQLDB server, comment
#       out the above line and uncomment the following line 
# SampleDataAdmin/url=jdbc:hsqldb:hsql://localhost/sampledata

The JDNI definition file holds all connections known to the local JNDI system. This system has no connection to the BI-Server and does not access any connections defined in the Pentaho Administration Console.

A JNDI database definition in the report designer is defined by a set of properties. Each property name has two parts: The name of the data-source (‘SampleData’ and ‘SampleDataAdmin’ in the example above) and the JDNI property name itself. Each datasource declared in the “simple-jndi” definition file requires the following properties:

  • Mandatory “type” entry set to the value “javax.sql.DataSource”
  • Driver class name
  • Connection URL
  • A username and password
  • .. and any additional property you want to pass in

The simplest way of creating a new definition entry is to copy an existing connection entry and to alter the name of the entry (the first part of the properties) and then to replace the connection information with your own details.

All JNDI connection names are case sensitive, so inside the report you have to use the same spelling for the name as in this properties-file.

And last but not least: Defining your connection information in the JNDI system does not relieve you from providing suitable database drivers. You can install new drivers by copying the jar files (and if needed any native libraries) into the “lib/jndi” directory of the Pentaho Report Designer.

Defining a JDNI connection in the Pentaho Administration Console

For the Pentaho BI-Server, the Pentaho architects chose not to use the JDNI subsystem that ships with all J2EE application servers. Sadly on the application servers JNDI has a few implementation flaws:

  • Redefining a JNDI connection requires you to redeploy or restart the application. This is a nightmare for a mission critical server.
  • Each server system comes with its own way to define JNDI definitions. Each of these ways is inherently incompatible with any other vendor.
  • You are bound to the implementations provided by the server vendor. There is little hope to customize the database access for additional capabilities like security checks or multi-tenancy.

Before you can setup a connection, you have to install the correct JDBC driver into both the BI-Server and the Pentaho Administration Console. For that copy the jar files into the following directories:

  • Pentaho BI Server: pentaho/tomcat/webapps/pentaho/WEB-INF/lib
  • Pentaho Administration Console: administration-console/jdbc

To define the connection, start the Pentaho Administration Console. The console starts up on port 8099, and can be reached via the URL “http://localhost:8099/”. The user and password are “admin” and “password” (sadly not documented anywhere in the download). The console can only be run successfully if the BI-Server is up and running as well.

To define a datasource, activate the “Administration” mode on the left hand side of the window and switch to the “DataSources” tab. Hit the small white “+” to add a connection.

The Pentaho Administration Console is smart enough to detect all installed drivers. If your driver is not listed here, you probably have forgotten to add it to the “lib” directories outlined above.

The data-source names you define here must match the datasource names defined for the Pentaho Report Designer. I also heavily recommend that you use the same type of database system for designing reports and for running them in production. SQL is a widely interpretable standard and therefore SQL written for one database is not necessarily compatible with the SQL dialect understood by the others.

Let me close with: Death to all hard-coded settings, long lives the host provided setting!

This entry was posted in Basic Topic on by .

About Thomas

After working as all-hands guy and lead developer on Pentaho Reporting for over an decade, I have learned a thing or two about report generation, layouting and general BI practices. I have witnessed the remarkable growth of Pentaho Reporting from a small niche product to a enterprise class Business Intelligence product. This blog documents my own perspective on Pentaho Reporting's development process and our our steps towards upcoming releases.

6 thoughts on “Don’t hardcode host names, use JNDI

  1. Doug

    That guy sounds like a real dumbass!

    It would be great if PRD could give you a 1 click change datasource to JNDI button and update the simple jndi file.

    Also – can PRD add the new data source to the BI Server on publish like Agile BI Does?

  2. Doug

    Also… You CAN use JNDI within the BI Server if you want to. The BI Server will check both JNDI and the Pentaho Datasources for a match to the JNDI name you used in your report and use whichever it finds.

  3. Thomas Morgner

    Making JNDI definitions more usable from within PRD is actually on my big list of items for 4.0 – and (thats probably more important: on my personal list of things that annoy me). I hate editing raw configuration files.

    Publishing datasources on the server? I never thought about that, as Marc B., the platform’s security guardian was always very strict on not letting anyone touch the secret database with the passwords. A publish feature would lift that JNDI thing to a whole new level.

    And last but not least: Can do does not mean it is sensible to do so, as we learned from the email quoted at the beginning 🙂

  4. Meherun

    How can I change JNDI definition for biserver-ce-5.0.1-stable?

    I found
    \biserver-ce-5.0.1-stable\biserver-ce\pentaho-solutions\system\simple-jndi\ file but changes are not effecting the server.

    1. ThomasThomas Post author

      AFAIK that file is not used. Login as an Admin user and define the datasource via the user console. The name of the datasource must match the JNDI name your report requests. (The Pentaho Info-Centre holds excellent documentation on this topic here )

      If you love pain, you could also skip the Pentaho datasources and define the JNDI datasources directly on the web-app context. But at that point, every change will require you to redeploy your web-app, and thus interrupt your users sessions. (Refer to the tomcat documentation for this path: )

Comments are closed.