How can I run pyodbc as normal user without root privilege?

[huashiyiqike]

I have been stuck for days and tried every method, strangely this question has not been asked before so I come here for help.

Environment

• Python: Python 3.9.16
• pyodbc: 4.0.35
• OS: centos 8.1
• DB: sql server
• driver: ODBC Driver 18 for SQL Server

Issue

Error if I try to connect db as non root user: conn = pyodbc.connect(conn_str) pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1' : file not found (0) (SQLDriverConnect)") while if I run as root no error happens. I should not run as root.

I followed instructions here to install Microsoft ODBC 18 for RHEL and Oracle Linux: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=redhat18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo sudo ACCEPT_EULA=Y yum install -y msodbcsql18

I think I have installed pyodbc and unixODBC correctly. More information provided as follows, (base) [lq@centos81 python]$ odbcinst -j unixODBC 2.3.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/lq/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 (base) [lq@centos81 python]$ 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.2.so.1.1 UsageCount=1 (base) [lq@centos81 python]$ ldd /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 linux-vdso.so.1 (0x00007ffc05bf6000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f99beed6000) librt.so.1 => /lib64/librt.so.1 (0x00007f99becce000) libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f99beaba000) libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f99be7d0000) libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f99be57b000) libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f99be1e6000) libm.so.6 => /lib64/libm.so.6 (0x00007f99bde64000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f99bdc4c000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f99bda2c000) libc.so.6 => /lib64/libc.so.6 (0x00007f99bd667000) /lib64/ld-linux-x86-64.so.2 (0x00007f99bf4ea000) libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f99bd45d000) libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f99bd246000) libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f99bd042000) libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f99bce31000) libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f99bcc2d000) libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f99bc744000) libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f99bc52d000) libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f99bc303000) libz.so.1 => /lib64/libz.so.1 (0x00007f99bc0ec000) libpcre2-8.so.0 => /lib64/libpcre2-8.so.0 (0x00007f99bbe68000) (base) [lq@centos81 python]$ ll /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 -rwxr-xr-x 1 root root 2082176 Jan 4 02:45 /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1

Thank you in advance for giving me any enlightment.

[gordthompson]

Create a little test program like this:

# test.py
import pyodbc

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 18 for SQL Server;"
    "Server=192.168.0.199;"
    "UID=scott;PWD=tiger^5HHH;"
    "Database=test;"
    "TrustServerCertificate=yes;"
)
print("Connected.")

Use the strace command to log its activity when it fails (for a non-root user), for example

(venv) gord@gord-dv7:~/PycharmProjects/pyodbc_demo$ strace -o strace.out python test.py

and then upload "strace.out" to this thread as an attachment (i.e., drag and drop it into your reply).

[huashiyiqike]

@gordthompson strace.out.txt here is the trace file

[gordthompson]

This looks like a duplicate of #1157 . ldd /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 shows

libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f99bc744000)

but strace shows that Python is actually loading

openat(AT_FDCWD, "/home/lq/miniconda3/lib/python3.9/lib-dynload/../../libcrypto.so.1.1", O_RDONLY|O_CLOEXEC) = 3

Is it possible that when running as root you were not running under the miniconda virtual environment?

[v-chojas]

/home/lq/miniconda3/lib/python3.9/lib-dynload/../../libcrypto.so.1.1

OpenSSL conflict; that looks like a nonstandard location. When you run as root the dynamic linker search path is different - if you strace that you'll probably find it loads OpenSSL from the normal system location.

[huashiyiqike]

@gordthompson yes, root does not use miniconda and the crypto lib cannot be found

