0

I want to use a DSN file to connect to a SQL Server database with the R library RODBC. I am able to create a User DSN and connect using the uid and pwd, but I want to exclude those from the script if possible. I have my dsn in the working directly for R which is validated by using getwd().

The examples I see show this command: 

odbcConnect(dsn = "<dsn_file>").

I have tried using the DBI and RODBC packages using dbconnect and odbcConnect. 

dbConnect(odbc::odbc(), 
"user_dsn",uid="username",pwd="password",database="db_name")

I would like to connect to the database without showing the uid and pwd in the script. I want to use a dsn file, So I can change the credentials in one place rather than in many scripts.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
David Davis
  • 23
  • 1
  • 5

3 Answers3

1

I have a DSN called "phone" that connects to our phone database. To connect and then query that database, I just do the following:

library(RODBC)
phone <- odbcConnect("phone")
Marc
  • 11
  • 3
0

I don't have access to a system set up to test it but I would check this link on how to connect Setting up R to connect to SQL Server and this one on SO for info about the RODBC library.

Using a DSN
con <- dbConnect(odbc::odbc(), "mydbalias")

here are information about Securing Credentials

Marc0
  • 181
  • 7
  • I tried using the dbConnect(odbc::odbc(), "alias") with no luck. If I have to use another route such as keyring I can but I just see a lot of examples showing just the dsn alias for connection. – David Davis Jan 07 '19 at 17:18
  • I don't have the environment to test it but have you tried using the DBI library instead of RODBC? There are many links in the pages provided that should help with setting things up, [this link](https://db.rstudio.com/databases/microsoft-sql-server/) is specific to MSSQL Server. [This link](https://github.com/r-dbi/odbc) has info about DBI on Github. – Marc0 Jan 07 '19 at 17:42
  • Yeah I have tried using the DBI library as well. I am going to try the keyring method since I can't get the DSN to work. – David Davis Jan 07 '19 at 17:49
  • Keyring works but will require two locations of management as I would like to use the DSN to store hostname/driver and the keyring will be used for the uid/pwd. – David Davis Jan 07 '19 at 18:56
0

With odbc package I use filedsn parametr (described on microsoft page). I think it should work with RODBC.

db_conn <- dbConnect(odbc::odbc(), filedsn="path/to/my/file/user_dsn.dsn")
Marek
  • 49,472
  • 15
  • 99
  • 121