"Failed to connect to database: Error: Access denied for user......" error when trying to connect to AWS database

[ML_Rookie_2021]

I see this issue has been asked in several places on the internet like this however not of the solutions seem to be working for me.

I have set up a mysql database in AWS however I cannot connect to it using R. This is my code for connecting -

library(tidyverse)
library(dplyr)
library(RMySQL)

con <- DBI::dbConnect(
    drv = RMySQL::MySQL(),
    username = Sys.getenv("AWSRDS_USER"),
    passwords = Sys.getenv("AWSRDS_PASS"),
    host = Sys.getenv("AWSRDS_HOST"),
    port = 3306,
    dbname = "awsrds01"
)

I keep getting this error -

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Access denied for user 'xxxx'@'pool-xxx-xx-xx-xx.bltmmd.fios.verizon.net' (using password: NO)

Other info -
Operating system - macOS Monterey (Version 12.5)
R Studio version - 4.0.2 (2020-06-22)
dplyr version - 1.0.9
RMySQL version - 0.10.21
DBI version - 1.1.3

Any help/ideas/suggestions on how to fix this will be much appreciated.

[SPratap]

Did you checked this solution from PilarRius--
1-
username="root"
password= "YOUR_PASSWPRD"

As in no need to put the actual password, only copy "YOUR_PASSWORD"

2-If still doen´t work remove database

3-Check that your IP address is added to the server permissions/security

4-Check that the DB is available and public connection is allowed

5-If using Windows allow connections to the port 3306 on the Firewall Defender(control panel, security, firewall defender, advanced setting, add inbound rule, port, 3306, allow)

[ML_Rookie_2021]

@SPratap Thanks. Yes I saw that and i did try 1 and 2, but still getting the same error. Unfortunately I'm not sure how to check #3. Currently researching that now.

[andresrcs]

@ML_Rookie_2021

Failed to connect to database: Error: Access denied for user 'xxxx'@'pool-xxx-xx-xx-xx.bltmmd.fios.verizon.net' (using password: NO)

It seems remote access is not allowed or at least not with password authentication. Have you opened the port for your MySQL server (usually 3306) in the AWS security settings for your EC2 instance? Have you configured your MySQL server to allow for password authentication from remote connections?

[ML_Rookie_2021]

@andresrcs Thanks. Can you please point me to any resources that can give step by step instructions on how to do this?

[andresrcs]

Sorry, I'm not aware of any specific step by step guide for this, when I forget how to do this sort of thing I simply "Google" something like "how to open ports on AWS EC2?"

[ML_Rookie_2021]

@andresrcs Ah. I see what you mean't. It requires editing the inbound rules in aws. Already tried that and still did not fix the error.

[andresrcs]

@andresrcs

Have you configured your MySQL server to allow for password authentication from remote connections?

I don't remember how to do this with MySQL because I haven't use it in many years, but I do remember that it doesn't allow remote passwords autentication by default.