Working with MS SQL server on non-windows systems

Dmitriy Selivanov — written Jul 16, 2015 — source

As I know, there are few choices to connect from R to MS SQL Server:

But only second option can be used on mac and linux machines. Here is nice stackoverflow thread.

Most of the people suggest to use microsoft sql java driver. But there is a case when this will not help – windows domain authentification. In this situation I found the only working solution is to use nice jTDS. It not only solve this problem, but also outperform Microsoft JDBC Driver.

So to use it you have to:

Install rJava. There are a lot of manuals for diffrent OS on the internet.
Install RJDBC.
Download jTDS from official site. Unpack it.

Now you can easily connect to your source:
(assume jtds-1.3.1, which is unpacked into ~/bin )

drv  <-  JDBC("net.sourceforge.jtds.jdbc.Driver"  ,  
            "~/bin/jtds-1.3.1-dist/jtds-1.3.1.jar") 
mssql_addr  <-  "10.0.0.1" 
mssql_port  <-  "1433" 
domain  <-  "YOUR_DOMAIN" 
connection_string  <-  paste0("jdbc:jtds:sqlserver://",  mssql_addr,  ":",  mssql_port,  
                            ";domain=",  domain) 
conn  <-  dbConnect(drv,  
                  connection_string,  
                  user  =  'user_name',  
                  password  =  "********") 
query  <-  "select count(*) from your_db.dbo.your_table" 
cnt  <-  dbGetQuery(conn  =  conn,  statement  =  query)

tags: setup

text2vec 0.4 — Dmitriy Selivanov
text2vec 0.3 — Dmitriy Selivanov
Read from hdfs with R. Brief overview of SparkR. — Dmitriy Selivanov

Working with MS SQL server on non-windows systems

Related Articles