Querying a database from a server running R requires three things:
- Network security that allows you to communicate between the machines
- Drivers installed on the R server
- 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.