0

I'm looking to connect SQL Server with R using the odbc and DBI library for the first time on OSX Big Sur 11.1 but when I run dbConnect() I get the following error:

library(odbc)
library(DBI)
sql_conn <- dbConnect(
  odbc::odbc(),
  driver = "/usr/local/lib/libmsodbcsql.13.dylib",
  server = "xxxxx",
  Database = "xxxxx",
  uid = "xxxxx",
  pwd = "xxxxx",
  port = xxxx)

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libmsodbcsql.13.dylib' : file not found

Literally the file with the exact path /usr/local/lib/libmsodbcsql.13.dylib exists and the error is still saying it doesn't. Here is a screenie of my Finder: Finder SS of /usr/local/lib/libmsodbcsql.13.dylib

I tried assigning the driver argument in dbConnect() to the filepath but to no avail. This is the same error that happens when I assign "{ODBC Driver 13 for SQL Server}" to driver.

I have reinstalled my drivers countless times using the info found on Microsoft's website and I think I may have installed both the ODBC Driver 17 and ODBC Driver 13 for SQL Server which the troublshooting on Microsoft seems to point to being an issue getting the ODBC connection to work. Here's the thread that talks about this: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/known-issues-in-this-version-of-the-driver?view=sql-server-ver15#connectivity

How do I uninstall a UnixODBC driver and is that the correct solution to getting my SQL Server connection working?

BLI
  • 1
  • If you go to _System Preferences > Security & Privacy_ and look on the General tab, is there a prompt asking you to grant permissions to SQL Server, R, or anything else? – AlwaysLearning Jan 11 '21 at 09:00
  • Also, your screenshot indicates that _/usr/local/lib/libmsodbcsql.13.dylib_ is a link - could the link target have been deleted? – AlwaysLearning Jan 11 '21 at 09:02

1 Answers1

1

I got a very similar error today. I learned that when a library comes up missing, just like you observed, it can be present and still give this error. But it may depend on other libraries that for one reason or another can't be found. As per the most popular answer on this thread, you can run the ldd command to determine which other libraries can't be found. In my case, there were a few that had been installed in locations readable only by root, and I also had to create a symlink with the name of one library to the name of the more current version.

Again, that command is

# ldd /path/to/your/socalled/missing/file.so

You might get some results that look like

libodbcinst.so.1 => not found

And the solution could be

cd /lib64
sudo ln -s libodbcinst.so.2.0.0 libodbcinst.so.1

or

cd /usr/local/your.app
sudo chown a+rx lib
cd lib
cd chown a+rx *.so
Bradley Mecham
  • 41
  • 5