I am trying to configure my CA API Gateway on remote database connection. I followed steps in "CA API Gateway – Virtual Appliance" PDF document. On my remote database server I added MySQL user and granted him all privileges:
On CA API Gateway after I confirm my configuration summary I get this result:
However, after I check databases on database server I can see that it created "ssg" database:
I am without ideas what to try next ? I tried playing with privileges and allowed port 3306 for all incoming IP's with this command:
iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
but error message is still displayed with this code 42000 and I don't understand what I am doing wrong.
After I added privileges with GRANT option:
I got other error and database "ssg" is not created anymore:
Any help will be appreciated!
you have the database user (default name gateway) and the administrative database user (default name root), that need access to the mysql database. (see also Gateway Configuration Menu (Appliance) - CA API Gateway - 9.3 - CA Technologies Documentation )
You only mentioned the grant for the root user, did you do the same for the gateway user?
Yes I did, both "root" and "gateway" user have full privileges, added with this commands:
CREATE USER 'root'@'myipaddress' IDENTIFIED BY 'mypassword';CREATE USER 'gateway'@'myipaddress' IDENTIFIED BY 'mypassword';GRANT ALL PRIVILEGES ON *.* TO 'root'@'myipaddress' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON *.* TO 'gateway'@'myipaddress' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;FLUSH PRIVILEGES;
I tried to log in from gateway server to database server and I connected without any problems, but when I try to connect directly to mysql:
mysql -u gateway -h myIpAddress -p3306
I get this error:
mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2003 (HY000): Can't connect to MySQL server on 'myIpAddress' (113)
I don't think the ip address is working very well . if possible try to replace it for % so the user has
access from every host or ip .
I added that:
but it still shows message:
mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1130 (HY000): Host 'X.X.X.X' is not allowed to connect to this MySQL server
Does your MySQL logs show anything helpful in this case? Perhaps it can provide more details for us all on what it doesn't like about the privileges of the user logging in.
Additionally, it may be helpful to try the following grants:
grant all on ssg.* to gateway@'%';grant all on ssg.* to gateway@'localhost';grant all on ssg.* to gateway@'localhost.localdomain';grant all on ssg.* to gateway@'localhost6';grant all on ssg.* to gateway@'localhost6.localdomain6';
Reference: Gateway logs indicate replication failing due to a - CA Knowledge
For new install (create ssg database), the root db user should create the gateway db user and grant permissions, so the root user need with grant option permission.
grant all on *.* to ‘root'@'%' WITH GRANT OPTION;
furthermore, you may want to run the command below to let the permission change take effect immediately,
DustinDauncey thank you, I added that and some changes on my firewall settings with help of this article:https://www.admfactory.com/how-to-open-port-for-a-specific-ip-address-on-centos-7/
and it works!Also CA API Gateway will refuse to run if you have "_" or other special characters in your DNS name, so I had to change it.