Launching and setting up Pentaho server with MSSQL database

This article describes the steps to install MS SQL as the host database for a Pentaho server in a Pentaho archive installation.

Set Java and Environment Variables

You need to install version 11 of Java. Perhaps it will work with other versions too.

Create or edit variables

There must be such.

PENTAHO_JAVA_HOME= C:\Program Files\Java\jdk-11

JAVA_HOME= C:\Program Files\Java\jdk-11

Important.

This must be done first. If you change the environment variables after starting the Pentaho server. You will receive an error the next time you start the Pentaho server.

This is due to the fact that when the server is first launched, it creates settings from the \pentaho-server\pentaho-solutions\system\karaf folder. When changing variables, the settings are lost. The cure is either reinstalling the server or replacing the karaf pack with a fresh one from the archive.

Creating databases in MS SQL Server Pentaho

To initialize MS SQL Server to serve as a Pentaho repository, you will need to run several SQL scripts to create databases on the server

• Hibernate

• Quartz

• Jackrabbit

Attention! When running these scripts, use the ASCII character set. Do not use UTF-8 as there are limits on the length of a text string that may cause scripts to fail.

Step 1: Configure MS SQL Server configuration settings.

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

• Select SQL Server and Windows authentication mode to use mixed authentication.

• Enable TCP/IP for MS SQL Server.

• Check that the port is 1433

Step 2. Creating databases on the server

Run the SQL scripts listed in the table below.

Scripts for creating a database are located in the \pentaho-server\data\sqlserver folder

Run these scripts from the sqlcmd utility window or from Microsoft SQL Server Management Studio.

Create Quartz -i <путь к файлу DDL>/create_quartz_sqlServer.sql

Create Hibernate -i <путь к файлу DDL>/create_repository_sqlServer.sql

Create Jackrabbit -i <путь к файлу DDL>/create_jcr_sqlServer.sql

Step 3: Check MS SQL Server Initialization

After running the scripts, follow these steps to ensure that the databases and user roles are created:

1. Open MS SQL Server Management Studio.

2. In the Object Browser section of the window, make sure that the Quartz, Jackrabbit, Hibernate databases are present.

3. Go to the Secure Logins section and ensure that the appropriate users have been created.

4. Exit MS SQL Server Management Studio tool.

Download the driver and apply it to the Pentaho server

To connect to a database, including the Pentaho repository database, you need to download the JDBC driver.

Important: official JDBC drivers from Microsoft could not be connected. Pentaho doesn’t want to work with them.

The JTDS driver will be used download https://sourceforge.net/projects/jtds/files/

Version jtds-1.3.1.jar

Download the driver and put it in the tomcat lib folder at pentaho-server\tomcat\lib

Setting up MS SQL Server Pentaho repository databases

We edit files to configure Quartz, Hibernate, Jackrabbit for the MS SQL Server database.

We edit files in the Quartz, Hibernate, Jackrabbit folders that are

pentaho-server\pentaho-solutions\system

Step 1: Setting up Quartz on MS SQL Server

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must specify where the JobStore is located by editing the quartz.properties file.

1. Open the file pentaho-server/pentaho-solutions/system/quartz/quartz.properties in any text editor.

2. Find the #_replace_jobstore_properties section and set org.quartz.jobStore.driverDelegateClass as shown:

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

3. Find the section #Configuring Data Sources and set org.quartz.dataSource.myDS.jndiURL to Quartz as shown (usually this setting is already set):

   org.quartz.dataSource.myDS.jndiURL = Quartz

4. Save the file and close the text editor.

Step 2. Configuring Hibernate for MS SQL Server

Step 1

Modify the Hibernate configuration file to specify where Pentaho should find the Pentaho repository Hibernate configuration file. The Hibernate configuration file specifies driver and connection information, as well as dialects and how connection closures and timeouts are handled.

The files in this section are located in the pentaho-server/pentaho-solutions/system/hibernate directory.

Follow these steps to specify where Pentaho can find the Hibernate configuration file.

1. Open the hibernate-settings.xml file in a text editor

2. Find the tags and change

   system/hibernate/postgresql.hibernate.cfg.xml

   on

   system/hibernate/sqlserver.hibernate.cfg.xml

3. Save and close the file.

Step 2

Change the configuration file.

Find the line in the configuration file

com.microsoft.sqlserver.jdbc.SQLServerDriver

Change to

net.sourceforge.jtds.jdbc.Driver

Comment out the line

And

Step 3: Setting up Jackrabbit for MS SQL Server

Edit the following code to change the default Jackrabbit repository to MS SQL Server.

Go to pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file in any text editor.

This file contains settings for MySQL, Oracle, MS SQL, PostgreSQL and the default database HSQLDB.

At the same time, the settings for MySQL, Oracle, MS SQL, PostgreSQL are commented out, and for HSQLDB they are uncommented.

The task is to comment out the lines related to HSQLDB and uncomment the lines for mssql.

Step 4. Change the connection

Editing the applicationContext-spring-security-hibernate.properties file

At pentaho-server\pentaho-solutions\system\ applicationContext-spring-security-hibernate.properties

Remove everything from the file and paste

jdbc.driver=net.sourceforge.jtds.jdbc.Driver

jdbc.url=jdbc:jtds:sqlserver://localhost/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

Editing Tomcat files

Change JDBC connection information in Tomcat XML file

Database connection information and network information such as username, password, driver class information, IP address or domain name, and port numbers for your Pentaho repository database are stored in a file context.xml Modify this file to reflect the database connection and network information for your operating environment.

Attention! If you have a user or password, be sure to change the password and port number in these examples to match those specified in your configuration environment.

1. Go to the directory pentaho-server/tomcat/webapps/pentaho/META-INF and open the context.xml file in any text editor.

2. Replace the code in the file with the following.

   <Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="hibuser" password="password"

   driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://localhost/hibernate" validationQuery=" select 1" />                     

   <Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="pentaho_user" password="password"

   driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://localhost/quartz" validationQuery="select 1"/>

    <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
username="jcr_user" password="password"

   driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://localhost/jackrabbit" validationQuery="select 1"/>

Disable the launch of the HSQLDB database

1. Go to the pentaho-server/tomcat/webapps/pentaho/WEB-INF directory and open the web.xml file in any text editor.

2. Comment out the lines

hsqldb-databases

sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz

AND

org.pentaho.platform.web.http.context.HsqldbStartupListener

Launch

We launch the file start-pentaho.bat from the root folder. The first launch takes a long time because the server is being configured.

Important: Tomcat starts on port 8005; if tomcat does not start (it shuts down immediately), port 8005 may be busy.

Edit in the pentaho-server \tomcat\conf.xml file

String

Replace port=”8005″ to a free port for example 8006