Can't open lib 'ODBC Driver 18 for SQL Server' in R on MacOS (CPU M2)

On a MacOS Ventura with a Apple Silicon M2 CPU, I have installed the Microsoft ODBC driver 18 for SQL Server following the instructions provided by Microsoft at: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos

When using standard DBI connection string in R:

db <- config::get("localhost")
conn <- DBI::dbConnect(odbc::odbc(),
                       Driver   = "ODBC Driver 18 for SQL Server",
                       Server   = "localhost",
                       UID      = db$uid,
                       PWD      = db$pwd,
                       Port     = 1433,
                       Database = db$database

When running this code I get the following error:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found

I have the feeling that this indicates that there is something wrong with the connection between my R and the driver register in macOS. Unfortunately, I have not been able to identify how to correct the problem.

Does anybody have experience with this and can advise me on how to connect?

I appreciate your assistance!

Solution

It may just be that your install path isn't where R is looking. In the Rstudio docs, it's advised one can add the odbc driver path to .Renviron.

I found simply adding this was sufficient to resolve the issue:

# in ~/.Renviron
ODBCSYSINI=/opt/homebrew/etc