Connection error in CABI/Jasper when trying to create reports

Products

• Spectrum
• Network Observability

Issue/Introduction

When trying to create reports using CABI/Jasper, users are seeing the following error;

"Host "HOSTNAME" is not allowed to connect to this MySQL server"

Environment

DX NetOps Spectrum integrated with CABI/Jasper

Cause

The CABI/Jasper system does not have privilege to access MySQL on the Spectrum Report Manager (SRM) server.

Resolution

1 - Log into the SRM system as the user that owns the Spectrum installation

2 - If on Windows, start a bash shell by running "bash -login"

3 - cd to the $SPECROOT/mysql/bin directory and run the following command to log into MySQL where <PASSWD> is the root password for your version of DX NetOps Spectrum:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD>

4. At the MySQL command prompt, run the following MySQL commands where <HOSTNAME> is the hostname of the CABI/Jasper system::

mysql> GRANT ALL PRIVILEGES ON srmdbapi.* TO 'WEBI_user'@'<HOSTNAME>' IDENTIFIED BY '0n3cl1Ck';

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

mysql> GRANT ALL PRIVILEGES ON reporting.* TO 'WEBI_user'@'<HOSTNAME>' IDENTIFIED BY '0n3cl1Ck';

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

mysql> FLUSH PRIVILEGES;

 

From Spectrum 22.2.2 onwards, MySQL has been upgraded to 8.x and the GRANT statement has been changed to the following:

mysql> GRANT ALL ON reporting.* TO 'CR_user'@'<CABI Server>';
mysql> GRANT ALL ON srmdbapi.* TO 'CR_user'@'<CABI Server>';

mysql> FLUSH PRIVILEGES;
mysql> exit

Note: Replace <CABI Server> with the actual FQHN or IP Address of the CABI Server.

Note: You may need to create the CR_user account if it does not exist.

mysql> CREATE USER 'CR_user'@'<CABI Server>' IDENTIFIED BY '0n3cl1Ck';