Python ODBC (pyodbc) connection to MSSQL server fails with error "Can't open lib ... file not found"- but driver file is there

Question

I am trying to get pyodbc to connect to SQL Server with this test script:

import pyodbc
server = 'ms.sql.server'
database = 'database'
username = 'username'
password = 'password'

print(pyodbc.drivers())
drivers = [item for item in pyodbc.drivers()]
driver = drivers[-1]
print('driver:'+driver)

cnxn = pyodbc.connect('DRIVER={'+driver+'};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

#Sample select query
cursor.execute("SELECT loginame FROM sys.sysprocesses  WHERE spid = @@SPID")
row = cursor.fetchone()
while row:
    print(row[0])
    row = cursor.fetchone()

The error output looks like:

['ODBC Driver 17 for SQL Server']
driver:ODBC Driver 17 for SQL Server
Traceback (most recent call last):
  File "db_test.py", line 12, in <module>
    cnxn = pyodbc.connect('DRIVER={' + driver + '};SERVER='+server+';DATABASE='+database)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1' : file not found (0) (SQLDriverConnect)")

The driver file it is complaining about exists:

[root@localhost]# ll /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1
-rwxr-xr-x. 1 root root 2065280 Mar  5 08:28 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1

I am working on Centos and installed msodbcsql17 with

curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
yum install msodbcsql17

Not sure if it's relevant, but I also installed mssql-tools (bcp/sqlcmd) and they are working correctly.

After trawling through similar-but-different posts on stackoverflow I had no luck finding a solution. Below are checks I saw on other posts that might be helpful with diagnosis.

Output from odbcinst -j :

unixODBC 2.3.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Contents of /etc/odbcinst.ini :

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1
UsageCount=1

ldd output:

ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1
        linux-vdso.so.1 (0x00007ffef0bf3000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f1678da1000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f1678b99000)
        libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f167897f000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f1678696000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f1678441000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f16780ac000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f1677d2a000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f1677b12000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f16778f2000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f167752f000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f16793b1000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f1677318000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f1677114000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f1676f03000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f1676cff000)
        libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f1676819000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f1676602000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f16763d8000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f16761c1000)
        libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f1675f3d000)

Any idea what the problem is here?

Does it make any difference if you replace driver = drivers[-1] with driver = 'ODBC Driver 17 for SQL Server'? — Gord Thompson
– Gord Thompson, Commented May 24, 2021 at 22:51
The error is the same. (the drivers[-1] trick I took from another post :) ) — LeeW
– LeeW, Commented May 24, 2021 at 23:04
IIRC another trick is to use strace to see if something is failing as you try to establish the connection. — Gord Thompson
– Gord Thompson, Commented May 24, 2021 at 23:36
Was there some reason you didn't choose to use the CentOS package from centos.pkgs.org/8/microsoft-prod-x86_64/…? — AlwaysLearning
– AlwaysLearning, Commented May 25, 2021 at 1:52
No reason other than ignorance of its existence :) I assumed getting the driver direct from MS was the right move given I am working with SQL Server. I'll give that one a try, thanks. And try strace on one (or both) if I still have no luck, once I have a replicated VM environment to test on. — LeeW
– LeeW, Commented May 26, 2021 at 4:41

tdy · Accepted Answer · 2021-11-18 00:54:21Z

2

I had this same symptom and could not get pyodbc to connect to a MSSQL DB server. Setting environment variable LD_DEBUG=libs and running the script showed me that older (non existent) version of libstdc++ was being expected.

Setting the environment variable resolved my problem:

LD_PRELOAD=/usr/lib64/libstdc++.so.6

edited Nov 18, 2021 at 0:54

tdy

42.1k42 gold badges124 silver badges125 bronze badges

answered Nov 17, 2021 at 14:22

Jayanth S Vasisht

212 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LeeW Over a year ago

Thanks for the LD_DEBUG tip. Seems in my case it is an issue with openssl lib versions /lib64/libk5crypto.so.3: error: symbol lookup error: undefined symbol: EVP_KDF_ctrl, version OPENSSL_1_1_1b (fatal)

Collectives™ on Stack Overflow

Python ODBC (pyodbc) connection to MSSQL server fails with error "Can't open lib ... file not found"- but driver file is there

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related