JDBC drivers 10.2 11.2 from Microsoft SQL Server 2017/2019 are not compatible with UVMS or Reporter

Products

• CA Automic Dollar Universe

Issue/Introduction

JDBC drivers 10.2 and superior versions (11.2 and 12.2 by the time of this article) from Microsoft SQL Server 2017/2019/2022 are not compatible and cannot be used with UVMS or Reporter.

On the Reporter Server we can see below error:

[INFO ]  [main] com.orsyp.reppub.server.process.RepDBChecker - The load target parameters are invalid. Database couldn't be reached
[INFO ]  [main] com.orsyp.reppub.server.process.RepDBChecker - RepDBChecker process won't execute
[INFO ]  [main] com.orsyp.reppub.server.communication.NetAcceptor - ServerStartUp started succesfully to 10630 port...

The Enforced TLS communication that was added in mssql-jdbc-10.2.0 (or superior) and the fact that Reporter / UVMS JDBC connection string cannot be customized to allow to set encrypted=false  provokes that they fail to connect to the database with this kind of error:

[ERROR]  :Unable to initialize DB
[com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:35d73c91-e159-4af1-b3cc-24003b18d073]
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:35d73c91-e159-4af1-b3cc-24003b18d073
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3806)   

Environment

Release : 6.x and 7.x

Component : UVMS and Reporter

Database: MS SQL Server

Cause

Caused by a change in JDBC drivers where TLS encryption is enabled by default starting with Microsoft JDBC Driver 10.2 for SQL Server.

 

Resolution

Starting from 7.01.01 version, MSSQL 2019 was certified for UVMS:
https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/dollar-universe/7-01-01/release-notes/new-features.html

• Certifications

  : With the MSSQL 2019 certification, you can now set up a secure TLS connection to your database.

  For more information, see UVMS Interactive Upgrade on Windows , UVMS Interactive Installation on Windows, and Configuring a Connection to the Database.

Reporter is not yet compatible with the new jdbc 10.x drivers or MS SQL with encryption enabled

Additional Information

https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver16