Mask password from odbcDriverConnect

Question

I want to mask my password and username from my script. There are examples of how to do this, e.g. here: https://db.rstudio.com/best-practices/managing-credentials/

However, I can't get this to work with my script set-up.

Tried to use keyring but can't get it to work.

conn <- odbcDriverConnect("Driver=ODBC Driver 13 for SQL Server;
                           server=XXX,1433;
                           database=XXX;
                           uid=Needtohide;
                           pwd=Needtohide")

This code connects me to my DB. I have to use RODBC otherwise SQL tables won't pull back due to data ordering. I want to modify my script to hide the username or at least the password please. Thanks

I get a cannot connect error with

conn <- odbcDriverConnect("Driver=ODBC Driver 13 for SQL Server; server=XXX,1433; database=XXX; uid=myuserID; pwd=key_get('DB', 'myuserID')")

The main problem with all the solutions is that RODBC uses a connection STRING and so how to put an additional function into that.

keyring::key_list("my-database")[1,2], keyring::key_get("my-database") I replaced username and pass with these but it then won't connect — Matthew Appleyard, Jul 03 '19 at 11:40
Have you tried the environment variables in `.Renviron` solution in that linked article? — neilfws, Jul 03 '19 at 11:42
check this https://theautomatic.net/2019/06/25/how-to-hide-a-password-in-r-with-the-keyring-package/ — s.brunel, Jul 03 '19 at 11:51
@s.brunel - this works in R on its own. But then how do I write that into my odbcDriverConnect. I get an error with conn <- odbcDriverConnect("Driver=ODBC Driver 13 for SQL Server; server=XXX,1433; database=XXX; uid=myuserID; pwd=key_get('DB', 'myuserID')") — Matthew Appleyard, Jul 03 '19 at 12:47
@neilfws I may go back to try this, but this solution seems less great due to the fact it displays the password in the environment panel then. — Matthew Appleyard, Jul 03 '19 at 12:48
What OS are you working on, and have you considered using Kerberos to authenticate? With an active Kerberos ticket that you can generate using a keytab and Simon Urbanek's [krb5](https://github.com/s-u/krb5) package the need for plaintext credentials can be eliminated and you can use a connection string like `"Driver=ODBC Driver 13 for SQL Server;Server=Name.Domain.com,1433;Database=Database;Trusted_Connection=yes"`. If you're interested, I can post as an answer. — Matt Summersgill, Jul 03 '19 at 14:21

Konrad Rudolph · Accepted Answer · 2019-07-03T15:29:53.110

1

You need to read up on the basics of string manipulation.

However, you don’t actually need to pass a connection string at all, if you use the appropriate connection function (but this doesn’t provide quite the same kind of flexibility; ODBC bindings for DBI may be a remedy). I strongly recommend you do that instead of manipulating connection strings manually, as it’s much cleaner.

But just for the purpose of discussion, you essentially have three choices to construct the connection string:

Use paste0:

conn_str = paste0(
    'Driver=ODBC Driver 13 for SQL Server; server=XXX,1433; database=XXX; ',
    'uid=myuserID; pwd=', key_get('DB', 'myuserID')
)

Use sprintf:

conn_str = sprintf(
    'Driver=ODBC Driver 13 for SQL Server; server=XXX,1433; database=XXX; uid=myuserID; pwd=%s',
    key_get('DB', 'myuserID')
)

Use glue from the package {glue}:

conn_str = glue('Driver=ODBC Driver 13 for SQL Server; server=XXX,1433; database=XXX; uid=myuserID; pwd={key_get("DB", "myuserID")}')

In all cases you then call odbcDriverConnect(conn_str).

edited Jul 03 '19 at 15:29

answered Jul 03 '19 at 14:12

Konrad Rudolph

530,221
131
937
1,214

I think your commentary on the `RODBC` package is off topic, out of line, and actually inaccurate. `RODBC` has been working stably for decades, that to me is an asset, not "dusty". Further, based on my own benchmarking for my workloads, `RODBC` is actually faster than the newfangled `odbc` package for a large number of use cases, and equally supports the use of named data sources in lieu of connection strings. – Matt Summersgill Jul 03 '19 at 14:27
@KonradRudolph - I will try your suggestions now. As I said the reason for using RODBC is because ODBC is incapable of pulling back an SQL query in many cases due to the column order. Only RODBC worked for me. – Matthew Appleyard Jul 03 '19 at 14:38
Point 1 worked for me, thanks. But I stand by my other comments: the major issues with ODBC are listed in this question which essentially means many DB's it won't work with: https://stackoverflow.com/questions/45001152/r-dbi-odbc-error-nanodbc-nanodbc-cpp3110-07009-microsoftodbc-driver-13-fo – Matthew Appleyard Jul 03 '19 at 15:02
@MattSummersgill I was unaware (and am quite baffled) by the performance difference. Fair enough. I had also misread the the documentation, and thought it was last updated in 2002 (rather than, as is the case, in 2017), hence my “old and dusty” remark. – Konrad Rudolph Jul 03 '19 at 15:31

Mask password from odbcDriverConnect

1 Answers1