Issue making a database connection with keyring

keyring

#1

I'm trying to create a database connection using keyring, following this approach: https://db.rstudio.com/best-practices/managing-credentials/

Running on Windows

However, keep getting an error:

" Error in b_wincred_i_get(target) : 
  Windows credential store error in 'get': Element not found. " 

And set it up like this:

keyring::key_set_with_value(service = "db_read", username = "Thijs" ,password = "XXXXXXXXXXXXXXXXXX")

db_read <- DBI::dbConnect(
  RMySQL::MySQL(),
  host = "host.com",
  dbname = "a",
  user = keyring::key_list("db_read")[1,2],
  password = keyring::key_get("db_read"),
  port = 0000
)

Thanks


#2

The instructions assume (possibly erroneously) that you have only one row in the keyring table.

What is the result of:

nrow(key_list("test"))

?

If you have multiple rows in the keyring table, then you may have to extract the correct value using something like:

keyring::key_list("test") %>% 
  dplyr::filter(service == "db_read") %>% 
  .$username

I will also say that I'm using Windows, and that I don't get this error message. Can you please provide your session info?

sessioninfo::session_info()

(It might be that some of your packages are out of date.)


#3

Thanks, for you Reply,

> nrow(keyring::key_list("test"))
[1] 1

> sessioninfo::session_info()

─ Session info ───────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.4.2 (2017-09-28)
 os       Windows >= 8 x64            
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 tz       Europe/Berlin               
 date     2018-10-08                  

─ Packages ───────────────────────────────────────────────────────────────────────────────────────
 package      * version    date       source                             
 aider        * 0.0.1.02   2018-10-04 Github (konradsemsch/aider@ffc9068)
 assertthat     0.2.0      2017-04-11 CRAN (R 3.4.2)                     
 backports      1.1.1      2017-09-25 CRAN (R 3.4.1)                     
 bindr          0.1.1      2018-03-13 CRAN (R 3.4.4)                     
 bindrcpp       0.2.2      2018-03-29 CRAN (R 3.4.4)                     
 broom        * 0.4.2      2017-02-13 CRAN (R 3.4.0)                     
 caret        * 6.0-80     2018-05-26 CRAN (R 3.4.4)                     
 cellranger     1.1.0      2016-07-27 CRAN (R 3.4.2)                     
 class          7.3-14     2015-08-30 CRAN (R 3.4.2)                     
 cli            1.0.0      2017-12-11 Github (r-lib/cli@ab1c3aa)         
 clisymbols     1.2.0      2017-05-21 CRAN (R 3.4.3)                     
 codetools      0.2-15     2016-10-05 CRAN (R 3.4.2)                     
 colorspace     1.3-2      2016-12-14 CRAN (R 3.4.2)                     
 crayon         1.3.4      2017-09-16 CRAN (R 3.4.2)                     
 CVST           0.2-1      2013-12-10 CRAN (R 3.4.3)                     
 DBI            1.0.0      2018-05-02 CRAN (R 3.4.4)                     
 ddalpha        1.3.1      2017-09-27 CRAN (R 3.4.3)                     
 DEoptimR       1.0-8      2016-11-19 CRAN (R 3.4.1)                     
 dimRed         0.1.0      2017-05-04 CRAN (R 3.4.3)                     
 dplyr        * 0.7.6      2018-06-29 CRAN (R 3.4.4)                     
 DRR            0.0.3      2018-01-06 CRAN (R 3.4.3)                     
 forcats      * 0.2.0      2017-01-23 CRAN (R 3.4.2)                     
 foreach        1.4.4      2017-12-12 CRAN (R 3.4.3)                     
 foreign        0.8-69     2017-06-22 CRAN (R 3.4.2)                     
 ggplot2      * 3.0.0      2018-07-03 CRAN (R 3.4.4)                     
 glue         * 1.3.0      2018-07-17 CRAN (R 3.4.4)                     
 gower          0.1.2      2017-02-23 CRAN (R 3.4.3)                     
 gtable         0.2.0      2016-02-26 CRAN (R 3.4.2)                     
 haven          1.1.0      2017-07-09 CRAN (R 3.4.2)                     
 here         * 0.1        2017-05-28 CRAN (R 3.4.3)                     
 hms            0.3        2016-11-22 CRAN (R 3.4.2)                     
 httr           1.3.1      2017-08-20 CRAN (R 3.4.4)                     
 ipred          0.9-6      2017-03-01 CRAN (R 3.4.3)                     
 iterators      1.0.9      2017-12-12 CRAN (R 3.4.3)                     
 jsonlite       1.5        2017-06-01 CRAN (R 3.4.2)                     
 kernlab        0.9-25     2016-10-03 CRAN (R 3.4.1)                     
 keyring        1.1.0      2018-10-05 Github (r-lib/keyring@76accc0)     
 lattice      * 0.20-35    2017-03-25 CRAN (R 3.4.2)                     
 lava           1.6        2018-01-13 CRAN (R 3.4.3)                     
 lazyeval       0.2.0      2016-06-12 CRAN (R 3.4.2)                     
 lubridate      1.7.1      2017-11-03 CRAN (R 3.4.2)                     
 magrittr     * 1.5        2014-11-22 CRAN (R 3.4.2)                     
 MASS           7.3-47     2017-02-26 CRAN (R 3.4.2)                     
 Matrix         1.2-11     2017-08-21 CRAN (R 3.4.2)                     
 mnormt         1.5-5      2016-10-15 CRAN (R 3.4.1)                     
 ModelMetrics   1.1.0      2016-08-26 CRAN (R 3.4.3)                     
 modelr         0.1.1      2017-07-24 CRAN (R 3.4.2)                     
 munsell        0.5.0      2018-06-12 CRAN (R 3.4.4)                     
 nlme           3.1-131    2017-02-06 CRAN (R 3.4.2)                     
 nnet           7.3-12     2016-02-02 CRAN (R 3.4.2)                     
 pillar         1.2.1      2018-02-27 CRAN (R 3.4.4)                     
 pkgconfig      2.0.1      2017-03-21 CRAN (R 3.4.2)                     
 pls            2.6-0      2016-12-18 CRAN (R 3.4.4)                     
 plyr           1.8.4      2016-06-08 CRAN (R 3.4.4)                     
 prodlim        1.6.1      2017-03-06 CRAN (R 3.4.3)                     
 psych          1.7.8      2017-09-09 CRAN (R 3.4.2)                     
 purrr        * 0.2.5      2018-05-29 CRAN (R 3.4.4)                     
 R6             2.2.2      2017-06-17 CRAN (R 3.4.2)                     
 Rcpp           0.12.17    2018-05-18 CRAN (R 3.4.4)                     
 RcppRoll       0.2.2      2015-04-05 CRAN (R 3.4.3)                     
 readr        * 1.1.1      2017-05-16 CRAN (R 3.4.2)                     
 readxl         1.1.0      2018-04-20 CRAN (R 3.4.4)                     
 recipes      * 0.1.3      2018-06-16 CRAN (R 3.4.4)                     
 reshape2       1.4.3      2017-12-11 CRAN (R 3.4.3)                     
 rlang          0.2.2      2018-08-16 CRAN (R 3.4.4)                     
 RMySQL         0.10.9     2016-05-08 url                                
 robustbase     0.92-8     2017-11-01 CRAN (R 3.4.3)                     
 rpart          4.1-11     2017-03-13 CRAN (R 3.4.2)                     
 rprojroot      1.2        2017-01-16 CRAN (R 3.4.2)                     
 rstudioapi     0.7.0-9000 2017-12-20 Github (rstudio/rstudioapi@109e593)
 rvest          0.3.2      2016-06-17 CRAN (R 3.4.4)                     
 scales         1.0.0      2018-08-09 CRAN (R 3.4.4)                     
 sessioninfo    1.0.1.9000 2017-12-11 Github (r-lib/sessioninfo@c871d01) 
 sfsmisc        1.1-1      2017-06-08 CRAN (R 3.4.3)                     
 stringi        1.1.7      2018-03-12 CRAN (R 3.4.4)                     
 stringr      * 1.3.0      2018-02-19 CRAN (R 3.4.4)                     
 survival       2.41-3     2017-04-04 CRAN (R 3.4.2)                     
 tibble       * 1.4.2      2018-01-22 CRAN (R 3.4.4)                     
 tidyr        * 0.8.1      2018-05-18 CRAN (R 3.4.4)                     
 tidyselect     0.2.4      2018-02-26 CRAN (R 3.4.4)                     
 tidyverse    * 1.2.1      2017-11-14 CRAN (R 3.4.4)                     
 timeDate       3043.102   2018-02-21 CRAN (R 3.4.4)                     
 withr          2.1.2      2018-03-15 CRAN (R 3.4.4)                     
 xml2           1.1.1      2017-01-24 CRAN (R 3.4.2)                     
 yaml           2.2.0      2018-07-25 CRAN (R 3.4.4)                     
