Connect R (RStudio) with RJDBC to Microsoft SQL Server Express 2008 R2

In this blog post I will describe how I connected RStudio running on an Ubuntu Linux Notebook through JDBC to a Microsoft SQL Express R2 2008 Server. An overview can be seen here:

Block diagram how it technically works

What I used:

Installing Microsoft SQL Server

So after downloading Microsoft SQL Server Express you have to go through the set up wizard. Afterwards you can start it.

SQL-Management

Then you have to make sure that a remote connection is possible. You have to do the following:

  1. Enable remote connections on Microsoft SQL Server
  2. Enable TCP/IP connections in SQL Server Configuration Manager
  3. Set  Port to 1433 in IPAll configuration and delete everything from the dynamic Port
  4. Open the Windows Firewall for Port 1433

SQL-Configuration-Manager

 

You can check if this worked by using netstat in a cmd console.

Install R

The version of R shipped with Ubuntu 14.04 wasn’t recent enough (3.0.2) I had problems to install a recent plyr version (newer then 1.8.1). So I used the following tutorial. How To Set Up R on Ubuntu 14.04 to set up R 3.2.3.

Install RJDBC

It should be as easy as:

install.packages("RJDBC")

I had problems getting rJava running with Java 8 because libjvm.so wasn’t found. I did the following in a terminal:


export LD_LIBRARY_PATH="/usr/lib/jvm/java-8-oracle/jre/lib/amd64/server:/usr/lib/x86_64-linux-gnu/"
sudo -E updatedb
sudo R CMD javareconf

You should start RStudio from the same Terminal as well. Afterwards it worked.

Install RStudio

To install RStudio just download the deb package and install it.

RStudio

Install JDBC Driver

This is straight forward. Just download it and unpack it.

tar xzf ~/Downloads/sqljdbc_3.0.1301.101_enu.tar.gz

Afterwards you will find the driver here:

/etc/sqljdbc_3.0/enu/sqljdbc4.jar

The big moment

Now you are read to connect to your server:

options( java.parameters = "-Xmx2g" )
require(RJDBC)
# specify the driver
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
"/etc/sqljdbc_3.0/enu/sqljdbc4.jar")
conn <- dbConnect(drv, "jdbc:sqlserver://192.168.178.1", "user", "password")
# then build a query and run it
version <- dbGetQuery(conn, "SELECT @@version")
version
# Should output: 1 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) \n\tJun 28 2012 08:42:37 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)\n

If you need support feel free to contact us.

8 thoughts on “Connect R (RStudio) with RJDBC to Microsoft SQL Server Express 2008 R2

  • Hi. Could you also show how to use the src_sqlserver to connect to
    sql server. This functionality allows me to use the tbl function from
    dplyr which is a more efficient way of handling data.

    I think the step by step instructions with snapshots were great
    Thanks

    • Hi Ajay,
      I wasn’t aware of a specific package used to connect to MSQL Server. https://github.com/imanuelcostigan/RSQLServer/ is a DBI driver and uses an open source jdbc driver. This is a little bit different from my approach. It seems that RSQLServer is well documented. You should try to read the documentation on github.
      Unfortunately I currently don’t have the time to elaborate on this topic.

      Hope that helps
      Manuel

    • Hi Ajay,
      I am not an MSQL Server Expert. Therefore I don’t know how to enable windows authentification on MSQL server. I would guess that if you enable it you can easily use your windows credentials for authentification.

      Hope that helps
      Manuel

  • Hello,

    I tried the same step mentioned above to connect Microsoft SQL server in R by using RJDBC but ,
    getting below error

    .jcall(drv@jdrv, “Ljava/sql/Connection;”, “connect”, as.character(url)[1], :
    java.lang.NoClassDefFoundError: javax/xml/bind/DatatypeConverter

    Your help is valuable
    Thanks

  • I can’t use JAVA and alternative option for JAVA is Amazon Corretto 11 JDK (64 Bit) 11.0.8.10 & Open JDK (RedHat) 8u181 .

Leave a Reply to Manuel Blechschmidt Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.