Maintenance

Moving database server

Use these step-by-step instructions if you are moving the database used by TrustView.

Step 1

TrustView uses an Apache Tomcat to host the application. First locate the Tomcat installation directory, typically:

Directory path

C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView

Tip

If you are unable to locate the Apache Tomcat directory, contact our for assistance.

Step 2

The database connection URL can be located in one of two places, depending on whether your installation of TrustView is configured to use JNDI or not.

Depending on the type of database, the connection URL may need to be adjusted, but it can also change if the driver type is changed.

Step 2a (JNDI case)

If your installation is using JNDI for database configuration, the file Tomcat-install-dir/conf/context.xml contains the JDBC URL in the URL attribute of the Resource element, e.g.:

JDBC URL example (JDNI case)

<Resource
name="jdbc/trustview"
...
url="jdbc:jtds:sqlserver://dbhost:1433;databaseName=trustviewdb;useNTLMv2=true;domain=CONTOSO"
...
/>

Now simply change the dbhost to the new database host and save the file.

Step 2b (non-JNDI case)

If your installation is not using JNDI for the database resource, the file

Tomcat-install-dir/lib/TrustView.groovy contains the JDBC URL for the database connection:

JDBC URL example (non-JDNI case)

dataSource {
...
url = "jdbc:jtds:sqlserver://dbhost:1433;databaseName=trustviewdb;useNTLMv2=true;domain=CONTOSO"
...
}

Now simply change the dbhost to the new database host and save the file.

Step 3 (SQL Server Express)

If using a SQL Express database, the instance name needs to be added.

JDBC URL example for SQL Express database

url="jdbc:jtds:sqlserver://dbhost:1433;databaseName=trustviewdb;instance=SQLEXPRESS;user=dbuser"

See Building the connection URL for more information on building case-specific connection URLs.

Step 4

Finally, you must restart the Apache Tomcat service for the change to take effect. TrustView is now connecting to the database on the new database host.

Setup Microsoft JDBC Driver

To use the Microsoft JDBC drivers for the database connection, follow these steps:

Step 1

Download the newest version of the drivers from Microsoft: Download Microsoft JDBC Driver.

Step 2

Copy the file mssql-jdbc_auth-<version>.x64.dll to C:\Program Files\Eclipse Adoptium\jdk-<version>\bin

Copy the file mssql-jdbc-<version>.jre8.jar to C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\lib

Important

The directories may differ depending on where your TrustView and Java installation folders are placed. The Java used may also not be Eclipse Adoptium, but another version, and therefore the directory may be named differently, but the jre and bin folder, should still be present.

Step 3

Replace the line with the default Java JDBC driver class in the context.xml file, located in the conf folder, to Microsoft:

Driver class to use for Microsoft JDBC drivers

driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

Step 4

Configure the JDBC URL (or adjust it, if already configured with the default Java JDBC driver) in the context.xml file:

JDBC URL example for the SQL database and the new Microsoft JDBC drivers

url="jdbc:sqlserver://dbhost:1433;databaseName=trustviewdb;integratedSecurity=true;authenticationScheme=NTLM;user=dbuser;password=dbpassword"

Step 5

Finally, you must restart the Apache Tomcat service for the change to take effect. TrustView will now be connecting to the database on the database host with the Microsoft JDBC drivers.

Troubleshooting

If the following error is seen in the trustview logfile:

Error message that may occur during the establishment of the database connection

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption

The following options should be added to the JDBC URL:

Error message that may occur during the establishment of the database connection

encrypt=true;trustServerCertificate=true

In some instances, it may also be necessary to remove the user=dbuser and password=dbpassword from the JDBC URL if it has trouble connecting with the Microsoft JDBC drivers.

How to use gMSA for the database connection

If you want to use gMSA (Group Managed Service Accounts) for the TrustView database connection, you can do so by setting up such an account gMSA overview.

Next you need to attach it to the TrustView service by adding it under Log On {octicon}`arrow-right;1em;` This account under Properties of the service.

Then you need to update the JDBC URL found in the context.xml file in the conf directory.

Remove the lines with Username and Password from the context.xml file, as that is not necessary with a gMSA.

JDBC URL example with an gMSA user

url="jdbc:sqlserver://dbhost:1433;databaseName=trustviewdb;integratedSecurity=true;authenticationScheme=NativeAuthentication;encrypt=true;trustServerCertificate=true"

