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/220.127.116.11/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/18.104.22.168/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.
I installed homebrew, unixodbc and MS Driver 17 for SQL Server. MS supports native ARM64 from version 17.8.
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/Cellaremail@example.com/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/
- 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.