Read data from Microsoft SQL database on Remote Desktop via. R

Question

My data is loaded on Micorsoft SQL server on a Remote Desktop with Microsoft Windows Server 2012. I want to access Data via R. I know its possible via RODBC with

odbcConnect(dsn, uid="", pwd="")

But I am confused on what to enter on dsn.

Answer 1

it depends on whether you want to use ODBC DSN or DSN-less connection string, the type of authentication and the driver.

Here's an example of DSN-less connection string, using domain authentication and SQL Server driver:

odbcDriverConnect(connection="server=MYDB.mynet.tld;database=mydb_prod;trusted_connection=true;Port=1433;driver={SQL Server};TDS_Version=7.0;")

Here's another example, with SQL authentication and FreeTDS driver (e.g. on Mac or Linux)

odbcDriverConnect(connection="server=MYDB.mynet.tld;database=mydb_prod;uid=myuser;pwd=mypass;Port=1433;driver=FreeTDS;TDS_Version=7.0;")

See http://www.connectionstrings.com/sql-server/ for more examples of what can go in the "connection" element.

Here's a function I use to open connection in a cross-platform way:

require(RODBC)

connect <- function(host, db, user=NULL, pass=NULL, platform="win" ){

  # TODO: Check input paramaters and add a branch for SQL auth on windows

  if(platform == "win")
  {
    c <- odbcDriverConnect(connection=paste0("server=",host,
                                             ";database=",db,
                                             ";trusted_connection=true;Port=1433;driver={SQL Server};TDS_Version=7.0;"))

    if(class(c) == 'RODBC') 
    {  
      writeLines("Successfilly opened connection to db")
      return(c)
    } 
    else
    {
      writeLines(paste0("Error opening connection: ", as.character(c)))
    }
  }  

  if(platform == "mac")
  {
    c <- odbcDriverConnect(connection=paste0("server=",host,
                                             ";database=",db,
                                             ";uid=",user,
                                             ";pwd=",pass,
                                             ";Port=1433;driver=FreeTDS;TDS_Version=7.0;"))

    if(class(c) == 'RODBC') 
    {  
      writeLines("Successfilly opened connection to db")
      return(c)
    } 
    else
    {
      writeLines(paste0("Error opening connection: ", as.character(c)))
    }
  }  
}