driver not found, Pyodbc.drivers=[]: pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

zinyosrim commented 2 years ago

Environment

Python: 3.9.7
pyodbc: 4.0.32
OS: MacOS 12.0.1, Apple M1 Max
DB: Azure SQL
driver: ODBC Driver 17 for SQL Server

Issue

When running

import pyodbc

server = 'xxx.database.windows.net'
database = 'yyy'
username = 'zzz'
password = '???
odbc_driver = '{ODBC Driver 17 for SQL Server}'
conn_str = f'DRIVER={odbc_driver};SERVER=tcp:{server};PORT=1433;DATABASE={database};UID={username};PWD={password}'

print('opening....')
cnn = pyodbc.connect(conn_str)
print("opened")

I get

  File "/Users/deniz/OneDrive/Dev/webhook_receiver/sql.py", line 12, in <module>
    cnn = pyodbc.connect(conn_str)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

output:

opening....
[ODBC][95553][1650740040.206997][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x7fb930024c00
[ODBC][95553][1650740040.207022][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x7fb930024c00
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 4
[ODBC][95553][1650740040.207060][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][95553][1650740040.207073][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fb930024c00
        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'

[ODBC][95553][1650740040.207119][SQLAllocHandle.c][513]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7fb930029600
[ODBC][95553][1650740040.207368][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x7fb930029600
            Window Hdl = 0x0
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:xxx.database.windows.net;PORT=1433;DATABASE=yyy...][length = 166 (SQL_NTS)]
            Str Out = 0x0
            Str Out Max = 0
            Str Out Ptr = 0x0
            Completion = 0
[ODBC][95553][1650740040.209211][SQLConnect.c][1140]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found
[ODBC][95553][1650740040.209244][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x7fb930029600
            Window Hdl = 0x0
            Str In = [DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:xxx;PORT=1433;DATABASE=yyy...][length = 166 (SQL_NTS)]
            Str Out = 0x3076d55c0
            Str Out Max = 2048
            Str Out Ptr = 0x0
            Completion = 0
[ODBC][95553][1650740040.209362][SQLConnect.c][1140]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found
[ODBC][95553][1650740040.209384][SQLGetDiagRecW.c][535]
        Entry:
            Connection = 0x7fb930029600
            Rec Number = 1
            SQLState = 0x3076d7964
            Native = 0x3076d794c
            Message Text = 0x7fb93003a600
            Buffer Length = 1023
            Text Len Ptr = 0x3076d7962
[ODBC][95553][1650740040.209410][SQLGetDiagRecW.c][596]
        Exit:[SQL_SUCCESS]
            SQLState = [01000]
            Native = 0x3076d794c -> 0 (32 bits)
            Message Text = [[unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found]
[ODBC][95553][1650740040.209439][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fb930029600
[ODBC][95553][1650740040.209451][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]

How can I fix this?

v-chojas commented 2 years ago

Have you actually installed the driver? Can you check if connecting using isql -v -k '<your connection string here>' works?

Also, note that PORT is not a valid connection string keyword. Where did you get that from? The full list of valid keywords is here: https://docs.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute

zinyosrim commented 2 years ago

I installed the drivers with brew.

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

After not being successfull, I followed the instructions on https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX

isql MYMSSQL <user> <pw>

returns [ISQL]ERROR: Could not SQLConnect

isql -v -k "Driver={ODBC Driver 18 for SQL Server};Server=tcp:<server>,1433;Database=<db>;Uid=<user>;Pwd=<pw>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

returns +---------------------------------------+	Connected!
sql-statement
help [tablename]
quit

+---------------------------------------+

zinyosrim commented 2 years ago

To formulate my issue more clear:

Running

import pyodbc

server   = 'myserver.database.windows.net'
database = 'mydb'
username = 'myuser'
password = 'mypassword'
odbc_driver = '{ODBC Driver 18 for SQL Server}'

conn_str = (
    f"Driver={odbc_driver};"
    f"Server=tcp:{server},1433;"
    f"Database={database};"
    f"Uid={username};"
    f"Pwd={password};"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "Connection Timeout=30;")

print(conn_str)

cnxn = pyodbc.connect(conn_str)

outputs

Driver={ODBC Driver 18 for SQL Server};Server=tcp:myserver.database.windows.net,1433;Database=mydb;Uid=myuser;Pwd=mypassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
Traceback (most recent call last):
  File "/Users/....", line 21, in <module>
    cnxn = pyodbc.connect(conn_str)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (0) (SQLDriverConnect)")

Using the same connection string in isql:

isql -v -k "Driver={ODBC Driver 18 for SQL Server};Server=tcp:<server>,1433;Database=<db>;Uid=<user>;Pwd=<pw>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

returns Connected and queries are possible

v-chojas commented 2 years ago

Are you using an x86 Python (and pyODBC) ? Check with python --version

zinyosrim commented 2 years ago

it's 3.9.7, /Users/deniz/Anaconda/anaconda3/bin/python. how do I find out if it's x86 or not?

v-chojas commented 2 years ago

python --version

lbilodeau commented 2 years ago

I ran into this problem the other day. What I found was the microsoft sql driver version with the current install defaults load is for version 18, not 17 when I changed the SQL driver designation in the pyodbc connect call to 18. this error went away and my connection was made.

ZebulonPi commented 2 years ago

I am getting the same 'file not found' error for both the MS SQL driver AND the FreeTDS driver on my M1 Mac, Monterey 12.4:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/Cellar/freetds/1.3.12/lib/libtdsodbc.so' : file not found (0) (SQLDriverConnect)")

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

No matter how I build out my odbcinst.ini file, reinstall unixodbc, or pyodbc, etc., I can't get pyodbc to work.

Jumping to something like pymssql won't work for me due to the lack of generic INSERT terms (? vs. %s), so I'm stuck here.

Python 3.9.7, pyodbc 4.0.3

Any thoughts?

EDIT: My odbcinst.ini file, showing symlinks to the relevant files. I even tried 17 with no luck:

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

[FreeTDS] Description=TD Driver (MSSQL) Driver=/opt/homebrew/Cellar/freetds/1.3.12/lib/libtdsodbc.so Setup=/opt/homebrew/Cellar/freetds/1.3.12/lib/libtdsodbc.so FileUsage=1

[ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib UsageCount=2

monofox commented 1 year ago

Have it on Gentoo as well with Python 3.9.12 and PyODBC 4.0.34 with DB2 as well MariaDB.

In connection string with:

Driver={/usr/lib64/mariadb/libmaodbc.so}

its working.

If i write

Driver=maodbc

while having

[maodbc]
Description=MariaDB ODBC Connnector 3.1.14
Driver=/usr/lib64/mariadb/libmaodbc.so
UsageCount=1
Threading=0

in /etc/unixODBC/odbcinst.ini

resulting into

    sock = pyodbc.connect(dsn, autocommit=autocommit)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'maodbc' : file not found (0) (SQLDriverConnect)")

In older version of python & pyodbc combination it worked with the exact same configuration and code.

Unfortunately for DB2 i can't pass the driver directly, as it would return in too long connection string for the DB2 driver :(

v-chojas commented 1 year ago

In older version of python & pyodbc combination it worked with the exact same configuration and code.

4.0.34 is flawed. See https://github.com/mkleehammer/pyodbc/issues/1082

monofox commented 1 year ago

In older version of python & pyodbc combination it worked with the exact same configuration and code.

4.0.34 is flawed. See #1082

Thank you @v-chojas ! Indeed, install pyodbc 4.0.34 by compiling instead of wheel solves it!

Himanshup21234 commented 3 months ago

Is it really solved?

Himanshup21234 commented 3 months ago

Himanshup21234 commented 3 months ago

I am still facing same issue even though i have drivers already installed in the system.

v-chojas commented 3 months ago

Did you check the pyODBC version as mentioned above?

Himanshup21234 commented 3 months ago

@v-chojas I tried with 4.0.32, 4.0.30, 4.0.34, 4.0.34, 4.0.35, 5.1.1 on MAC M3 silicon.

mkleehammer / pyodbc

driver not found, Pyodbc.drivers=[]: pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") #1054

Environment

Issue