Problems installing the Alerting Framework for Spotfire

[Mathew Banda]

 I am trying to install the Alerting Framework installation and I am running into some issues. When I check the api status using the link https:spotfireserver/alerting/api/status I am receiving the error: 404 file not found. After going through the troubleshooting steps, I am still unable to get this step working. In the catalina logs, I am receiving the error:

Error deploying web application archive [C:\tibco\tss\14.0.0\tomcat\webapps\alerting##sf14.x-above.war]

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'quartzDataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/quartz/QuartzAutoConfiguration$JdbcStoreTypeConfiguration.class]: Failed to instantiate [org.springframework.boot.autoconfigure.quartz.QuartzDataSourceScriptDatabaseInitializer]: Factory method 'quartzDataSourceScriptDatabaseInitializer' threw exception with message: Failed to determine DatabaseDriver

I have attached the catalina logs for reference.

Please assist. 

catalina.log

[Dave Leigh]

Hi Mathew

Basically the application won't start as the database connection pool cannot be instantiated.  This usually means that either the database connection details are wrong or there is a problem physically opening a connection such as a firewall, network or security issue.

From the log it looks like you are using SQL Server.  It also looks like the connection is failing due to problems checking the certificate 

    Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

In our own SQL Server environment we work around this issue by adding "encrypt=false" to the connection string as below:

    spring.quartz.properties.org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
    spring.datasource.url=jdbc:sqlserver://localhost\\test:1433;encrypt=false;databaseName=spotfire14_alerting

Can you try this and see if it fixes the problem?

Cheers
Dave

[Mathew Banda]

I am still receiving the same error. 

[Dave Leigh]

Hi Mathew

This page from Microsoft provides some more advice on how to connect using their JDBC driver.

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16

Please check whether any of their recommendations resolve your issue.

If you are still stuck, message me directly via my profile and we will setup a troubleshooting session.

Cheers
Dave

[Dave Leigh]

Summarizing what was done to resolve this issue.,,

1) The requirement was to connect to the Alerting Database using Integrated Authentication.  This is also how Spotfire Server was configured.

2) As part of the Integrated Authentication flow, Java loads a DLL to handle the Windows Authentication.  With Spotfire Server 14, the jar file is mssql-jdbc.jar version 11..2.3 and this in turn loads mssql-jdbc_auth-11.2.3.x64.dll which is present in the tomcat/spotfire-lib folder

3) When we tried to setup the Alerting Framework to use these same files, the startup failed with a message "java.lang.UnsatisfiedLinkError: Unable to load authentication DLL mssql-jdbc_auth-11.2.3.x64" in the catalina.log.

4) This was traced to a restriction in Java that two Class Loaders cannot load the same DLL file. See this article for further details.

5) The solution/workaround was to use a different version of the Microsoft SQL Server JDBC driver for the Alerting Framework, placing the jar file in the deployed webapps/alerting folder and the DLL file in the tomcat/spotfire-lib folder.  The exact version of the driver is not important, it just has to a different version than 11.2.3.

With these changes the Spotfire Server and Alerting Server both started successfully.