Note

The Authentication scheme of authenticationScheme=NativeAuthentication and authenticationScheme=JavaKerberos can be used. Just select one of the methods.

Add database performance logging section

Use these step-by-step instructions if you need to monitor the performance of the database used by TrustView.

Step 1

Locate the TrustView.groovy file in the TrustView installation directory, typically:

Directory path to the TrustView configuration file

     C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\lib\TrustView.groovy

Add and enable the generate_statistics setting to the TrustView.groovy file:

Example of enabling additional logging in the TrustView configuration file

hibernate {
    ...
        generate_statistics = true
    ...
}

Step 2a - logback.xml

If you are using xml-based logging (logback.xml), then locate the file and add the following appender:

Directory path of the logback.xml logging configuration file

     C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\conf\logback.xml

Example of how to add the appender in the logback.xml configuration file

<appender name="DB_PERFORMANCE" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <fileNamePattern>${LOG_ROOT}/trustview-db-%d{yyyy-MM-dd}.log</fileNamePattern>
    </rollingPolicy>
    <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
        <fileNamePattern>${LOG_ROOT}/trustview-db-%d{yyyy-MM-dd}.%i.log.zip</fileNamePattern>
        <maxFileSize>250MB</maxFileSize>
        <maxHistory>60</maxHistory>
        <totalSizeCap>500MB</totalSizeCap>
    </rollingPolicy>
    <encoder>
        <pattern>%clr(%d{yyyy-MM-dd HH:mm:ss.SSS}){faint} %clr(%5p) %clr(---){faint} %clr([%15.15t]){faint} %clr(%-40.40logger{39}){cyan} %clr(:){faint} %m%n%wex</pattern>
    </encoder>
</appender>

Add the following loggers to the logback.xml file as well:

Additional logging appender that should be added to the logback.xml configuration file

<logger name="org.hibernate.SQL" level="DEBUG" additivity="false">
    <appender-ref ref="DB_PERFORMANCE"/>
</logger>
<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE" additivity="false">
    <appender-ref ref="DB_PERFORMANCE"/>
</logger>
<logger name="org.hibernate.engine.internal.StatisticalLoggingSessionEventListener" level="TRACE" additivity="false">
    <appender-ref ref="DB_PERFORMANCE"/>
</logger>
<logger name="org.hibernate.stat.internal.ConcurrentStatisticsImpl" level="TRACE" additivity="false">
    <appender-ref ref="DB_PERFORMANCE"/>
</logger>

Finally register the appender in the same file:

<root level="INFO">
    <appender-ref ref="STDOUT" />
    <appender-ref ref="FILE" />
    <appender-ref ref="DB_PERFORMANCE"/>
</root>

Note

When using the logback.xml file, the Apache Tomcat Windows service does not need to be restarted. It will automatically reload itself within minutes. The logback.xml file only needs to be saved after the changes.

Step 2b - logback.groovy

If you are using the groovy-based logback configuration (logback.groovy), locate the file and add the following:

Example of how to add the appender in the logback.groovy configuration file

     C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\conf\logback.groovy

appender("DB_PERFORMANCE", FileAppender) {
    file = "${targetDir}/db-performance.log"
    append = true
    encoder(PatternLayoutEncoder) {
        pattern = "%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n"
    }
}

Then add the loggers:

logger("org.hibernate.SQL", DEBUG, ['STDOUT', 'DB_PERFORMANCE'], false)
logger("org.hibernate.type.descriptor.sql.BasicBinder", TRACE, ['STDOUT', 'DB_PERFORMANCE'], false)
logger("org.hibernate.engine.internal.StatisticalLoggingSessionEventListener", TRACE, ['STDOUT', 'DB_PERFORMANCE'], false)
logger("org.hibernate.stat.internal.ConcurrentStatisticsImpl", TRACE, ['STDOUT', 'DB_PERFORMANCE'], false)

Finally register the DB_PERFORMANCE appender:

     root(INFO, ['FILE', 'DB_PERFORMANCE'])

Important

When using the groovy-based logback configuration (logback.groovy), the Apache Tomcat Windows service must be restarted, after the changes have been completed and saved.

Renewing TrustViews own SSL/TLS certificate

