Spectrum CABI JasperSoft AD-Hoc reports cannot be run

Products

• CA Spectrum
• CA eHealth

Issue/Introduction

User is unable to run Ad-Hoc Spectrum JasperSoft reports. The following error is seen:

Error Details

The server has encountered an error. Please excuse the inconvenience.

Error Message

An error occurred while creating the connection. Try again

Error Trace.

com.jaspersoft.jasperserver.api.JSException: jsexception.error.creating.connection Arguments: at
com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.createConection(JdbcDataSourceService.java:64) 
....

Cause

The CABI Server does not have permissions to execute queries against the Spectrum MySQL database server:

 

The following error is seen in the CABI_HOME/apache-tomcat/webapps/jasperserver-pro/WEB-INF/logs/jasperserver.log

2020-07-16 17:45:54,156 ERROR errorPage_jsp,http-nio-443-exec-5:583 - stack trace of exception that redirected to errorPage.jsp
com.jaspersoft.jasperserver.api.JSException: jsexception.error.creating.connection
Arguments: 
...
Caused by: java.sql.SQLException: Host '<CABI Server>' is not allowed to connect to this MySQL server

Testing the spectrum_ds data source in JasperSoft results in one of the following errors:

“Could not connect: Access denied for user 'CR_user'@'<CABI Server>' to database 'reporting'"
“Host '<CABI Server>' is not allowed to connect to this MySQL server”.

 

Environment

Spectrum 10.x
CABI - JasperSoft Reports 6.4.3

Resolution

On the Spectrum server:

Open a bash shell (bash -login)

Navigate to the $SPECROOT/mysql/bin directory

./mysql -uroot -proot (Windows)
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot (Linux/Unix)

mysql> GRANT ALL PRIVILEGES ON reporting.* TO 'CR_user'@'<CABI Server>' IDENTIFIED BY '0n3cl1Ck';
mysql> GRANT ALL PRIVILEGES ON srmdbapi.* TO 'CR_user'@'<CABI Server>' IDENTIFIED BY '0n3cl1Ck';

mysql> FLUSH PRIVILEGES;
mysql> exit

Note - Replace <CABI Server> with the actual FQDN of the CABI Server.

After this try re-running the report