Azure SQL Database
An Azure relational database service.
5,703 questions
Azure RHEL 8 VM. pyodbc or django can't connect to SQL Server: pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib ... file not found (0) (SQLDriverConnect)")
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(179.20000000000002, 4%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
Cliff
1
Reputation point
I'm on an Azure RHEL 8 VM trying to connect to an Azure SQL Server using pyodbc and django, but got a "Can't open lib...file not found" error. The ODBC library files are in those locations.
However, the connection to SQL Server works with pymssql. Connecting using isql with the ODBC drivers also work.
I've done the same installation on an Azure Ubuntu 18.04 VM and the connection works for both pyodbc and django. I only have this problem with an Azure RHEL 8 VM
-------------------------
Red Hat Enterprise Linux release 8.6 (Ootpa)
Python 3.9
--------------------------
Installed Microsoft ODBC 17 and ODBC 18 based on instructions below. I tried installing each individually but the problem is the same:
https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16#redhat18
pip install pyodbc
-------------------------
$ odbcinst -j
unixODBC 2.3.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/xxxx/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
-------------------------------
$ cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1
UsageCount=1
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
UsageCount=1
-----------------
ODBC Driver files are present
$ ls -l /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1
-rwxr-xr-x. 1 root root 2079488 Jul 15 21:00 /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.1.1
$ ls -l /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
-rwxr-xr-x. 1 root root 2061248 Jun 23 21:23 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
-----------------------
Check driver dependencies. No errors
$ ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
linux-vdso.so.1 (0x00007ffd6f7cc000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fd417499000)
librt.so.1 => /lib64/librt.so.1 (0x00007fd417291000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007fd41707b000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007fd416d91000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007fd416b3c000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fd4167a7000)
libm.so.6 => /lib64/libm.so.6 (0x00007fd416425000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fd41620d000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd415fed000)
libc.so.6 => /lib64/libc.so.6 (0x00007fd415c28000)
/lib64/ld-linux-x86-64.so.2 (0x00007fd417aa8000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007fd415a1e000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007fd415807000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007fd415603000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007fd4153f2000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fd4151ee000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007fd414d05000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fd414aee000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007fd4148c4000)
libz.so.1 => /lib64/libz.so.1 (0x00007fd4146ac000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007fd414428000)
-----------------------
$ pip freeze
asgiref==3.5.2
certifi @ file:///opt/conda/conda-bld/certifi_1655968806487/work/certifi
Django==4.0.7
mssql-django==1.1.3
pymssql==2.2.5
pyodbc==4.0.34
pytz==2022.1
sqlparse==0.4.2
---------------------
Can't open lib error when connecting using pyodbc
$ python
Python 3.9.0 (default, Nov 15 2020, 14:28:56)
[GCC 7.3.0] :: Anaconda, Inc. on linux
Type "help", "copyright", "credits" or "license" for more information.
import pyodbc
conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=<server>;'
'Database=<database>;'
'UID=<user_name>;'
'PWD=<password>;'
'Trusted_Connection=no;')
Traceback (most recent call last):
File "<console>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1' : file not found (0) (SQLDriverConnect)")
---------------------------
django settings.py DATABASES. Actual credentials replaced with dummy tokens
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': BASE_DIR / 'db.sqlite3',
},
'trial_design': {
'ENGINE': 'mssql',
'NAME': <database>,
'HOST': <server>',
'POST': '1433',
'USER': <user_name>,
'PASSWORD': <password>,
"OPTIONS": {
"driver": "ODBC Driver 17 for SQL Server",
"extra_params": "Encrypt=yes;TrustServerCertificate=no",
},
},
}
------------------------
Can't open lib error when connecting using django
$ python manage.py shell
Python 3.9.0 (default, Nov 15 2020, 14:28:56)
[GCC 7.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
import django
print(django.db.connection.ensure_connection())
Traceback (most recent call last):
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/db/backends/base/base.py", line 244, in ensure_connection
self.connect()
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/utils/asyncio.py", line 26, in inner
return func(*args, **kwargs)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/db/backends/base/base.py", line 225, in connect
self.connection = self.get_new_connection(conn_params)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/mssql/base.py", line 353, in get_new_connection
conn = Database.connect(connstr, **args)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1' : file not found (0) (SQLDriverConnect)")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/utils/asyncio.py", line 26, in inner
return func(*args, **kwargs)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/db/backends/base/base.py", line 244, in ensure_connection
self.connect()
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/db/utils.py", line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/db/backends/base/base.py", line 244, in ensure_connection
self.connect()
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/utils/asyncio.py", line 26, in inner
return func(*args, **kwargs)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/django/db/backends/base/base.py", line 225, in connect
self.connection = self.get_new_connection(conn_params)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/site-packages/mssql/base.py", line 353, in get_new_connection
conn = Database.connect(connstr, **args)
django.db.utils.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1' : file not found (0) (SQLDriverConnect)")
-------------------------------
pymssql connection gives no errors and works
import pymssql
conn = pymssql.connect(server=<server>, user=<user>, password=<password>, database=<database>)
cursor = conn.cursor()
--------------------------------
Connection using isql works
isql -v -k "DRIVER={ODBC Driver 17 for SQL Server};Server=<server>;Database=<database>;UID=<username>;PWD=<password>;Trusted_Connection=no;"
isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};Server=<server>;Database=<database>;UID=<username>;PWD=<password>;Trusted_Connection=no;"
{count} votes
-
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee2022-08-16T19:03:56.617+00:00 Hi, @Cliff Thanks for posting your question in the Microsoft Q&A forum.
I am trying to reproduce the issue and will get back to you with more details.Regards
Geetha -
Cliff 1 Reputation point
2022-08-21T06:03:22.227+00:00 Hi @GeethaThatipatri-MSFT
Thank you very much for following up! Please let me know if you have any trouble reproducing this issue.Cliff
-
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-22T17:45:25.363+00:00 Hi, @Cliff Pyodbc 4.0.34 seems to be a known broken version - is it possible to downgrade to 4.0.32? in the meantime, here is a possible workaround
I would suggest trying mssql-django driver to connect SQL Server/ Azure SQL DB with django app. While mssql-django also works on top of pyodbc but its focused mainly on django compatibility.
Please check the samples Here
https://learn.microsoft.com/en-us/samples/azure-samples/mssql-django-samples/mssql-django-samples/
Create REST API in Python with Django, using the Django REST Framework and Azure SQL - Azure SQL Database Devs’ CornerRegards
Geetha -
Cliff 1 Reputation point
2022-08-22T17:53:53.207+00:00 Thanks for your response! I am already using mssql-django (in my post's "pip freeze" printout). I think mssql-django is not the driver (ODBC is the driver) but is for adding SQL Server backend connectivity to Django. In your link to the sample code, mssql-django still requires the ODBC driver.
Cliff
-
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-22T18:08:20.867+00:00 I have updated my comment Pyodbc 4.0.34 seems to be a known broken version - is it possible to downgrade to 4.0.32? and give it a try.
Regards
Geetha -
Cliff 1 Reputation point
2022-08-22T18:41:22.317+00:00 Thanks for your suggestion. I downgraded pyodbc to 4.0.32. I tried connecting again using pyodbc and Django described above in my post, and they both still gave the same "Can't open lib .... : file not found (0)" error.
Cliff
-
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-22T18:43:31.947+00:00 @Cliff Sure let me check with the internal product team, do you have any error message?
Regards
Geetha -
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-22T19:41:46.897+00:00 @Cliff Can you please check whether 4.0.32 is actually in use by printing out the value of pyodbc.version inside the Python. If it is actually '32, then proceed to get an strace of a connection attempt and see which files it is trying to open after it loads the driver library (libmsodbcsql-17..so..*), and which file it does not find.
Regards
Geetha -
Cliff 1 Reputation point
2022-08-22T21:06:08.3+00:00 @GeethaThatipatri-MSFT . I verified the 4.0.32 version below. The error message is the same. I verified that the file exists.
print(pyodbc.version)
4. 0.32conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=<server>;'
'Database=<database>;'
'UID=<user>;'
'PWD=<password>;'
'Trusted_Connection=no;')Traceback (most recent call last):
File "<console>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1' : file not found (0) (SQLDriverConnect)") -
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-24T14:22:25.977+00:00 @Cliff-9961What is the output of ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1 ?
Regards
Geetha -
Cliff 1 Reputation point
2022-08-24T14:33:38.453+00:00 Hi @GeethaThatipatri-MSFT . Here is the output. Thanks!
$ ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
linux-vdso.so.1 (0x00007fff419fa000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f89a531a000)
librt.so.1 => /lib64/librt.so.1 (0x00007f89a5112000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f89a4efc000)
libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f89a4c12000)
libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f89a49bd000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f89a4628000)
libm.so.6 => /lib64/libm.so.6 (0x00007f89a42a6000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f89a408e000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f89a3e6e000)
libc.so.6 => /lib64/libc.so.6 (0x00007f89a3aa9000)
/lib64/ld-linux-x86-64.so.2 (0x00007f89a5929000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f89a389f000)
libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f89a3688000)
libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f89a3484000)
libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f89a3273000)
libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f89a306f000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f89a2b86000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f89a296f000)
libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f89a2745000)
libz.so.1 => /lib64/libz.so.1 (0x00007f89a252d000)
libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f89a22a9000) -
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-24T15:37:08.103+00:00 @Cliff that looks normal. Can you please strace a connection attempt from Python?
-Geetha
-
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-25T15:05:11.57+00:00 @Cliff Did you get a chance to trace a connection attempt from Python?
Regards
Geetha -
Cliff 1 Reputation point
2022-08-25T18:23:29.583+00:00 @GeethaThatipatri-MSFT Sorry for the late response. I hope the trace below is what you meant. Thanks!
$ python -m trace --count -C . test_conn.py
Traceback (most recent call last):
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/runpy.py", line 197, in _run_module_as_main
return _run_code(code, main_globals, None,
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/runpy.py", line 87, in _run_code
exec(code, run_globals)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/trace.py", line 740, in <module>
main()
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/trace.py", line 728, in main
t.runctx(code, globs, globs)
File "/home/cliff/anaconda3/envs/rwd/lib/python3.9/trace.py", line 450, in runctx
exec(cmd, globals, locals)
File "test_conn.py", line 2, in <module>
conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1' : file not found (0) (SQLDriverConnect)")----------------
test_conn.py source:
import pyodbc
conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=<server>;'
'Database=<database>;'
'UID=<user>;'
'PWD=<password>;'
'Trusted_Connection=no;') -
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-26T17:35:20.257+00:00 @Cliff Sorry I meant https://en.wikipedia.org/wiki/strace . It is a standard utility available on all Linux distros. If you can strace the Python running the test script you can see what exactly it fails to open.
Regards
Geetha -
Cliff 1 Reputation point
2022-08-26T20:25:09.093+00:00 @GeethaThatipatri-MSFT . Ok. Thanks for the clarification. The attached file contains the output from:
strace -o trace.txt python test_conn.py -
GeethaThatipatri-MSFT 29,397 Reputation points Microsoft Employee
2022-08-31T15:20:18.503+00:00 That log shows the driver and its direct dependencies have loaded successfully, but then some error may have occurred in initializing an indirect dependency since after libpcre2-8.so is loaded (this is not a dependency of the driver, but may be an indirect one) I see it is unloading all the libs and then printing the error message.
I also notice that the linker search paths seem to be prepended with additional directories and some libraries which should usually be system-provided ones are instead overridden, but then other libraries are loaded from the system locations. This has been a cause of some issues in the past because the system libs and custom ones can conflict. As you have mentioned that direct connection using isql is successful, I suspect the additional custom libraries that may be added to this Python environment are causing the conflict.
Regards
Geetha
Sign in to comment
Sign in to answer