TrustView uses Apache Tomcat to host the application. First, locate the Tomcat installation directory, typically:

Directory path of the Tomcat installation

C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView

Tip

If you are unable to locate the Apache Tomcat directory, contact our for assistance.

The SSL certificate is specified in the Tomcat Apache server.xml configuration file Tomcat-install-dir\conf\server.xml. In the file, you must locate the Connector element matching the port your TrustView is running on, typically 443 or 8443.

Example of Connector element

<Connector
port="443"
maxhttpheadersize="8192"
maxThreads="150"
minsparethreads="25"
maxsparethreads="75"
enablelookups="false"
disableuploadtimeout="true"
acceptcount="100"
scheme="https"
secure="true"
sslProtocol="TLS"
clientAuth="true"
SSLEnabled="true"
keystoreFile="conf/trustview_ssl.p12"
keystorePass="Test1234"
keystoreType="PKCS12"
/>

Tip

It is recommended to have a backup of the server.xml file before modifying it.

Now update the following attributes of the Connector element to match the renewed certificate:

• keystoreFile

• keystorePass

If the new certificate is available in JKS format instead of PKCS12 format, make sure that keystoreType is set to JKS instead of PKCS12. If the new SSL certificate is in PFX format, the keystoreType still needs to be set to PKCS12.

Finally, you must restart the Apache Tomcat service for the change to take effect.

Update application specific Java version

Java requirements

TrustView Version	Java Version Required
TrustView v4.x	Java 8.x
TrustView v5.14.x	Java 8.x
TrustView v5.15.1 - v5.15.80	Java 8.x
TrustView v5.15.81	Java 17.x
TrustView v5.16.x and above	Java 17.x

To update the Java version used by TrustView specifically, follow these steps:

Step 1

Download the new Java version from this link: Download OpenJDK.

Important

The version must be based on the relevant major Java version of the package type JDK. See the Java requirements in the table above for the specific version needed, depending on the version of TrustView you are currently running.

Choose the .msi version and run it to install it on the server, where TrustView is installed.

Step 2

Once installed, go to the bin folder of the Apache Tomcat directory: C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\bin and open/run the application file called TrustVieww.exe.

Under the Java tab, the section named Java Virtual Machine, must have the path changed to the path of the jvm.dll file of the newly installed Java version. The path can vary, but usually it can be found under the Java directory by following this path: ...\jre\bin\server\jvm.dll.

Once the new jvm.dll file has been selected, apply the changes and restart the Apache Tomcat Windows service, to get TrustView to use the newly installed Java version.

Tip

If you want to verify that the changes has been applied, you can see the Java version used by TrustView, by logging into TrustView and going to Settings and then at the bottom of the page under Systeminfo.

Step 3

Important

If you are only using a TrustView login with local users, this step is not required.

If you are using AD login for TrustView, you need to update your cacerts (the keystore) file in the conf folder of the Apache Tomcat installation directory to match the new Java version installed.

Follow these steps (starting from step 3) here to update your cacerts file: Configuration of AD integrated user management - Step 3.

Update Apache Tomcat

This is needed if TrustView is moved to a new major release of Apache Tomcat, but also if you need to move your TrustView instance to another server. A clean installation will then be necessary, on the newly appointed server to host TrustView.

To update the Apache Tomcat application used to run TrustView, follow these steps:

Step 1

Tip

It is highly recommended to create a backup of the old Apache Tomcat directory before proceeding, just in case.

Download the new Apache Tomcat version from this link: Download Apache Tomcat. Choose major version 9, but minor versions may vary depending on releases - so simply choose the newest minor version available.

Download the .exe file, by choosing the 32-bit/64-bit Windows Service Installer under the Binary Distributions segment of the download page.

Step 2

Once all the relevant files have been moved from the old Apache Tomcat directory, to the new one, you can delete the old directory and the old Apache Tomcat Windows service, by using this command in a Command Prompt (with administrator rights):

sc delete <serviceName>

Tip

The name of a Windows service, can always be found under “Properties” of the specific service.

Ensure that the service of the new TrustView instance has Startup type set to Automatic (Start Delayed). It is also recommended to setup a recovery method for the service, which can be done under Properties Recovery and by setting the First, Second and Third failure to Restart the Service. Then finish by setting Reset fail count after to 1 days and clicking apply before starting the service.

Step 3

