BPM Admin Guide

Using MS SQL Server

This section describes how to use an SQL server as the solution repository.

Note: A repository database stores the metadata of the Pentaho server, and does not serve as the data source for the ETL. The steps in this guide assume you have followed the Pentaho Server Archive Installation up to step 4.

Workflow:

Components

  • Jackrabbit: Contains the solution repository, examples, security data and content data from reports for which you use the Pentaho software to create.
  • Quartz: Holds data related to scheduling reports and jobs.
  • Hibernate: Holds data related to audit logging.

Initialize SQL Server Pentaho Repository Database

To initialize SQL Server so that it serves as the Pentaho Repository, you will need to run several SQL scripts to create Hibernate, Quartz, and Jackrabbit (JCR)

Step 1: Adjust MS SQL Server Configuration Settings

Configure the following MS SQL Server settings in Microsoft SQL Server Management Studio or another tool of your choice.

  1. Select SQL Server and Windows Authentication Mode​ to use mixed authentication.
    1. Open the SQL Server Management Studio.
    2. Log in with either your Windows Account or SQL Server account.
    3. On the left side, right-click the SQL Server instance
    4. Select Properties » Security.
    5. Under Server Authentication, select SQL Server and Windows Authentication Mode​.

  2. Enable TCP/IP for MS SQL Server.
    1. Open the SQL Server Configuration Manager.
    2. From the menu panel, navigate to the SQL Server Network Configuration.
    3. Select Protocols for <SQL Server instance>.
    4. Right-click TCP/IP then select Enable.
  3. Make sure that MS SQL Server is listening on an external IP, and not the localhost.

Step 2: Change Default Passwords

For the production server, follow best practices to change the default passwords in the following SQL script files to secure the databases.

  1. Navigate to the <pentaho_directory>/pentaho-server/data/sqlserver folder.
  2. Use any text editor to modify the following CREATE scripts as needed according to the existing setup for the user, password, database and other required information:
    • create_jcr_sqlServer.sql
    • create_quartz_sqlServer.sql
    • create_repository_sqlServer.sql
  3. Save and close the files.

Step 3: Run SQL Scripts

Procedure:

  1. Open a command prompt window.
  2. Run the following scripts in the order listed below:
    • sqlcmd -S <host> -U <username> -P <password> -i "<pentaho_directory>/pentaho-server/data/sqlserver/create_quartz_sqlServer.sql"
    • sqlcmd -S <host> -U <username> -P <password> -i "<pentaho_directory>/pentaho-server/data/sqlserver/create_repository_sqlServer.sql"
    • sqlcmd -S <host> -U <username> -P <password> -i "<pentaho_directory>/pentaho-server/data/sqlserver/create_jcr_sqlServer.sql"
  3. Log into the SQL Server Management Studio.
  4. Navigate to Security » Logins and verify that the following users exist:
    • hibuser
    • jcr_user
    • pentaho_user
Note: If you did not modify the scripts, the default password for all the new database users is password.

Configure the SQL Server Pentaho Repository Database

After initializing the repository database, you must configure Quartz, Hibernate and Jackrabbit for an SQL Server database. Make sure that you change the password and port number in these examples to match the ones in your configuration.

Step 1: Set Up Quartz on SQL Server

  1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties file in any text editor.
  2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown:

    org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore. MSSQLDelegate

  3. Save the file and close the text editor.

Step 2: Set Hibernate Settings for SQL Server

  1. Open the hibernate-settings.xml file in a text editor.
  2. Find the <config-file> tags and change postgresql.hibernate.cfg.xml to sqlserver.hibernate.cfg.xml as shown:

    From: <config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

    To: <config-file>system/hibernate/sqlserver.hibernate.cfg.xml</config-file>

  3. Save and close the file.

Step 3: Replace Default Version of Audit Log File with SQL Server Version

  1. Locate the pentaho-solutions/system/dialects/sqlserver/audit_sql.xml file.
  2. Copy it into the pentaho-solutions/system directory.

Step 4: Modify Jackrabbit Repository Information for SQL Server

  1. Download PentahoServer-config.zip.
  2. Copy sqlServer_repository.xml from /config/jackrabbit/sqlServer.
  3. Place sqlServer_repository.xml into pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit.
  4. Back up and delete the repository.xml file in the same folder as the previous step.
  5. Rename sqlServer_repository.xml to repository.xml

Perform Tomcat-Specific Connection Tasks

After your repository has been configured, you must configure the web application servers to connect to the Pentaho Repository. In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

Step 1: Download Driver and Apply to Pentaho Server

To connect to a database, including the Pentaho repository database, you must download and copy a JDBC driver to the appropriate locations for the Pentaho server, as well as on the web application server.

  1. Download a JDBC Driver JAR from your database vendor or a third-party driver developer:

    JDBC and UCP Downloads page

  2. Copy the JDBC driver JAR downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.

Step 2: Modify JDBC Connection Information in the Tomcat XML File

  1. Download PentahoServer-config.zip.
  2. Copy sqlServer_context.xml from /config/tomcat/sqlServer.
  3. Place sqlServer_context.xml to the /pentaho-server/tomcat/webapps/pentaho/META-INF.
  4. Backup and delete the context.xml file in the same folder as the previous step.
  5. Rename sqlServer_context.xml to context.xml.
  6. Open the new context.xml file with any text editor.
  7. Replace all the password="password" with the corresponding database user's password.
  8. Replace all the url="jdbc:sqlserver://localhost:1433 value with the correct host and port.
  9. Save the context.xml file, then close it.
    Warning:

    After changing to another database type (for example, PostgreSQL to Oracle) make sure to clean up the following cache:

    • \pentaho-solutions\system\jackrabbit\repository\*
    • \pentaho-solutions\system\karaf\cache\*

    Otherwise, it could result in unpredictable errors like "java.sql.SQLSyntaxErrorException: ORA-00906: missing left parenthesis" as it tries to use the older syntax for the new database type

Start Your Server

Now that you have completed the initial Pentaho archive installation steps, you are ready to start the Pentaho Server. Follow these steps:

  1. Navigate to the <pentaho_directory>/server/pentaho-server folder.
  2. Run the startup script, start-pentaho.bat.
  3. From a workstation, open a web browser and enter http://<hostname>:<port>/pentaho in the address bar.
    Note: The default port is 8080.
  4. In the the Pentaho User Console (PUC), enter your username and password then click Login.