Connecting to SQL Server from R on a Mac with a Windows domain user

July 23, 2019
By

[This article was first published on R – David's blog, 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.

Connecting to an SQL Server instance as a Windows domain user is relatively straightforward when you run R on Windows, you have the right ODBC driver installed, and your network is setup properly. You normally don’t need to supply credentials, because the ODBC driver uses the built-in Windows authentication scheme. Assuming your odbcinst.ini file includes an entry for SQLServer, you typically just need the following:

con <- odbc::dbConnect(
  odbc::odbc(),                 
  Driver = "SQLServer",
  Server = "mysqlhost",
  Database = "mydbname",
  Port = 1433
)

But if you want to connect to SQL Server from a Mac, things are less simple.

Don’t bother installing the ODBC driver supplied by Microsoft; it just doesn’t work with a Windows domain user. No matter what I tried, I always got the following error message: Error: nanodbc/nanodbc.cpp:950: 28000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'dlindelof'. I’ve tried setting the user id to domain\\username, I’ve tried passing an extra parameter DOMAIN, all to no avail.

As far as I could determine, it simply is not possible to connect to SQL Server with a domain user using the ODBC driver supplied by Microsoft. Instead, obtain the open-source FreeTDS driver. Once installed you should see its libraries installed as /usr/local/lib/libtdsodbc.so. Edit your /usr/local/etc/odbcinst.ini file accordingly:

freetds                              = Installed
# ...
[freetds]
Driver = /usr/local/lib/libtdsodbc.so

You can, but don’t need to, also edit /usr/local/odbc.ini and /usr/local/etc/freetds.conf if you want to specify human-friendly aliases to specific database connections. I never used that.

You can now create a database source in R using the usual connection parameters, with the important gotcha that (unless you edit freetds.conf) you must specify the port number. The username must be prefixed by the domain and a double backslash (to prevent escaping). Putting it all together, my connection call looks like this:

con <- odbc::dbConnect(
   odbc::odbc(),                 
   Driver = "freetds",
   Server = "mysqlhost",
   Database = "mydbname",
   uid = "domainname\\username",
   pwd = "somepassword",
   Port = 1433
 )

To leave a comment for the author, please follow the link and comment on their blog: R – David's blog.

R-bloggers.com 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.



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.

Search R-bloggers

Sponsors

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)