Once the new Apache Tomcat version has been downloaded, install the new Apache Tomcat version and name the service (during the installation setup) TrustView. The old version, containing the current relevant files, is listed below:

• TrustView.groovy (located in the lib directory)

• context.xml (located in the conf directory)

• server.xml (located in the conf directory)

• logback.xml (located in the conf directory)

• .war files (located in the webapps directory)

Optional files, depending on your setup (and if you have the files):

• cacerts (located in the conf directory and used for AD login)

• Any SSL certificates (located in the conf directory and used for the https connection)

Needs to be moved over in the corresponding directories of the new Apache Tomcat installation directory, once that version has been installed.

Important

The Microsoft JDBC drivers, used for the database connection, also needs to be moved or reinstalled, when a new version of Apache Tomcat is installed. See what files to move or how to install the Microsoft JDBC drivers here: Installing Microsoft JDBC drivers

Step 4

After the installation of the new Tomcat has been completed and the relevant files has been moved over, go to the bin directory and open the TrustVieww.exe file. Go to the Java tab in the dialogue box and at the bottom, set the HEAP memory:

• Initial memory pool - 512 MB

• Maximum memory pool - 2048 MB

Note

The HEAP memory pool is based on the available RAM on the server, where TrustView is installed, so the maximum value, can be increased if necessary.

In the Java Options, add this line if it is missing:

-Dlogging.config=C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\conf\logback.xml

Important

Remember to adjust the file path if necessary.

Changing the SMTP gateway address

If the SMTP mail server changes, and you need to change the SMTP gateway address, follow these instructions:

Step 1

Locate the TrustView.groovy file in the TrustView installation directory, typically:

Directory path of the TrustView configuration file

     C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\lib\TrustView.groovy

Tip

If you are unable to locate the directory, contact our for assistance.

Step 2

Open the TrustView.groovy file with a text editor (Notepad++ can be recommended) and locate the following lines at the end of the file:

Example of SMTP server setup in the TrustView configuration file

     grails {
         mail {
             host = "smtp.company.com"
        }
     }

Change the smtp.company.com part of the hostname to the new name of your SMTP gateway address and save the TrustView.groovy file. Restart the Apache Tomcat service for the change to take effect, and the SMTP gateway address will now be updated.

Using Office 365 SMTP server

This example shows how to configure an Office 365 SMTP server in the TrustView.groovy file:

Example of Office 365 SMTP setup

grails {
    mail {
     host = "Outlook.office365.com"
     port = 587
     username = "username here"
     password = "password here"
     props = ["mail.smtp.starttls.enable":"true" ,
              "mail.smtp.port":"587"
             ]
   }
}

How to change the support mail and mail sender

The support mail is used as the sender of the notification mails, that are sent from TrustView, but also as the recipient mail when pressing forgot password on the login screen.

Note

It is recommended to change the support mail address in TrustView to fit your organization.

Step 1

Locate the TrustView.groovy file in the TrustView installation directory, typically:

Directory path of the TrustView configuration file

     C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\lib\TrustView.groovy

Tip

If you are unable to locate the directory, contact our for assistance.

Step 2

Open the TrustView.groovy file with a text editor (Notepad++ can be recommended) and locate the following line in the file:

The supportEmailAddress counts as both the sender on notifications from TrustView and the support mail

 supportEmailAddress = "support@trustskills.com"

Change the support@trustskills.com (default value) to something else that fits your organization, such as support@domain.com.

Releasing the Change log lock in the database

When TrustView makes a database update, Liquibase reads from the DATABASECHANGELOG table in the database to determine which changesets need to run. To avoid conflicts between concurrent updates, which can happen if multiple changes happens at the same time, for the same database instance. If TrustView make another update during this time, Liquibase waits until the lock releases before running it.

This example shows how the database change log lock should be displayed in a trustview-logback-xxxx-xx-xx.log file if it was acquired successfully:

20XX-XX-XX 00:00:00.000  INFO --- [ost-startStop-1] liquibase                                : Successfully acquired change log lock
20XX-XX-XX 00:00:00.000  INFO --- [ost-startStop-1] liquibase                                : Reading from [dbo].[DATABASECHANGELOG]
20XX-XX-XX 00:00:00.000  INFO --- [ost-startStop-1] liquibase                                : Successfully released change log lock

Important

