Connecting to MS SQL Server in R

Thursday, November 15, 2018 · 2 minute read

There are dozens of ways to connect to an MS SQL Server database in R. You can use a number of packages, and within those packages, there are various methods for connecting and running SQL queries via an R script.

The easiest method I’ve found for a traditional Windows user with access to an MS SQL Server instance is what I’m going to demonstrate.

Setting up a System DSN

  1. In Windows, if you hit the start menu and search for “odbc”, you’ll get an app called “ODBC Data Source Administrator (64-bit).” Open that.
  2. Find the System DSN tab, and choose Add. I always choose SQL Server Native Client 11.0 (don’t ask me why). Hit finish.
  3. Name it something (maybe the same name as the instance name), and the full server name. I just click through all the following steps and choose to use Windows Authentication. Hit finish.
DSN Setup

DSN Setup

RStudio set up

Once you have the system DSN set up above, calling that is simple!

Install and call up the DBI and odbc packages like so:

install.packages("DBI")
install.packages("odbc")

library(DBI)
library(odbc)

From there, you can connect to the instance:

con <- dbConnect(odbc::odbc(), "DSN name from step above")

Now that you have a connection established you can run any SQL query you like by sending a query and then fetching:

query <- dbSendQuery(con, "select * from table")
df <- dbFetch(query)

I’ve found this to be a lot easier than trying to enter all the ODBC connection string details in an R function. Instead, using the Windows ODBC wizard saves all that info in the System Data Source, and you just tell R to go out and reference it.