Can't open lib ODBC Driver 18 for SQL Server / Rstudio / Mac OS

[XoliloX]

Dear all,

After countless hours trying to find a solution, I am back to square one. I started to follow the instructions on https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16. I saw there were possible issues with M1 chips, openssl or compatibility between OS and SQL driver but nothing worked so far. It seems that no matter what I do, Rstudio never finds the driver.

I run an Imac with Intel Core i7 and Sonoma OS (14.1.1 (23B81)).

My Rstudio session selected info are:

R version 4.3.2 (2023-10-31)
Platform: x86_64-apple-darwin20 (64-bit)
Running under: macOS Sonoma 14.1.1
Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib 
LAPACK: /Library/Frameworks/R.framework/Versions/4.3-x86_64/Resources/lib/libRlapack.dylib;  LAPACK version 3.11.0

Status:

• Running odbc::odbcListDrivers() returns an empty dataframe
• Running system("odbcinst -j") returns

  unixODBC 2.3.12
  DRIVERS............: /usr/local/etc/odbcinst.ini
  SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
  FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
  USER DATA SOURCES..: /Users/home/.odbc.ini
  SQLULEN Size.......: 8
  SQLLEN Size........: 8
  SQLSETPOSIROW Size.: 8

• running

  con <- DBI::dbConnect(drv = odbc::odbc(),
                    Driver = "ODBC Driver 18 for SQL Server",
                    server = Sys.getenv("servp"),
                    database = Sys.getenv("dtb"),
                    uid = Sys.getenv("userid"),
                    pwd = Sys.getenv("pwdp"))

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

All help would be much appreciated !

Best, D.

[v-chojas]

Can you try running isql -v -k '<your connection string here>' and see if that works?

[XoliloX]

Dear @v-chojas ,

Thanks for your fast reply ! I tried the line in terminal and got this:

**********************************************
* unixODBC - isql and iusql                  *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
*      iusql DSN [UID [PWD]] [options]       *
*      iusql "Connection String" [options]   *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* -L         Length of col display (def:300) *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      iusql -v WebDB MyID MyPWD -w < My.sql *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Datasources, drivers, etc:                 *
*                                            *
*      See "man 1 isql"                      *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
**********************************************

I don't understand what it means - as I'm actually "only" an R user... What do you think ?

Thanks a thousand times ! Best, D.

[v-chojas]

Are you sure you wrote the syntax correctly? That message only occurs if you supply invalid syntax to isql.

This GitHub project is for ODBC driver support only, we are not able to provide support for using driver with specific ODBC applications.

[XoliloX]

Hi @v-chojas,

Thanks for your answer. Understood. I tried again and indeed the message is different, but still no luck:

[S1T00][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
[08001][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2AF9
[08001][Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to <sqlpcrmpprd01.database.windows.net>. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[ISQL]ERROR: Could not SQLDriverConnect

Any idea on how to resolve this ?

Thanks in advance, D.

[XoliloX]

Hi all,

The issue was solved with multiple uninstall-install loops - unfortunately, no clear answer to provide.

D.