rsql-serverrodbcdsn

How to use DSN file for connection to SQL Server using R


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.


Solution

  • 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