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:
What I used:
- Ubuntu Linux LTS 14.04 (Trusty)
- Windows Vista
- Microsoft SQL Server Express 2008 R2
- RStudio Desktop
- R 3.2.3 from the Uni Münster Mirror
- Microsoft JDBC Driver 3.0 for SQL Server
- RJDBC
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.
Then you have to make sure that a remote connection is possible. You have to do the following:
- Enable remote connections on Microsoft SQL Server
- Enable TCP/IP connections in SQL Server Configuration Manager
- Set Port to 1433 in IPAll configuration and delete everything from the dynamic Port
- Open the Windows Firewall for Port 1433
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.
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
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
Also how do arguments to dbconnect change if one connects
via Windows Authentication
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
Please use Java 8. The class that you are mentioning is not part of recent JDKs anymore.
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 .
Open JDK (RedHat) 8u181 should work.