DX Netops Spectrum reports in CABI fail with error after upgrading CABI to 7.9.x

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

Error seen:

java.sql.SQLSyntaxErrorException: Could not connect to address=(host=hostname.domain)(port=3306)(type=master) : Access denied for user 'CR_user'@'hostname.domain' to database 'srmdbapi&allowPublicKeyRetrieval=true'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:243)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1241)

Environment

Release : 21.2, 22.2

Component : Jaspersoft for CA Spectrum

Cause

Jaspersoft appends &allowPublicKeyRetrieval=true to the database connection string. It is possible jaspersoft is incorrectly assuming that there
   are other post variables and it is appending one more. Since this is the first post variable '?' is needed after the database name and before the first variable.

Resolution

Login to CABI as "superuser".

Select Data Sources

Then find and edit the spectrum datasources (right click -> Edit):

In the edit screen, change the & to a ? in the URL:

Save the settings.

Reports should now work successfully.

Additional Information

There are 2 places in Jasper Soft where the data sources reside. In the UI and in a database table named jijdbcdatasource. The value in the jijdbcdatasource is
   what really matters here.


In the OneClick interface
   ~~> Administrator ~~> Report Manager ~~> Jasper Integration

When the 'Save' button is clicked

The following occurs:

1. OneClick configures the Spectrum_ds and Spectrum_domain_ds data sources in the Postgres database via a REST call. The data source entries in the jijdbcdatasource table
        at this point does not contain any parameters

   


Jaspersoft in the UI appends &allowPublicKeyRetrieval=true



2. At this point reports will execute without issue as the data source entry in the jijdbcdatasource table.


If a user edits the Spectrum_ds data source and clicks 'Save' it will write the data source with the &allowPublicKeyRetrieval=true value to the database
   table and reports will then fail.
 

** If a user does edit the Data Source in the Jaspersoft UI the '&' character after the database name (reporting or srmdbapi) will need to be changed to '?' before saving.