(base) [lq@centos81 lib-dynload]$ find ./ libcrypto.so.1.1 ./ ./_asyncio.cpython-39-x86_64-linux-gnu.so ./_bisect.cpython-39-x86_64-linux-gnu.so ./_blake2.cpython-39-x86_64-linux-gnu.so ./_bz2.cpython-39-x86_64-linux-gnu.so ./_codecs_cn.cpython-39-x86_64-linux-gnu.so ./_codecs_hk.cpython-39-x86_64-linux-gnu.so ./_codecs_iso2022.cpython-39-x86_64-linux-gnu.so ./_codecs_jp.cpython-39-x86_64-linux-gnu.so ./_codecs_kr.cpython-39-x86_64-linux-gnu.so ./_codecs_tw.cpython-39-x86_64-linux-gnu.so ./_contextvars.cpython-39-x86_64-linux-gnu.so ./_crypt.cpython-39-x86_64-linux-gnu.so ./_csv.cpython-39-x86_64-linux-gnu.so ./_ctypes.cpython-39-x86_64-linux-gnu.so ./_ctypes_test.cpython-39-x86_64-linux-gnu.so ./_curses.cpython-39-x86_64-linux-gnu.so ./_curses_panel.cpython-39-x86_64-linux-gnu.so ./_datetime.cpython-39-x86_64-linux-gnu.so ./_decimal.cpython-39-x86_64-linux-gnu.so ./_elementtree.cpython-39-x86_64-linux-gnu.so ./_hashlib.cpython-39-x86_64-linux-gnu.so ./_heapq.cpython-39-x86_64-linux-gnu.so ./_json.cpython-39-x86_64-linux-gnu.so ./_lsprof.cpython-39-x86_64-linux-gnu.so ./_lzma.cpython-39-x86_64-linux-gnu.so ./_md5.cpython-39-x86_64-linux-gnu.so ./_multibytecodec.cpython-39-x86_64-linux-gnu.so ./_multiprocessing.cpython-39-x86_64-linux-gnu.so ./_opcode.cpython-39-x86_64-linux-gnu.so ./_pickle.cpython-39-x86_64-linux-gnu.so ./_posixshmem.cpython-39-x86_64-linux-gnu.so ./_posixsubprocess.cpython-39-x86_64-linux-gnu.so ./_queue.cpython-39-x86_64-linux-gnu.so ./_random.cpython-39-x86_64-linux-gnu.so ./_sha1.cpython-39-x86_64-linux-gnu.so ./_sha256.cpython-39-x86_64-linux-gnu.so ./_sha3.cpython-39-x86_64-linux-gnu.so ./_sha512.cpython-39-x86_64-linux-gnu.so ./_socket.cpython-39-x86_64-linux-gnu.so ./_sqlite3.cpython-39-x86_64-linux-gnu.so ./_ssl.cpython-39-x86_64-linux-gnu.so ./_statistics.cpython-39-x86_64-linux-gnu.so ./_struct.cpython-39-x86_64-linux-gnu.so ./_testbuffer.cpython-39-x86_64-linux-gnu.so ./_testcapi.cpython-39-x86_64-linux-gnu.so ./_testimportmultiple.cpython-39-x86_64-linux-gnu.so ./_testinternalcapi.cpython-39-x86_64-linux-gnu.so ./_testmultiphase.cpython-39-x86_64-linux-gnu.so ./_tkinter.cpython-39-x86_64-linux-gnu.so ./_uuid.cpython-39-x86_64-linux-gnu.so ./_xxsubinterpreters.cpython-39-x86_64-linux-gnu.so ./_xxtestfuzz.cpython-39-x86_64-linux-gnu.so ./_zoneinfo.cpython-39-x86_64-linux-gnu.so ./array.cpython-39-x86_64-linux-gnu.so ./audioop.cpython-39-x86_64-linux-gnu.so ./binascii.cpython-39-x86_64-linux-gnu.so ./cmath.cpython-39-x86_64-linux-gnu.so ./fcntl.cpython-39-x86_64-linux-gnu.so ./grp.cpython-39-x86_64-linux-gnu.so ./math.cpython-39-x86_64-linux-gnu.so ./mmap.cpython-39-x86_64-linux-gnu.so ./nis.cpython-39-x86_64-linux-gnu.so ./ossaudiodev.cpython-39-x86_64-linux-gnu.so ./parser.cpython-39-x86_64-linux-gnu.so ./pyexpat.cpython-39-x86_64-linux-gnu.so ./readline.cpython-39-x86_64-linux-gnu.so ./resource.cpython-39-x86_64-linux-gnu.so ./select.cpython-39-x86_64-linux-gnu.so ./spwd.cpython-39-x86_64-linux-gnu.so ./syslog.cpython-39-x86_64-linux-gnu.so ./termios.cpython-39-x86_64-linux-gnu.so ./unicodedata.cpython-39-x86_64-linux-gnu.so ./xxlimited.cpython-39-x86_64-linux-gnu.so ./zlib.cpython-39-x86_64-linux-gnu.so find: ‘libcrypto.so.1.1’: No such file or directory

According to your ref solution, I ln -s /lib64/libcrypto.so.1.1 under lib-dynload directory, still got the same error, don't know where to put it.

[gordthompson]

I ln -s /lib64/libcrypto.so.1.1 under lib-dynload directory, still got the same error, don't know where to put it.

It is loading from lib-dynload/../../, which is actually two directories higher than lib-download itself.

[huashiyiqike]

strace.out.txt

seems not the only file that need to be linked..

[v-chojas]

OpenSSL consists of both libcrypto and libssl.

