We have a custom API developed to work with our AWS-hosted database. The API and database are currently running in the field, however, the only way to perform operations is by connecting via the 'admin' account that was created during database instance initialization.
The goal is to create specific login accounts for each VM with minimum required permissions.
When attempting to connect to the instance via the API using any account other than 'admin', we get the following error:
Authentication to host 'AWS_ENDPOINT' for user 'NON-ADMIN-USER' using method 'mysql_native_password' failed with message: Access denied for user 'NON-ADMIN-USER'@'****' (using password: YES)
When attempting to use 'NON-ADMIN-USER' via MySQL Workbench, operations work as expected.
This is true for a mock-up LAB-PC in our test lab as well as our VMs in the field.
This however is NOT the case on employee Sam's workstation. Sam's workstation is a domain computer, but while doing testing, it is physically connected to the same isolated VLAN as the LAB-PC. Sam's computer is able to connect and perform operations using NON-ADMIN-USER with both MySQL Workbench and the API.
We've been working with our security team and have confirmed there are no connections blocked by the firewall or anything on that side. This is supported by the fact that the error above looks to be a response from the MySQL service meaning the request is at least getting to the endpoint before being rejected. We have also been working with AWS as well. Everyone is stumped.
Recap:
LAB-PC and field VMs
- can connect to the database via both the custom API and MySQL Workbench using 'admin'
- can connect to the database via MySQL Workbench using 'NON-ADMIN-USER'
- can NOT connect to the database via the custom API using 'NON-ADMIN-USER'
Sam's Workstation
- can connect to the database via both the custom API and MySQL Workbench using both 'admin' and 'NON-ADMIN-USER'
Further info:
AWS RDS Aurora MySQL database Engine version 8.0.mysql_aurora.3.03.1
For troubleshooting purposes, 'NON-ADMIN-USER' had been deleted and recreated multiple times and also given the same permissions as 'admin'.
Operations used to test once connection is made: SELECT statements and stored procedures.
PCs using Windows 10 Pro and Windows 10 Enterprise.
Edited to add: @'%' is used throughout as to not restrict any user from any address.
Please let me know if any more info is required. Thank you for your help!
Testing summary:
I tried to connect to our database via our API using our non-superuser creds from a test LAB-PC and a production VM.
We expected a successful connection on the LAB-PC and production VM as we did when using the developer workstation. Connection strings and credentials were copied and pasted from the same source each time, several attempts were made to confirm connection info was entered correctly.
The result was the following error:
Authentication to host 'AWS_ENDPOINT' for user 'NON-ADMIN-USER' using method 'mysql_native_password' failed with message: Access denied for user 'NON-ADMIN-USER'@'****' (using password: YES)