Connecting RStudio to SQL Server

RStudio includes the r-dbi/odbc interface, which enables you to develop R programs that can work with data from any database for which an ODBC driver is available. This blog shows how to use RStudio to connect to SQL Server from Linux by using Easysoft's SQL Server ODBC driver.

1. Download the SQL Server ODBC driver for Linux (x86).

   If your version of RStudio is 32-bit, download the 32-bit ODBC driver. If your version of RStudio is 64-bit, download the 64-bit ODBC driver. (To find out, in RStudio, choose Help > About RStudio.)

2. Install and license the SQL Server ODBC driver on the machine where RStudio is or will be installed.

   For installation instructions, refer to the ODBC driver documentation.

   Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft SQL Server ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

3. If they are not already present, install the following packages on your Linux system:

   sudo apt-get install unixodbc-dev unixodbc

   Although the SQL Server ODBC driver distribution includes the unixODBC Driver Manager libraries and header files, the r-dbi/odbc interface included with RStudio uses system unixODBC files by default. A future blog will address how to use r-dbi/odbc with a "custom" version of unixODBC.

4. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from RStudio. For example:

   [SQLSERVER_SAMPLE]
   Driver          = Easysoft ODBC-SQL Server
   Server          = my_machine\SQLEXPRESS
   User            = my_domain\my_user
   Password        = my_password
   # If the database you want to connect to is the default
   # for the SQL Server login, omit this attribute
   Database        = Northwind

5. Use isql to test the new data source. For example:

   cd /usr/local/easysoft/unixODBC/bin
   ./isql.sh -v SQLSERVER_SAMPLE

   At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

   If you are unable to connect, refer to this article and the SQL Server ODBC driver knowledge base for assistance.

6. If you have not already done so, install RStudio on this machine.
7. In RStudio, build and install the r-dbi/odbc interface:

   install.packages("odbc")
   library("odbc")

8. Connect to and query your SQL Server ODBC data source:

   con <- dbConnect(odbc::odbc(), "SQLSERVER_SAMPLE")	  
   dbGetQuery(con, "SELECT * FROM mytable")