Rave Connector: Troubleshooting Clinical Cache refresh failure with Spotfire Server

Description: This article addresses a scenario where the Rave Connector's Clinical Cache database fails to receive new information from the external Medidata Rave system, resulting in outdated or no data presentation in Spotfire analyses. This scenario manifests as A) failing automation jobs for the Clinical Cache, and B) the Clinical Cache Manager displays an error message rather than the expected information.  The failing request is initiated by the execution of an Automated Task via the Node Manager's automation service. 

The below error messages indicate failed connection between the Spotfire server and the Clinical Cache database:

Log entry:

ERROR;2024-11-26T17:50:43,985-05:00;2024-11-26 22:50:43,985;680904cc-76fa-4b3b-a38c-045ff4f9c861;2222129500gdRW;148;automationservices@SPOTFIRESYSTEM;TsasUserSession;Spotfire.Dxp.Worker.Services.Web.Automation.JobInstance;"Automation job df865eb5-2277-4f7c-90f8-079755d7318b failed in task 1 Update Clinical Cache with Data from Medidata Rave®: Failed RunJobAsync service call: Failed ReadStudyAsync service call: An exception occurred while initializing the database. See the InnerException for details.
An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
An existing connection was forcibly closed by the remote host"

Solution:

1. Verify Rave Connector Configuration:

   • The Install Guide describes the ConfigClinicalCache command line utility.

2. Investigate TLS Handshake Issues:

   • Review recent system changes that may have affected the TLS handshake between the application server and database.
   • Consult the following Microsoft article: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-exist-connection-closed

3. Examine Log Files:

   • Collect and analyze relevant log files, including:
     • Spotfire Troubleshooting Bundle (set logging to DEBUG level).
     • Rave Connector logs.
     • Windows Event Viewer logs (Application and System categories) from the Spotfire Node Manager server, SQLServer, and Spotfire application server.

4. Review Automation Services:

   • Check the Automation Services job details in the Admin Console.
   • Attempt to run the job locally to gather more specific error information.

5. Verify Clinical Cache Manager:

   • Examine the Clinical Cache Manager for any additional error messages or status indicators.

6. Consider System Updates:

   • Investigate any recent domain-level changes, software updates, or security setting modifications that might have affected the allowed cipher suites for TLS connections.
   • Cipher settings. The following article lists Encryption/Hashing algorithm used by Spotfire. This scenario may occur if the network does not allow the 'backwards compatibility' cipher suites.

https://docs.tibco.com/pub/spotfire_server/latest/doc/html/TIB_sfire_server_and_environment_security/servsecurity/topics/standards_and_algorithms.html