Adding a JDBC Driver

Before you can connect to a data source in any Pentaho server or client tool, you must first install the appropriate database driver. Your database administrator, CIO, or IT manager should be able to provide you with the proper driver JAR. If not, you can download a JDBC driver JAR file from your database vendor or driver developer's Web site. Once you have the JAR, follow the instructions below to copy it to the driver directories for all of the BI Suite components that need to connect to this data source.

Note: Microsoft SQL Server users frequently use an alternative, non-vendor-supported driver called JTDS. If you are adding an MSSQL data source, ensure that you are installing the correct driver.

Backing up old drivers

You must also ensure that there are no other versions of the same vendor's JDBC driver installed in these directories. If there are, you may have to back them up and remove them to avoid confusion and potential class loading problems. This is of particular concern when you are installing a driver JAR for a data source that is the same database type as your Pentaho solution repository. If you have any doubts as to how to proceed, contact your Pentaho support representative for guidance.

Installing JDBC drivers

Copy the driver JAR file to the following directories, depending on which servers and client tools you are using (Dashboard Designer, ad hoc reporting, and Analyzer are all part of the BI Server):

Note: For the DI Server: before copying a new JDBC driver, ensure that there is not a different version of the same JAR in the destination directory. If there is, you must remove the old JAR to avoid version conflicts.

BI Server: /pentaho/server/biserver-ee/tomcat/lib/
Enterprise Console: /pentaho/server/enterprise-console/jdbc/
Data Integration Server: /pentaho/server/data-integration-server/tomcat/webapps/pentaho-di/WEB-INF/lib/
Data Integration client: /pentaho/design-tools/data-integration/libext/JDBC/
Report Designer: /pentaho/design-tools/report-designer/lib/jdbc/
Schema Workbench: /pentaho/design-tools/schema-workbench/drivers/
Aggregation Designer: /pentaho/design-tools/agg-designer/drivers/
Metadata Editor: /pentaho/design-tools/metadata-editor/libext/JDBC/

Note: To establish a data source in the Pentaho Enterprise Console, you must install the driver in both the Enterprise Console and the BI Server or Data Integration Server. If you are just adding a data source through the Pentaho User Console, you do not need to install the driver to Enterprise Console.

Restarting

Once the driver JAR is in place, you must restart the server or client tool that you added it to.

Connecting to a Microsoft SQL Server using Integrated or Windows Authentication

The JDBC driver supports Type 2 integrated authentication on Windows operating systems through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to all the directories to which you copied the JDBC files.

The sqljdbc_auth.dll files are installed in the following location:

<installation directory>\sqljdbc_<version>\<language>\auth\

Note: Use the sqljdbc_auth.dll file, in the x86 folder, if you are running a 32-bit Java Virtual Machine (JVM) even if the operating system is version x64. Use the sqljdbc_auth.dll file in the x64 folder, if you are running a 64-bit JVM on a x64 processor. Use the sqljdbc_auth.dll file in the IA64 folder, you are running a 64-bit JVM on an Itanium processor.