Search code examples
mysqlrshinyr-dbirmariadb

Failed to connect: access denied for user - MySQL DB hosted in AWS RDS

I´m trying to connect to a MySQL database hosted in AWS for a shiny dashboard with no success. It throws the error Access denied for user:

R error

I'm sorry I can´t produce a reprex because I don´t have control over the aws infrastructure. Any advice on debugging an on-cloud db connection from R would be much appreciated.

Here is the R code:

con <- DBI::dbConnect(RMariaDB::MariaDB(),
                  host     = "xxxxx.eu-west-1.rds.amazonaws.com",
                  dbname   = "my_schema",
                  user     = "my_user",
                  password = "my_pw",
                  port     = 3306)

I know I can connect to the host from my ip because I am able to access the DB from Power BI and from Python.

From Power BI:

Power BI MySQL connection

From python:

import mysql.connector as mysql
from tabulate import tabulate

HOST = "xxxxx.eu-west-1.rds.amazonaws.com"
DATABASE = "my_schema"
USER = "my_user"
PASSWORD = "my_pw"
db_connection = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)

cur = db_connection.cursor(buffered=True)

cur.execute("SELECT * FROM my_table")

rows = cur.fetchall()

print(tabulate(rows, headers=cur.column_names))

python query

Solution

  • I had to solve this changing from my Windows 10 local pc to an Ubuntu server. In Ubuntu the conection was a breeze. Run this before install.packages("RMariaDB"):

    sudo apt install libsodium-dev
sudo apt install libxml2-dev
sudo apt install libmariadbclient-dev

    windows sucks!