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

zinyosrim · 2022-04-23T19:07:44Z

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?

The text was updated successfully, but these errors were encountered:

v-chojas · 2022-04-25T14:42:00Z

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 · 2022-04-25T15:47:56Z

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;"

zinyosrim · 2022-04-26T09:48:53Z

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 · 2022-04-26T17:09:50Z

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

zinyosrim · 2022-04-26T18:34:45Z

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

v-chojas · 2022-04-26T19:08:27Z

python --version

lbilodeau · 2022-07-05T16:15:17Z

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 · 2022-07-05T20:13:01Z

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 · 2022-07-27T19:44:46Z

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 · 2022-07-27T19:54:45Z

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

4.0.34 is flawed. See #1082

monofox · 2022-07-29T19:17:10Z

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 · 2024-04-17T06:28:43Z

Is it really solved?

Himanshup21234 · 2024-04-17T07:27:11Z

Himanshup21234 · 2024-04-17T07:27:37Z

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

v-chojas · 2024-04-17T15:10:08Z

Did you check the pyODBC version as mentioned above?

Himanshup21234 · 2024-04-18T04:28:44Z

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

zinyosrim closed this as completed Apr 25, 2022

zinyosrim reopened this Apr 25, 2022

gordthompson added the Waiting for Info label May 23, 2022

gordthompson closed this as completed Oct 24, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

zinyosrim commented Apr 23, 2022 •

edited

v-chojas commented Apr 25, 2022

zinyosrim commented Apr 25, 2022

zinyosrim commented Apr 26, 2022

v-chojas commented Apr 26, 2022

zinyosrim commented Apr 26, 2022

v-chojas commented Apr 26, 2022

lbilodeau commented Jul 5, 2022

ZebulonPi commented Jul 5, 2022 •

edited

monofox commented Jul 27, 2022

v-chojas commented Jul 27, 2022

monofox commented Jul 29, 2022

Himanshup21234 commented Apr 17, 2024

Himanshup21234 commented Apr 17, 2024

Himanshup21234 commented Apr 17, 2024

v-chojas commented Apr 17, 2024

Himanshup21234 commented Apr 18, 2024

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

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

Comments

zinyosrim commented Apr 23, 2022 • edited

Environment

Issue

v-chojas commented Apr 25, 2022

zinyosrim commented Apr 25, 2022

zinyosrim commented Apr 26, 2022

v-chojas commented Apr 26, 2022

zinyosrim commented Apr 26, 2022

v-chojas commented Apr 26, 2022

lbilodeau commented Jul 5, 2022

ZebulonPi commented Jul 5, 2022 • edited

monofox commented Jul 27, 2022

v-chojas commented Jul 27, 2022

monofox commented Jul 29, 2022

Himanshup21234 commented Apr 17, 2024

Himanshup21234 commented Apr 17, 2024

Himanshup21234 commented Apr 17, 2024

v-chojas commented Apr 17, 2024

Himanshup21234 commented Apr 18, 2024

zinyosrim commented Apr 23, 2022 •

edited

ZebulonPi commented Jul 5, 2022 •

edited