Cannot Connect to SQL-Server via Python on MacOS with M1

Question

Environment

System: Darwin Kernel Version 21.6.0
Python: 3.11.1
pyodbc: 4.0.35
OS: MacOS Monterey (12.6.3)
DB: MS SQL Server (on vscode I am using mssql)
driver: ODBC Driver 18 for SQL Server

I am trying to connect to my company's SQL server with Python (Jupyter Environment) using pyodbc module on my machine and I am getting an error. I have followed this installation guide and I still can't get it to run.

Here is my connection string:

I got the driver from pyodbc.drivers() as ['ODBC Driver 18 for SQL Server'] so therefore,

cnxn_str = (
            "DRIVER={'ODBC Driver 18 for SQL Server'};"
            "SERVER=companyserver.net;"
            "DATABASE=somedatabase;"
            "UID=myuid;"
            "PWD=mypassword;")
cnxn = pyodbc.connect(cnxn_str)

This is the error I am getting:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib ''ODBC Driver 18 for SQL Server'' : file not found (0) (SQLDriverConnect)")

Here is what I have tried:

I tried doing the troubleshoot provided in the Microsoft ODBC driver installation document:

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

and it didn't work either, secondly I tried odbcinst -j command and here is what I got:

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/myname/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

cat /opt/homebrew/etc/odbcinst.ini

Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib
UsageCount=1

Using the driver I made the connection string:

cnxn_str = (
            "DRIVER={'/opt/homebrew/lib/libmsodbcsql.18.dylib'};"
            "SERVER=companyserver.net;"
            "DATABASE=somedatabase;"
            "UID=myuid;"
            "PWD=mypassword;")
cnxn = pyodbc.connect(cnxn_str)

and I am still getting this error:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib ''/opt/homebrew/lib/libmsodbcsql.18.dylib' : file not found (0) (SQLDriverConnect)")

Does anyone have any idea what I am doing wrong?? Any help would be helpful.

Thank you so much!

What does `ls -la /opt/homebrew/lib/libmsodbcsql.18.dylib` output? There shouldn't be anything at all related in `/usr/local`, as per the advice on the page you linked, _Additionally, the Homebrew default directory changed with the ARM64 architecture, to `/opt/homebrew`. The paths in the Driver files section use the x64 Homebrew paths, which default to `/usr/local`, so your file paths will vary accordingly._ — AlwaysLearning, Mar 08 '23 at 02:27
You'll notice that `''ODBC Driver 18 for SQL Server''` in the error message is two sets of `'` characters, not single sets of `"` characters. Have you tried `"DRIVER={ODBC Driver 18 for SQL Server};"` without the `'` characters as per the documentation? — AlwaysLearning, Mar 08 '23 at 02:30

score 0 · Answer 1 · answered Mar 08 '23 at 14:48

Here is the solution to my question: I used Rene B. answer to the question to solve it. In my case brew install msodbcsql mssql-tools wasn't properly installed. I also uninstall and reinstalled unixodbc and then installed msodbcsql mssql-tools which finally worked for me.

Cannot Connect to SQL-Server via Python on MacOS with M1

1 Answers1