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!

Answer 1

uninstalling and installing unixodbc didn't work for me. I did these steps:

• brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release ,then
• brew install msodbcsql mssql-tools