Data Source Troubleshooting

#JDBC Connection Logging
#log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JndiJdbcDataSourceService=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.jasperserver.war.action.ReportDataSourceAction=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.commons.datarator.JdbcDataSet=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.jasperserver.war.common.JasperServerUtil=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.commons.semantic.dsimpl.JdbcDataSetFactory=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.commons.semantic.metaapi.impl.jdbc.BaseJdbcMetaDataFactoryImpl=DEBUG, stdout, fileout
#log4j.logger.com.jaspersoft.jasperserver.war.validation.ReportDataSourceValidator=DEBUG, stdout, fileout

Database Driver

Ensure Driver is in the Classpath

JasperReports Server ships with drivers for several databases, including MySQL, Oracle, PostgreSQL, etc. It is important to ensure that the drivers for your database are included in the JasperReports Server classpath.

Ensure that the Driver Class Name is correct

The following are some example driver class names for various databases:

Database Privileges

When creating Database users, you must ensure that the user has the appropriate permissions to your database as well as from the server that JasperReports Server is running on.

Permissions Required to the Database

The database user that is used within your data source definition should have the appropriate select permissions to query the tables within your database for the reports you wish to generate.  The easiest way to troubleshoot this is to use a tool, such as SquirrelSQL or other database query tool, to run various queries against your database.

Ensuring Permission from JasperReports Server

Many databases, including MySQL, also require the user grants to include the specific host from which connections are allowed. Otherwise, when testing the JDBC connection, a connection may not be allowed even though the username and password are correct.  For example, you may refer to the following page for Setting up MySQL users: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

An fairly easy way to test connectivity from the server to that database location with a particular user is to use a tool such as SquirrelSQL or another DB query tool to connect to the database from the same host as JasperReports Server.

JDBC Data Source Issues

Ensure Database URL is correct

Ensure that the Database URL string entered when defining your JDBC Connection is consistent with what is required for your specific database and database driver.

JNDI DataSource Issues

JNDI is handled by your application server, check the documentation of your application server for help with JNDI data sources.

Tomcat

• Ensure the driver for your database connection is in the <tomcat>/lib folder.

• Ensure you've created entries in both the context.xml and web.xml files. Here is an example for a mysql JNDI connection:

  <resource auth="Container"
            driverclassname="com.mysql.jdbc.Driver"
            maxactive="100"
            maxidle="30"
            maxwait="10000"
            name="jdbc/YOUR_DATABASE"
            password="YOUR_PASSWORD"
            testonborrow="true" type="javax.sql.DataSource"
            url="jdbc:mysql://YOUR_HOST:3306/YOUR_DATABASE?autoReconnect=true&amp;autoReconnectForPools=true"
            username="YOUR_USERNAME"
            validationquery="SELECT 1">
  </resource>
  

  In <tomcat>/webapps/jasperserver-pro/WEB-INF/web.xml:

  <resource-ref>
      <description>JNDI Example</description>
      <res-ref-name>jdbc/YOUR_DATABASE</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
  

• If you installed JasperReports Server from a WAR file, Tomcat may have created a separate copy of context.xml in <tomcat>/conf/Catalina/Localhost/jasperserver-pro.xml. See the Install Guide's Troubleshooting Section Special Configuration Case under Tomcat for more detail.

• Also see the Tomcat documentation for JNDI data sources.

• Glassfish > Oracle GlassFish Server Administration Guide > Administering the Java Naming and Directory Interface (JNDI) Service

Oracle

Configuring a JNDI entry should be straightforward - start by copying an existing Resource entry and modifying it to use the right driver and URL. The big "gotcha" to watch out for is the value for the validationQuery attribute; MySQL and Postgres can take "SELECT 1" but Oracle cannot; instead use "SELECT 1 FROM DUAL" and that should work.

MsSQL

Configuring MsSQL involves many things and enumerating all possible source of issue would be too long to be described here and generally unrelated with JasperReports.

Situation:

One thing though which can happen is, in the situation when you use MsSQL setup to use windows authentication credentials and thus you don't need to pass any username or password in the JNDI connection, you may get a connection failure due to the way the Windows Service which starts JasperReports Server is setup. For example if the MsSQL Database is located on a different server than JasperReports Server, the credentials passed for the Windows authentication will be, by default, the one from the server which runs the server and not from the server which runs the database. If this situation happens, the logs (when setup the way described above) will show that the server name is passed as the username.

Fix:

To fix this you need to restart your server without using the Windows service or to change the log on properties of the service from local to the other account.

Also See

• JasperReports Server > Data Sources > Troubleshooting