Can't open lib '/opt/homebrew/lib/libmsodbcsql.17.dylib' from M1 Mac ARM64

[realMBP]

I can connect to MS SQL Server from my Bash terminal but I'm not having any luck connecting from RStudio. I'm using a new M1 Macbook running native ARM64 homebrew. I have no issues setting this up on my old Mac running intel chip so I am wondering if native ARM64 is just not compatible? Can anyone confirm they can connect to SQL Server? I've read some posts about connecting using an x86 emulator but nothing for native ARM64 odbc compatibility in RStudio.

The error message when connecting to the Driver:

dbConn <- dbConnect(odbc(),
+                     Driver= "ODBC Driver 17 for SQL Server",
+                     Server="my-server-ip",
+                     Port= 1433,
+                     Database="my-db-name",
+                     UID= myusername,
+                     PWD= mypassword)

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/lib/libmsodbcsql.17.dylib' : file not found

Attempting to connect using the DSN throws the same error:

dbConn <- dbConnect(odbc(),
+                     DSN = "SQLServer",
+                     UID= myusername,
+                     PWD= mypassword)

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/lib/libmsodbcsql.17.dylib' : file not found

I verified that the symlink libmsodbcsql.17.dylib exists in /opt/homebrew/lib/ and points to /opt/homebrew/Cellar/msodbcsql17/17.8.1.1/lib/libmsodbcsql.17.dylib

I also added a symlink for libmsodbcsql.17.dylib in /usr/local/lib/ pointing it to the same file in /opt/homebrew/lib/

I have tried modifying the path in odbcinst.ini to the actual file in /opt/homebrew/Cellar/msodbcsql17/17.8.1.1/lib/libmsodbcsql.17.dylib but still get the same error.

I have tried other combinations of symlinks for odbc.ini and odbcinst.ini in my local user home directory and also in the /etc/ directory but didn't make any difference.

Background:

1. I installed homebrew, unixodbc and MS Driver 17 for SQL Server. MS supports native ARM64 from version 17.8.

2. I installed openssl1.1, removed the openSSL symlink at /opt/homebrew/opt/openSSL/ that pointed to openSSL3@ and created a new openSSL symlink that points to /opt/homebrew/Cellar/openssl@1.1/1.1.1l_1.

After the openSSL change I was able to successfully connect to SQL Server from Bash terminal but RStudio still couldn't even find the driver with odbcListDrivers().

Checked the unixODBC environment and it's all correct. Note that ARM64 homebrew files use /opt/homebrew/ instead of /usr/local/ which was used for x86 machines.

 myuser@realMBP  ~  odbcinst -j                                                                                             
unixODBC 2.3.9
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/yoitsdre/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

I also set environment variable using:

export ODBCINI=/opt/homebrew/etc/odbc.ini                                                                      
export ODBCSYSINI=/opt/homebrew/etc/

3. I made some progress after adding these 2 symlinks; it allowed RStudio to find the driver:

ln -s /opt/homebrew/etc/odbcinst.ini /usr/local/etc/odbcinst.ini                                      
ln -s /opt/homebrew/etc/odbc.ini /usr/local/etc/odbc.ini

But after finding the driver and attempting to connect I get the "Can't open lib...." error.

[technocrat]

Problems like this would have taken M1 off my wishlist if I hadn't already thrown up my hands and gone to Ubuntu. It looks like you've been down most of the available rabbit holes. The only think that comes to mind is to replace the symlink with a copy to /opt/homebrew/lib, confirm it is readable and see if that fixes the problem.

(If I should wake up to find that Santa had put an M1 under my tree, I'd set up a virtual Ubuntu to run R.)

[realMBP]

@technocrat Thanks, I tried replacing the symlink libmsodbcsql.17.dylib with a copy of the actual file but still no luck.

[technocrat]

@realMBP

/opt/homebrew/etc

and that's in $PATH? (Like I said, it was just this type of bug hunt that drove me away from the otherwise beautiful Garden of Apple—like a great lover who turns into a wearisome spouse.

[realMBP]

This is what I get for $PATH:
PATH=/opt/homebrew/bin:/opt/homebrew/sbin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin

[technocrat]

Try adding opt/homework/etc

[realMBP]

Still can't connect to SQL from RStudio but isql connects without a problem.

I tried adding /opt/homebrew/etc to $PATH but it still won't connect. I also tried adding /opt/homebrew/lib to $PATH but no luck either.

Any other suggestions? Or at least has anyone else with a M1 Mac able to ODBC connect to SQL??

[technocrat]

The sour grapes part of me wants to blame it on the M1, since I just discovered that I have to find my old Apple SSD and swap out my 1T OWC one dto be able to do Monterey. Therefore, I'm resisting replacing my 2015 laptop. But, really, I don't know.

This post might apply. The only other thing that comes to mind is the user under which RStudio is running.

[realMBP]

Finally got it to work. I uninstalled R and RStudio completely and reinstalled them using homebrew. The unixodbc, driver, R and RStudio all need to be installed in the ARM64 directory that homebrew uses. I think I originally installed R and RStudio prior to installing homebrew so they were installed in different directories and that's why it couldn't find the file it needed. Thanks again for your help @technocrat