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

manamhr commented 1 year ago

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

Python: 3.9.7
pyodbc: 4.0.34
OS: Mac (M1)
DB:
driver: ODBC

Issue

I have Mac M1, trying to connect to Azure SQL server using pyodbc. my settings works well on my previous windows base laptop, but not here. This is the error I get when trying to connect to sql server using 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.

I checked lipo -info /opt/homebrew/Cellar/msodbcsql18/18.1.1.1/lib/libmsodbcsql.18.dylib

returns: Non-fat file: /opt/homebrew/Cellar/msodbcsql18/18.1.1.1/lib/libmsodbcsql.18.dylib is architecture: arm64

libmsodbcsql.18.dylib exists in /opt/homebrew/Cellar/msodbcsql18/18.1.1.1/lib/libmsodbcsql.18.dylib, but there is a shortcut in /opt/homebrew/lib/libmsodbcsql.18.dylib. I also changed Driver to the above address, still same result.

This was my process: I have installed ODBC V18 and brew using Microsoft instruction. when I run this on terminal works fine:

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) User_name@TMDLXNPPVJ4J ~ % 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]

I have also did these:

brew install unixodbc export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/2.3.11/lib" export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/2.3.11/include" pip install pyodbc

isql -v -k "DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx" works by the way

Please help. thanks

v-makouz commented 1 year ago

When you say isql -v -k "DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx" does that mean you have both 17 and 18 installed? And if so have you tried isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx"?

If isql can load the .so and pyodbc can't load the same exact .so, I wonder if pyodbc somehow runs under emulation and not in the ARM mode, and thus can't load the ARM driver.

manamhr commented 1 year ago

When you say isql -v -k "DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx" does that mean you have both 17 and 18 installed? And if so have you tried isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx"?

If isql can load the .so and pyodbc can't load the same exact .so, I wonder if pyodbc somehow runs under emulation and not in the ARM mode, and thus can't load the ARM driver.

sorry the correct one is: isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx"

That's my suspecion too. I have installed pyodbc under anaconda, and also using pip install on terminal. I tried installing through brew, but didn't work. How could I solve this issue? Thanks

manamhr commented 1 year ago

also when I run Jupiter notebook through terminal, pyodbc.driver returns null

v-makouz commented 1 year ago

It looks like the driver and unixODBC are correct ARM versions, what about python itself?

AraHaan commented 1 year ago

Does this support connecting to sql server 2016 perhaps?

v-chojas commented 1 year ago

Yes it does

dilshatu commented 1 year ago

I have exactly the same problem. Has it been resolved? And why is it closed?

v-makouz commented 1 year ago

In the original issue isql worked, but the connection through pyodbc didn't. So the driver and unixODBC were definitely correct, it seems either pyodbc was a non-ARM version, or it was loading a different driver or driver manager, than isql.

SebDigital commented 1 year ago

Hi, I am running into the same issue, has anyone has solved it? also when I run this one: isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx" My password has a ! and it created this error: bash: !": event not found

Thanks

v-chojas commented 1 year ago

Use single quotes to escape your connection string.

SebDigital commented 1 year ago

Thanks @v-chojas , worked well. So I am in the same situation as you @v-makouz . Did you find a solution?

-- pyodbc.drivers() Return [] so empty.

odbcinst -j show:

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

And connection using isql is working..

Thanks!

v-chojas commented 1 year ago

Run otool -L on the pyodbc module file (pyodbc.__file__) and see whether it is linking to same libodbc as the odbcinst.

SebDigital commented 1 year ago

Thanks @v-chojas ,

This is the result I have:

/Users/sebdigital/.local/share/virtualenvs/testconnect-7i8svD64/lib/python3.10/site-packages/pyodbc.cpython-310-darwin.so:
        /usr/local/opt/unixodbc/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
        /usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.36.0)
        /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0)

Thanks for your help

v-chojas commented 1 year ago

Do the same with the isql and odbcinst files and check which libodbc they are linking to.

SebDigital commented 1 year ago

Thanks @v-chojas , here are my results

odbcinst

/usr/local/bin/odbcinst:
        /usr/local/Cellar/unixodbc/2.3.11/lib/libodbcinst.2.dylib (compatibility version 3.0.0, current version 3.0.0)
        /usr/local/opt/libtool/lib/libltdl.7.dylib (compatibility version 11.0.0, current version 11.2.0)
        /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1311.100.3)

And isql


 /usr/local/Cellar/unixodbc/2.3.11/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
        /usr/local/opt/libtool/lib/libltdl.7.dylib (compatibility version 11.0.0, current version 11.2.0)
        /usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
        /usr/lib/libedit.3.dylib (compatibility version 2.0.0, current version 3.0.0)
        /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1311.100.3)

orlandombaa commented 1 year ago

Hello, I have the same issue. I have been triying to make the conection without sucess.

Has anyone been able to establish the connection?

silverlightjashmin commented 8 months ago

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libmyodbc8a.so' : file not found (0) (SQLDriverConnect)") same error, i am able to connect to mysql server using same driver but , unable to connect using pyodbc. what might be the cause

v-chojas commented 8 months ago

Check whether pyodbc module is linking to the same unixODBC using otool -L as shown above.

silverlightjashmin commented 8 months ago

i am using ubuntu 20.04 os , and isql working with same driver, but pyodbc is unable to connect

Himanshup21234 commented 5 months ago

This post helped me for the same issue: https://github.com/mkleehammer/pyodbc/issues/1165

samcofer commented 1 month ago

i am using ubuntu 20.04 os , and isql working with same driver, but pyodbc is unable to connect

@silverlightjashmin Were you ever able to resolve this issue?

silverlightjashmin commented 1 month ago

@samcofer yes, what i did was uninstall all the driver, restarted the ubuntu server and reinstalled all the driver again. and it worked for me

mkleehammer / pyodbc