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]

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]

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]

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]

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]

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

[zinyosrim]

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

[v-chojas]

python --version

[lbilodeau]

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]

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]

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]

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]

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]

Is it really solved?

[Himanshup21234]

[Himanshup21234]

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

[v-chojas]

Did you check the pyODBC version as mentioned above?

[Himanshup21234]

@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.