Working with MS SQL server on non-windows systems

· · Read in about 1 min · (178 words)

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)