Spectrum CABI JasperSoft AD-Hoc reports cannot be run
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)
....
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”.
Spectrum 10.x
CABI - JasperSoft Reports 6.4.3
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