0

I work with a remote db using a ssh tunnel:

ssh -f myuser@ssh_server -N -L 7777:dbi_server:3306

I put my password, and then I can do this:

mysql -u myuser -h localhost --port=7777

I can access my db and do queries

But from R or Rstudio, I can't:

DBI::dbConnect(
    RMySQL::MySQL(),
    user = 'myuser',
    host = '127.0.0.1',
    port = 3306,
    dbname = 'dbi_name'
    )

Failed to connect to database: Error: Access denied for user 'myuser'@'localhost' (using password: NO)

This is after I connect the ssh tunnel Any help will be much appreciated

Pabort
  • 348
  • 1
  • 11
  • 1
    I thought you had set up the port on 7777. Why are you connecting to 3306 in your R code? – Bill Karwin Feb 28 '22 at 16:35
  • 1
    Based on your error *"using password: NO"*, you may need to put your password in the `dbConnect` call. [There's several ways to do this safely](https://db.rstudio.com/best-practices/managing-credentials/), I would recommend storing it in an environment variable as a good place to start. – Gregor Thomas Feb 28 '22 at 16:48
  • @GregorThomas, Using environment variables for the MySQL password is deprecated because it's extremely insecure. The manual says this feature will be removed in a future version of the product (see https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html). – Bill Karwin Feb 28 '22 at 17:57
  • @BillKarwin I mean using R environment variables as described at my link, not MySQL environment variables, so it will be unaffected by this deprecation. Certainly not the most secure option (other options are discussed in my link too) but it's a very easy way to test if that is part of OP's problem, still without including the password as plain text in this same block of code. – Gregor Thomas Feb 28 '22 at 18:15
  • @GregorThomas The 'myuser' doesn't have a password, do I still need to put param as " password='' "? – Pabort Mar 01 '22 at 08:47
  • @BillKarwin Thanks, I'll try this at home as asoon as I get back, but I'll swear I also tried with port 7777 – Pabort Mar 01 '22 at 08:48
  • 1
    No, if `myuser` doesn't have a password than you shouldn't need it as part of the `dbConnect`. I do think that the port you use in the `mysql` command should match the port you use in `dbConnect()`. – Gregor Thomas Mar 01 '22 at 14:17
  • Nop, still not working, tried another port and Rstudio can't connect to that remote db through ssh. – Pabort Mar 08 '22 at 08:36

0 Answers0