>

#4

Hmmm. I'm not sure what to suggest here. You are using a slightly old version of R, with some packages that are also not the most recent. I also notice that you have a github version of keyring.

I would suggest that you update at least the keyring package, and probably ensure that all of the keyring dependencies are up to date too.

Let me know if that makes a difference.


#5

The github version of keyring i just used to try if that might work.
For now i just updated to the latest version of R and reinstalled some packages.
However I'm still getting the same error:

 Error in b_wincred_i_get(target) : 
  Windows credential store error in 'get': Element not found. 

My current session info:

sessioninfo::session_info()
- Session info -----------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.1 (2018-07-02)
 os       Windows >= 8 x64            
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       Europe/Berlin               
 date     2018-10-09                  

- Packages ---------------------------------------------------------------------------------------
 package     * version date       lib source        
 assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.1)
 cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.1)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.1)
 DBI           1.0.0   2018-05-02 [1] CRAN (R 3.5.1)
 keyring       1.1.0   2018-07-16 [1] CRAN (R 3.5.1)
 lubridate     1.7.4   2018-04-11 [1] CRAN (R 3.5.1)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.1)
 R6            2.3.0   2018-10-04 [1] CRAN (R 3.5.1)
 Rcpp          0.12.19 2018-10-01 [1] CRAN (R 3.5.1)
 RMySQL        0.10.15 2018-05-13 [1] CRAN (R 3.5.1)
 rstudioapi    0.8     2018-10-02 [1] CRAN (R 3.5.1)
 sessioninfo   1.1.0   2018-09-25 [1] CRAN (R 3.5.1)
 stringi       1.2.4   2018-07-20 [1] CRAN (R 3.5.1)
 stringr       1.3.1   2018-05-10 [1] CRAN (R 3.5.1)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.1)
 yaml          2.2.0   2018-07-25 [1] CRAN (R 3.5.1)

[1] C:/Users/Thijs/Documents/R/win-library/3.5
[2] C:/Program Files/R/R-3.5.1/library

Thanks,


#6

Could you maybe print your session? So i could compare?

Thanks,


#7

You also need to specify the username in key_get(), if you specified it in key_set():

keyring::key_get("db_read", "Thijs")

#8

Amazing, thanks a lot!


#9

Looking at this now, the username could default to the current system user. We'll change this in the next version of keyring: https://github.com/r-lib/keyring/issues/60