Support

Setting up R to connect to SQL Server

Follow

One of R's core features is the ability to work with a variety of tools and data sources. Server side R is very often used to connect to Microsoft SQL Server as well as other popular relational databases. We here at RStudio have personally used RStudio Server this way for many years and can attest to its utility.

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; and (3) Configurations that allow you to connect from R. In general, we recommend that your IT group help you set up ODBC connects. Here's a few reasons why:

Network and security

Most organizations have strongly controlled procedures for accessing data and opening connections. Many organizations also have requirements on who can perform what functions on the servers. For example, some networking systems will allow you to leverage Active Directory so you do not have to reuse your password. In other systems, only certain geographies can communicate with a particular database. Because security and networking have standards, we strongly recommend that your IT/Ops be involved in connecting servers across the network. Your IT organization will help configure the network so your machines can "talk" to each other. Keep in mind, these are not R related functions. In fact any other analytic or data tool will require the same configuration. 

Drivers

You have to install the precise driver for your version of Linux and then configure it properly. For example, there is one SQL Server driver for Red Hat and another for SUSE, and each driver is designed to work with a specific version of SQL Server. IT should know how to install the right driver. Also, you will typically need root privileges to install drivers. Some organizations prevent its users from having root privileges. Regardless, we think it's a good idea to have IT set up the driver. If you are already able to query data from your desktop it is probably due to the fact that IT either set up the tools themselves or approved the specific tools that you are using.

RODBC

Once you have installed the driver and confirmed that you are able to query data from your server to your database, you can configure R to pull data in directly into the R session. RODBC is one of the more popular packages and is commonly used for SQL Server connections. The way you configure your connection depends on how the network is set up. In general, a connection looks something like this (taken from http://stackoverflow.com/questions/5720508/sql-server-rodbc-connection):

library(RODBC) dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')

In this example, the user is using a "trusted_connection", which likely refers to Active Directory. If the password is not being recognized by your network security, you might need to hard code the username and password with the "uid" and the "pwd" arguments. The driver might also require more specifics than is mentioned here. More help can be found in the vignettes (https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf). Again, your IT/Ops can help you configure this since it does not require any knowledge of R. Finally, it's good practice to have these R packages installed as root so that they are accessible and standard for everyone. That way if anyone has an issue with the connection, you or IT can fix that issue system wide.

Using R with databases is about as common as R itself, and it is proven technology that works. Setting up a connection requires an understanding of the network, server, and database. Furthermore, configuration requires testing an actual username with database privileges. It's hard for RStudio to provide details for every specific configuration because we don't have access to those things. Generally speaking, networking and configuring ODBC falls under IT functions. If you do not have an IT department, make sure you have permissions to install the correct driver.

Have more questions? Submit a request

Comments