ColdFusion app " Error Executing Database Query. Access denied for user" for RDS mySQL

Question

I have a legacy Coldfusion application that I am trying to migrate the local mySQL database to AWS RDS MySQL. Migration worked great and setup and test of the data source name (DSN) also indicated success. But the now when the app tries to query the database (just a SELECT statement), I get the following error.:

Access denied for user 'foobar'@'%' (using password: YES)

The user is the admin user setup on RDS. I can logon to the RDS mySQL server from the command prompt and perform the same query that the app is trying to use; so I know it's not a connectivity thing.

thanks in advance

Answer 1

From the CLI, remote into the database and check the GRANTS on the user foobar:

show GRANTS for 'foobar';

This will show the user's permissions and from which host they can access those privileges.

The posted error message says the user foobar is trying to connect from % (anywhere). I'm not sure if AWS allows "anywhere" access to their databases. You might need to update the user's host address based on the IP of the CF server. There could also be AWS firewall permissions to be investigated.