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'
Release : 21.2, 22.2
Component : Jaspersoft for CA Spectrum
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.
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.
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.