Encrypted database passwords in BI
This section describes the steps needed to use encrypted password with Business Intelligence database.
Encrypt the passwords
The first thing you have to do is to encrypt the passwords:
1. Locate the encryptPassword.bat file in bi\server\pentaho-server\pentaho-solutions\qmaticbi\platform\encryptPassword folder.
2. Run the encryptPassword.bat file and enter the password as the input to encrypt.
Modify context.xml
1. Locate the context.xml file in bi\server\pentaho-server\tomcat\webapps\businessintelligence\META-INF folder.
2. Introduce the encrypted passwords which were generated using the encryptPassword.bat file.
3. Change the factory attribute to com.qmatic.pentaho.extensions.jdbc.EncryptedDataSourceFactory in all encrypted connections
4. Introduce a new connection with the name jdbc/Jackrabbit
Note that this is an MS SQL Server connection
After modifying:
<Resource
name="jdbc/Jackrabbit"
auth="Container"
type="javax.sql.DataSource"
factory="com.qmatic.pentaho.extensions.jdbc.EncryptedDataSourceFactory" maxActive="20"
minIdle="0"
maxIdle="5"
initialSize="0"
maxWait="10000"
username="jcr_user"
password="encrypted-password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://localhost:1433;DatabaseName=jackrabbit"
validationQuery="select 1"
/>
Modify repository.xml
1. Locate the repository.xml file in bi\server\pentaho-server\pentaho-solutions\system\jackrabbit folder.
2. Change the driver value to javax.naming.InitialContext in all entries.
3. Change the url value to java:comp/env/jdbc/Jackrabbit in all entries.
4. Remove all the user and password entries.
Note that this is only one setting, and you should configure the rest of the file too.
Before modifying:
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
<param name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<param name="url" value="jdbc:sqlserver://localhost:1433;databaseName=jackrabbit"/>
<param name="user" value="jcr_user"/>
<param name="password" value="password"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>
After modifying:
<FileSystem class="org.apache.jackrabbit.core.fs.db.MSSqlFileSystem">
<param name="driver" value="javax.naming.InitialContext"/>
<param name="url" value="java:comp/env/jdbc/Jackrabbit"/>
<param name="schema" value="mssql"/>
<param name="schemaObjectPrefix" value="fs_repos_"/>
</FileSystem>
Modify hibernate-settings.xml
1. Locate the hibernate-settings.xml file in bi\server\pentaho-server\pentaho-solutions\system\hibernate folder.
2. Find the specified configuration file in hibernate-settings.xml.
3. Remove connection.url, connection.username and connection.password properties.
4. Introduce following property:
<property name="hibernate.connection.datasource">java:comp/env/jdbc/Hibernate</property>
Note that this is a MS SQL Server configuration.
After modifying:
<hibernate-configuration>
<session-factory>
<property name="cache.provider_class">net.sf.ehcache.hibernate.SingletonEhCacheProvider</property>
<property name="hibernate.generate_statistics">true</property>
<property name="hibernate.cache.use_query_cache">true</property>
<!-- SQL Server Configuration -->
<property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.datasource">java:comp/env/jdbc/Hibernate</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="connection.pool_size">10</property>
<property name="show_sql">false</property>
<property name="hibernate.jdbc.use_streams_for_binary">true</property>
<!-- replaces DefinitionVersionManager -->
<property name="hibernate.hbm2ddl.auto">update</property>
<!-- load resource from classpath -->
<mapping resource="hibernate/sqlserver.hbm.xml" />
<!-- mapping resource above is from CE; below is from EE -->
<mapping resource="hibernate/sqlserver.EE.hbm.xml" />
</session-factory>
</hibernate-configuration>
Restart Business Intelligence
After completing all the configurations, restart the QP Business Intelligence service.