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

[dilshatu]

Environment

• Python: 3.8.16
• pyodbc: 4.0.35
• Chip: Apple M1 Pro
• OS: macOS Ventura 13.2
• DB:
• driver: Microsoft ODBC

Issue

I have Macbook M1 Pro, and trying to connect to MS SQL server using pyodbc. This is the error I get when trying to connect to sql server using pyodbc:

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

But file exists in the indicated location.

Some additional info:

odbcinst -j
unixODBC 2.3.11
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/**<user_name>**/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

(base) ~ % cat /opt/homebrew/etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib
UsageCount=1

pyodbc.drivers() returns [ODBC Driver 18 for SQL Server]

[ektedar]

I am running into the same issue as well. This sounds like an issue with the new Mac M1 pros due to the ARM64 architecture instead of the x86.

https://github.com/microsoft/homebrew-mssql-release/issues/53#issuecomment-922208476

That suggestion above is attempting to run Rosetta terminal emulating a x86 architecture instead of ARM64 to properly install the ODBC drivers along with the libmsodbcsql.18.dylib. Although I haven't tried that solution myself, others have apparently and it was the solution for them.

Thought I'd share, let me know if that leads to any resolution because I will be trying this myself once I am home.

[v-chojas]

Try running otool -L on the driver lib and see if you are missing any dependencies. The ODBC Driver for SQL Server is available for arm64 on macOS, so make sure that you did install the arm64 version of the driver.

[basnijholt]

I was running into this issue as well, I posted my solution here https://github.com/microsoft/homebrew-mssql-release/issues/53#issuecomment-1431964136.

Copying my message:

This comment here https://github.com/microsoft/homebrew-mssql-release/issues/53#issuecomment-922208476 helped me a lot.

I have written up some instructions to get pyodbc to work on the M1 with Microsoft ODBC drivers as well:

One can only use pyodbc when using Rosetta (x86 emulation). Follow the steps below.

Uninstall M1 versions of brew packages (if installed at all):

brew uninstall unixodbc msodbcsql17 mssql-tools freetds

Install x86 Homebrew alongside the ARM M1 homebrew:

arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Then use x86 homebrew like arch -x86_64 /usr/local/bin/brew install or use the following alias (add to ~/.bash_profile)

# Relies on having installed x86 brew like:
# arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
alias x86brew="arch -x86_64 /usr/local/bin/brew"
alias brew="/opt/homebrew/bin/brew"  # M1 version, to avoid from using x86 version accidentally

Install the ODBC packages.

x86brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
x86brew update
HOMEBREW_ACCEPT_EULA=Y x86brew install msodbcsql17 mssql-tools

Create an x86 conda env with:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 conda create -n $ENV_NAME python
conda activate $ENV_NAME
conda config --env --set subdir osx-64

or if using micromamba:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 micromamba create -n $ENV_NAME python
micromamba activate $ENV_NAME

Test with

python -c "import pyodbc"

[guanjingyu]

HOMEBREW_ACCEPT_EULA=Y x86brew install msodbcsql17 mssql-tools

I was running into this issue as well, I posted my solution here microsoft/homebrew-mssql-release#53 (comment).

Copying my message:

This comment here microsoft/homebrew-mssql-release#53 (comment) helped me a lot.

I have written up some instructions to get pyodbc to work on the M1 with Microsoft ODBC drivers as well:

One can only use pyodbc when using Rosetta (x86 emulation). Follow the steps below.

Uninstall M1 versions of brew packages (if installed at all):

brew uninstall unixodbc msodbcsql17 mssql-tools freetds

Install x86 Homebrew alongside the ARM M1 homebrew:

arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Then use x86 homebrew like arch -x86_64 /usr/local/bin/brew install or use the following alias (add to ~/.bash_profile)

# Relies on having installed x86 brew like:
# arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
alias x86brew="arch -x86_64 /usr/local/bin/brew"
alias brew="/opt/homebrew/bin/brew"  # M1 version, to avoid from using x86 version accidentally

Install the ODBC packages.

x86brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
x86brew update
HOMEBREW_ACCEPT_EULA=Y x86brew install msodbcsql17 mssql-tools

Create an x86 conda env with:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 conda create -n $ENV_NAME python
conda activate $ENV_NAME
conda config --env --set subdir osx-64

or if using micromamba:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 micromamba create -n $ENV_NAME python
micromamba activate $ENV_NAME

Test with

python -c "import pyodbc"

Hi basnijholt,

I am running on M2 Max platform and encountered the same issue, and I tried the installation steps you given, and created a x86brew, but error still occurs, just want to know if I was missing any step?

[Alexander-Leon-Bayer]

HOMEBREW_ACCEPT_EULA=Y x86brew install msodbcsql17 mssql-tools

I was running into this issue as well, I posted my solution here microsoft/homebrew-mssql-release#53 (comment). Copying my message: This comment here microsoft/homebrew-mssql-release#53 (comment) helped me a lot. I have written up some instructions to get pyodbc to work on the M1 with Microsoft ODBC drivers as well: One can only use pyodbc when using Rosetta (x86 emulation). Follow the steps below. Uninstall M1 versions of brew packages (if installed at all):

brew uninstall unixodbc msodbcsql17 mssql-tools freetds

Install x86 Homebrew alongside the ARM M1 homebrew:

arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Then use x86 homebrew like arch -x86_64 /usr/local/bin/brew install or use the following alias (add to ~/.bash_profile)

# Relies on having installed x86 brew like:
# arch -x86_64 /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
alias x86brew="arch -x86_64 /usr/local/bin/brew"
alias brew="/opt/homebrew/bin/brew"  # M1 version, to avoid from using x86 version accidentally

Install the ODBC packages.

x86brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
x86brew update
HOMEBREW_ACCEPT_EULA=Y x86brew install msodbcsql17 mssql-tools

Create an x86 conda env with:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 conda create -n $ENV_NAME python
conda activate $ENV_NAME
conda config --env --set subdir osx-64

or if using micromamba:

ENV_NAME="rosetta"
CONDA_SUBDIR=osx-64 micromamba create -n $ENV_NAME python
micromamba activate $ENV_NAME

Test with

python -c "import pyodbc"

Hi basnijholt,

I am running on M2 Max platform and encountered the same issue, and I tried the installation steps you given, and created a x86brew, but error still occurs, just want to know if I was missing any step?

Same here, unfortunately.

I can now import pyodbc, but when i try to execute a connection I get

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

I feel like i'm going crazy trying to solve this.

[v-chojas]

There is no ARM version of the v13 msodbcsql, so that obviously won't work.

After installing msodbcsql17/18 and before using pyODBC you can attempt to connect directly using isql to check whether driver works by itself first:

isql -v -k '<your connection string goes here>'

If that doesn't work, then it's not related to pyODBC and that should be troubleshot before attempting to use pyODBC with it.

[Alexander-Leon-Bayer]

There is no ARM version of the v13 msodbcsql, so that obviously won't work.

After installing msodbcsql17/18 and before using pyODBC you can attempt to connect directly using isql to check whether driver works by itself first:

isql -v -k '<your connection string goes here>'

If that doesn't work, then it's not related to pyODBC and that should be troubleshot before attempting to use pyODBC with it.

Absolutely nailed it. I was so hung up on trying to resolve the issue with the driver that when i moved on to the next error I didn't realize it was a problem with my connection string.

Thank you so much! Sometimes it just takes a second pair of eyes to see what's right in front of you