This is not a question so much as an attempt to capture some info for use by other travelers. I spent a full day trying to get RJDBC to connect from my Mac to my SQL Server which requires Windows Authentication. @taras did a good job documenting setting up odbc to use the Simba drivers to connect to SQL Server from a Mac with Win Authentication. I, however, had issues with that method not handling writes of more than 1024 records. I could read fine, but I need to write many thousands of records to SQL Server so this is a big deal.
Here are a few things I tried, and the outcome:
Simba ODBC Driver:
Unable to write more than 1024 rows using theodbcpackage. Writing usingRODBCseems to do slow record by record inserts. Not a good option.
FreeTDS ODBC Driver:
I was never able to make this driver work with theodbcpackage. Although others have made it work with R and Python.
Microsoft JDBC Driver:
Buried in the documentation it says that the MSFT JDBC driver does not support Windows Authentication from non Windows based machines. Thanks Microsoft. So not going to work for me from Mac.
JTDS JDBC Driver:
This ended up being what worked for me. But not without some effort. Elaborated below.
Getting rJava up and running on the Mac in order to use RJDBC was my first speed bump. This article helped considerably:
rJava in Rstudio from Mac: Run rJava with RStudio under OSX 10.10, 10.11 (El Capitan) or 10.12 (Sierra) · MTFA/CohortEx Wiki · GitHub
The bit near the end where they make a symlink to libjvmdylib was critical.
After I had rJava up and running I reinstalled the RJDBC package from source just to be sure everything was linked up and working.
Then I installed JTDS from Sourceforge (yeah, your corp firewall may block sourceforge because they made bad business choices and should feel bad. But, you know, that's why you have a wifi dongle in your desk drawer and a guest wifi network)
JTDS: http://jtds.sourceforge.net/
I opened the zip with the latest JTDS (from 2013!) and copied the whole directory to /usr/local/etc/jtds-1.3.1-dist on my Mac. Then from within R I could connect like this:
drv <- JDBC("net.sourceforge.jtds.jdbc.Driver",
"/usr/local/etc/jtds-1.3.1-dist/jtds-1.3.1.jar")
con.string <- "jdbc:jtds:sqlserver://my_server:1433;databaseName=my_db;domain=my_domain;IntegratedSecurity=true;"
con <- dbConnect(drv, con.string, "username", "password")
Then I could run some write tests like this:
n <- 5025
tst <- data.frame(x=rnorm(n), y=rnorm(n), z=rnorm(n))
RJDBC::dbWriteTable(con, 'my_test_rjdbc', tst, append=TRUE, overwrite=FALSE)
It's worth noting that in the connection string, you should replace my_db and my_domain with your database name and corporate domain name.
Then in the dbConnect statement username and password should be replaced with your username & pass but the quotes should remain. And your username should not include domain// as you might do in other connections, because your domain's already in the connection string.
The part that turns on Windows Authentication is the IntegratedSecurity=true; part of the connection string.