[huashiyiqike]

strace.out.txt

seems not the only file that need to be linked..

Actually I cannot determine the error this time now still get pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1' : file not found (0) (SQLDriverConnect)")

[v-chojas]

Do you have strace of that?

[huashiyiqike]

strace.out.txt seems not the only file that need to be linked..

Actually I cannot determine the error this time now still get pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1' : file not found (0) (SQLDriverConnect)")

this is the new trace file @v-chojas. It is the same as the old one.

I have put the soft link in the right place I think, but the trace log is the same.

(base) [lq@centos81 lib-dynload]$ ls /home/lq/miniconda3/lib/python3.9/lib-dynload/../../libcrypto.so.1.1 /home/lq/miniconda3/lib/python3.9/lib-dynload/../../libcrypto.so.1.1

I don't understand the trace file. I thought I have fixed the crypto missing in my previous post seeing the stat = 3 which is not -1, however, after checking the previous trace file, it is the same, which means the problem still is libcrypto.so.1.1 not found? Then which directory should I put it in?

openat(AT_FDCWD, "/home/lq/miniconda3/lib/python3.9/lib-dynload/../../x86_64/libcrypto.so.1.1", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
stat("/home/lq/miniconda3/lib/python3.9/lib-dynload/../../x86_64", 0x7ffdc3e24910) = -1 ENOENT (No such file or directory)
openat(AT_FDCWD, "/home/lq/miniconda3/lib/python3.9/lib-dynload/../../libcrypto.so.1.1", O_RDONLY|O_CLOEXEC) = 3

[gordthompson]

I have put the soft link in the right place I think

It should be here:

$ ls -la /home/lq/miniconda3/lib/libcrypto.so.1.1 
lrwxrwxrwx 1 lq lq 42 Mar 31 16:52 /home/lq/miniconda3/lib/libcrypto.so.1.1 -> /lib64/libcrypto.so.1.1

but the trace log is the same

The trace log won't change. It will still look like it opened "/home/lq/miniconda3/lib/python3.9/lib-dynload/../../libcrypto.so.1.1", but the symlink will redirect it to "/lib64/libcrypto.so.1."

[huashiyiqike]

It is already here..

(base) [lq@centos81 lib]$ ls -la libcrypto.so.1.1.0 lrwxrwxrwx 1 lq lq 25 Mar 31 23:18 libcrypto.so.1.1.0 -> /lib64/libcrypto.so.1.1.0 (base) [lq@centos81 lib]$ pwd /home/lq/miniconda3/lib

[gordthompson]

In addition to libcrypto.so.1.1, strace also shows that

libstdc++.so.6

and

libgcc_s.so.1

are being loaded from

/home/lq/miniconda3/bin/../lib

instead of from /lib64 where ldd thinks they should be.

[gordthompson]

It is already here..

(base) [lq@centos81 lib]$ ls -la libcrypto.so.1.1.0 lrwxrwxrwx 1 lq lq 25 Mar 31 23:18 libcrypto.so.1.1.0 -> /lib64/libcrypto.so.1.1.0 (base) [lq@centos81 lib]$ pwd /home/lq/miniconda3/lib

I also notice that the symlink is for libcrypto.so.1.1.0, not libcrypto.so.1.1

[gordthompson]

FYI @v-chojas - I also also notice that for the latest version of msodbcsql18, the "RHEL and Oracle Linux" flavour of the driver includes a link to libcrypto.so.1.1

[gord@ol85 microsoft]$ ldd msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 | grep libcrypto
    libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f9ae06cb000)
[gord@ol85 microsoft]$ 

while the "Ubuntu" variant does not

gord@gord-dv7:/opt/microsoft$ ldd msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1 | grep libcrypto
gord@gord-dv7:/opt/microsoft$ 

[huashiyiqike]

fixed with soft link libcrypto.so.1.1

(base) [lq@centos81 lib]$ mv libcrypto.so.1.1 libcrypto.so.1.1.old
(base) [lq@centos81 lib]$ ln -s /lib64/libcrypto.so.1.1
(base) [lq@centos81 lib]$ ll libcrypto.so.1.1
lrwxrwxrwx 1 lq lq 23 Apr  3 09:36 libcrypto.so.1.1 -> /lib64/libcrypto.so.1.1

is it possible to be fixed by also supporting miniconda's lib version of libcrypto? Since minicoda is commonly used, especially for ai projects.

[v-chojas]

@gordthompson I believe that's an indirect dependency. The import table itself (or whatever it's called in the Linux equivalent) doesn't contain any references to libcrypto nor libssl because it loads OpenSSL dynamically.