Working with MS SQL server on non-windows systems

July 15, 2015

(This article was first published on Data Science Notes - R, and kindly contributed to R-bloggers)

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

  1. RODBC
  2. RJDBC
  3. rsqlserver

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:

  1. Install rJava. There are a lot of manuals for diffrent OS on the internet.
  2. Install RJDBC.
  3. 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" , 
mssql_addr <- ""
mssql_port <- "1433"
domain <- "YOUR_DOMAIN"
connection_string <- paste0("jdbc:jtds:sqlserver://", mssql_addr, ":", mssql_port, 
                            ";domain=", domain)
conn <- dbConnect(drv, 
                  user = 'user_name', 
                  password = "********")
query <- "select count(*) from your_db.dbo.your_table"
cnt <- dbGetQuery(conn = conn, statement = query)

To leave a comment for the author, please follow the link and comment on their blog: Data Science Notes - R. offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.


Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)