If the change log lock is not successfully acquired, TrustView will not be able to run and will not start up, until the lock is released.

This example shows how the database change log lock error would be displayed in a trustview-logback-xxxx-xx-xx.log file:

20XX-XX-XX 00:00:00.000  INFO --- [ost-startStop-1] liquibase                                : Waiting for changelog lock....
20XX-XX-XX 00:00:00.000  INFO --- [ost-startStop-1] liquibase                                : Waiting for changelog lock....
liquibase.exception.LockException: Could not acquire change log lock.  Currently locked by servername (127.0.0.1) since XX/XX/XX 0:00 PM
     at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:190)
     at liquibase.Liquibase.update(Liquibase.java:196)
     at liquibase.Liquibase.update(Liquibase.java:192)
     at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:431)
     at org.grails.plugins.databasemigration.liquibase.GrailsLiquibase.performUpdate(GrailsLiquibase.groovy:83)
     at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:388)

Clear the lock in the database

There are several ways of clearing or unlocking the change log lock in the TrustView database:

Method 1

Drop or delete the DATABASECHANGELOGLOCK table in the TrustView database; it will then be recreated, and the lock will be released after you restart the TrustView Tomcat service.

Method 2

Run the following command in the database:

Example of SQL command

UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1;

Note

You may need to replace LOCKED=0 with LOCKED=FALSE.

TrustView log file

Locate the TrustView log file

There may be cases where you need the trustview-xxxx-xx-xx.log file for troubleshooting or we need it to provide support if we do not have direct access to the server for your TrustView installation.

The log file can typically be found in this location, depending on the TrustView directory name:

C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\logs\trustview-date

The log file can be opened with any text editor but Notepad++ can be recommended.

Send the TrustView log file

It is also possible to send specific log files, directly from TrustView to our backend, instead of locating the log files manually.

Go to the Support section, found in the top right corner of TrustView.

Under Send logfiles to TrustSkills, you can select all the available log files that are relevant for the situation and then press Send to TrustSkills to send the selected log files to our backend.

Note

It requires a user of the role Superuser or Global Administrator, to be able to access this and send the log files.

Passwords including the & symbol

Escape the & symbol in .xml and .groovy files

If the passwords included in the xml and groovy files contain the & symbol, the Apache Tomcat service will not start and the symbol must be escaped or the password changed, so it does not contain the & symbol.

To escape the symbol, replace & with &.

Use certificate and key from Microsoft certstore in Apache Tomcat

We recommend using minimum Windows Server 2016

The following is specified in the Tomcat Apache server.xml configuration file, located in Tomcat-install-dir\conf\server.xml.

Update and/or add the following in the server.xml to match your configuration:

• keyAlias=”friendly name of the cert”

• keystoreType=”Windows-root or Windows-MY”

• keystoreFile=” “

• keystorePass=” “

KeyAlias can be found in mmc - certificate - properties - friendly name.

Note

There are multiple keystoreType for the Windows connector configuration. Windows-MY and Windows-Root, depending on where the certificate is located.

Windows-MY - System Accounts Personal certificate store

Windows-Root - Trusted Root CA store

Because Tomcat is running as a service you will need to use Windows-Root and place the certificate in the Trusted Root CA store or set the certificate into System accounts Personal certificate store and use Windows-MY. It won’t work with my user account or computer account for this.

Tip

Ensure your certificates are in the correct store by running certmgr.msc.

Server.xml example

     <Connector port="443" protocol="org.apache.coyote.http11.Http11NioProtocol"
            maxThreads="200"
            scheme="https"
            secure="true"
            SSLEnabled="true"
            keyAlias="**Test**"
            keystoreType="**Windows-root**"
            keystoreFile=""
            keystorePass=""
            clientAuth="false"
            sslProtocol="TLS"
            sslEnabledProtocols="TLSv1.2,TLSv1.3"

Configure Heap memory

To configure the Heap memory for the TrustView instance, go to the application configuration and open it:

Locate and open the trustvieww.exe file

C:\Program Files\Apache Software Foundation\Tomcat 9.0_TrustView\bin

Go to the Java tab and adjust the Heap memory section in the bottom to meet your needs. It is recommended to set the minimum Heap memory stack to 512 and the maximum to 2048 or more, depending on the available amount of GB of RAM on the server, that can be allocated to the use.