Setting up R to connect to SQL Server

Querying a database from a server running R requires three things:

1. Network security that allows you to communicate between the machines
2. Drivers installed on the R server
3. Configurations that allow you to connect to the database from R

In general, it is best to have your IT/Ops team take care of the Networking security and the installation of drivers, since these are things that they likely have security procedures around.  If you are a customer of RStudio, we recommend using the RStudio Professional Drivers, which are easy to install and designed to work with our products.

Then, when it comes to the connection from R to the database, we recommend using the odbc package, which is a DBI compliant interface to using ODBC drivers.  You can acquire the latest stable version from CRAN with install.packages("odbc"). 

In general, a connection looks something like this:

library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "mysqlhost",
                 Database = "mydbname",
                 UID = "myuser",
                 PWD = rstudioapi::askForPassword("Database password"),
                 Port = 1433)

The rstudioapi::askForPassword function will prompt the user for a password and reduce the need to store passwords in code. For more options on securing credentials, there is a dedicated article on the topic.  Note that there is also support for DSNs:

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

There is more information in the odbc package README and a great deal of documentation on this topic at http://db.rstudio.com/, as well as the specific section on SQL Server.