Working with MS SQL server on non-windows systems

[This article was first published on Data Science Notes - R, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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)