[Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1'

Question

When I run $ python manage.py inspectdb --database=mssql_database

I have the following error

django.db.utils.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1' : file not found (0) (SQLDriverConnect)")

but the file libmsodbcsql-17.9.so.1.1 is there.

$ cat /etc/odbcinst.ini 
[ODBC]
Trace=Yes
TraceFile=/tmp/odbc.log

[FreeTDS]
Description=TDS driver (Sybase/MS SQL)
Driver=libtdsodbc.so
Setup=libtdsS.so
CPTimeout=
CPReuse=
UsageCount=2

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



$ odbcinst -j
unixODBC 2.3.7
odbcinst: symbol lookup error: odbcinst: undefined symbol: odbcinst_system_file_name


$ ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1 
    linux-vdso.so.1 (0x00007fff545c4000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f9f85470000)
    librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f9f85268000)
    libodbcinst.so.2 => /home/pd/sibp/env/lib/python3.6/site-packages/sqlanydb-1.0.11.dist-info/lib64/libodbcinst.so.2 (0x00007f9f84fcc000)
    libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007f9f84cf6000)
    libgssapi_krb5.so.2 => /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2 (0x00007f9f84aab000)
    libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f9f84722000)
    libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f9f84384000)
    libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f9f8416c000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f9f83f4d000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f9f83b5c000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f9f85a80000)
    libdbtasks17_r.so => /home/pd/sibp/env/lib/python3.6/site-packages/sqlanydb-1.0.11.dist-info/lib64/libdbtasks17_r.so (0x00007f9f83912000)
    libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f9f836f8000)
    libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007f9f834c6000)
    libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007f9f832c2000)
    libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007f9f830b7000)
    libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007f9f82eb3000)
    libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f9f82c99000)

OS: Ubuntu 18.04.6 LTS

I started fresh with clean system and I'm creating snapshots of my virtualbox now. This happens as a result of installing SQL Anywhere Database Client. See here. I need SQL Anywhere Database Client to work with Sybase. After finishing this installation I have the above error.

$ pip list
Package       Version
------------- -------
Django        1.8
django-pyodbc 1.1.3
pip           21.3.1
pyodbc        4.0.32
setuptools    59.6.0
sqlany-django 1.13
sqlanydb      1.0.11
wheel         0.37.1

Answer 1

I had the same problem and was able to fix it (at least if all you need from the SQL Anywhere client is the 64-bit ODBC driver) by deleting this file, after backing it up of course:

rm /opt/sqlanywhere17/lib64/libodbcinst.so.2

I reached this conclusion as follows. I noticed that before installing the SQL Anywhere 17 client (v17.0.11.7058), the Microsoft ODBC driver (17 or 18) for SQL Server worked, and I could run odbcinst -j without issue. After installing the SQL Anywhere client, I got errors like (I'm using PHP not python):

PHP Warning:  PHP Startup: Unable to load dynamic library 'sqlsrv.so' (tried: /usr/lib/php/20190902/sqlsrv.so (/usr/lib/php/20190902/sqlsrv.so: undefined symbol: SQLGetInstalledDrivers), /usr/lib/php/20190902/sqlsrv.so.so (/usr/lib/php/20190902/sqlsrv.so.so: cannot open shared object file: No such file or directory)) in Unknown on line 0

Investigating this error message I found that SQLGetInstalledDrivers is a function of odbcinst, which correlates with the fact that odbcinst -j also stopped working, so I ran sudo find / -name "*odbcinst*" and found that there were versions of shared library libodbcinst.so in two places, /opt/sqlanywhere17/lib64 and /usr/lib/x86_64-linux-gnu. The relevant files in those directories:

$ ls -lha /opt/sqlanywhere17/lib64/    
    lrwxrwxrwx ...   22 ... libdbodbcinst17_r.so -> libdbodbcinst17_r.so.1
    -r-xr-xr-x ... 554K ... libdbodbcinst17_r.so.1
    lrwxrwxrwx ...   20 ... libodbcinst.so -> libdbodbcinst17_r.so
    lrwxrwxrwx ...   22 ... libodbcinst.so.1 -> libdbodbcinst17_r.so.1
    lrwxrwxrwx ...   22 ... libodbcinst.so.2 -> libdbodbcinst17_r.so.1

$ ls -lha /usr/lib/x86_64-linux-gnu/
    -rwxr-xr-x ...  986 ... libodbcinst.la
    lrwxrwxrwx ...   20 ... libodbcinst.so -> libodbcinst.so.2.0.0
    lrwxrwxrwx ...   20 ... libodbcinst.so.1 -> libodbcinst.so.2.0.0
    lrwxrwxrwx ...   20 ... libodbcinst.so.2 -> libodbcinst.so.2.0.0
    -rwxr-xr-x ... 463K ... libodbcinst.so.2.0.0

Then I found that only the SQL Server driver depends on libodbcinst.so.2:

$ sudo ldd /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.3.1
        linux-vdso.so.1 (0x00007ffebf7be000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f9c8d055000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f9c8ce4d000)
    =>  libodbcinst.so.2 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.2 (0x00007f9c8cc38000)
        libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007f9c8c962000)
        libgssapi_krb5.so.2 => /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2 (0x00007f9c8c717000)
        libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f9c8c38e000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f9c8bff0000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f9c8bdd8000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f9c8bbb9000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f9c8b7c8000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f9c8d66c000)
        libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007f9c8b5be000)
        libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007f9c8b38c000)
        libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007f9c8b188000)
        libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007f9c8af7d000)
        libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007f9c8ad79000)
        libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f9c8ab5f000)

$ sudo ldd /opt/sqlanywhere17/lib64/libdbodbc17.so
        linux-vdso.so.1 (0x00007ffc6a25f000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f3945249000)
        libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f394502f000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f3944e27000)
        libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f3944a9e000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f3944700000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f394430f000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f3945696000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f39440f7000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f3943ed8000)

It didn't seem to matter what order directories were included in environment variables, the SQL Anywhere version of libodbcinst.so.2 always seemed to take precedence when its directory was included, so to force the other version to be used, I deleted it.

Again, there may be components of the SQL Anywhere 17 client that do not work without the link from libodbcinst.so.2 to libdbodbcinst17_r.so.1, but according to ldd, the ODBC driver is not one of those components.

Answer 2

In my case, after migration of our server from RHEL7 to RHEL8, we updated from odbc driver 17 to 18 in /etc/odbcinst.ini and the packages in our own conda env. got broken. So, updated all the packages in our conda env.

conda update --all

and then the issue got resolved make sure you give your dsn properly while connecting to database server it should be,

>>conn=connect('dsn',UID,pwd,truncate=true)

dsn should be properly configured in /etc/odbc.ini

Answer 3

While not a complete answer for this particular case, the following information may be helpful for others having difficulty using both sqlanydb (for SAP SQL Anywhere) and pyodbc (for Microsoft SQL Server) on the same machine.

The SQL Anywhere setup instructions cited in the question instruct us to

source "/opt/sqlanywhere17/bin64/sa_config.sh"

in .bashrc. That script includes, in part

LD_LIBRARY_PATH="$SQLANY17/lib32:${LD_LIBRARY_PATH:-}"
LD_LIBRARY_PATH="$SQLANY17/lib64:${LD_LIBRARY_PATH:-}"
export LD_LIBRARY_PATH

which prepends the SQLANY17/ directories to any existing LD_LIBRARY_PATH. On a vanilla install of Xubuntu 20.04 there is no LD_LIBRARY_PATH defined, so the net result is

$ echo $LD_LIBRARY_PATH 
/opt/sqlanywhere17/lib64:/opt/sqlanywhere17/lib32:

That works fine for sqlanydb

# any.py
import sqlanydb
# Initiate connection to the database
DB_PARAMS = {"HOST": "192.168.0.199",
         "USER": "dba",
         "PASSWORD":"sql",
         "DB":""}
conn = sqlanydb.connect(host=DB_PARAMS['HOST'], uid=DB_PARAMS['USER'], pwd=DB_PARAMS['PASSWORD'], dbn=DB_PARAMS['DB'])
# Instantiate cursor
curs = conn.cursor()
# Execute a query
curs.execute("SELECT 'success' as connection_status FROM SYS.DUMMY")
result = curs.fetchall()
print(result)

$ python3 any.py
[('success',)]

but it causes pyodbc to fail

# pyo.py
import pyodbc

cnxn = pyodbc.connect(
    "Driver=ODBC Driver 17 for SQL Server;"
    "Server=192.168.0.199;"
    "Database=test;"
    "UID=scott;PWD=tiger^5HHH;"
)
crsr = cnxn.cursor()
print(crsr.execute("SELECT 'success' AS connection_status").fetchall())

$ python3 pyo.py 
Traceback (most recent call last):
  File "pyo.py", line 3, in <module>
    cnxn = pyodbc.connect(
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1' : file not found (0) (SQLDriverConnect)")

If we add a third LD_LIBRARY_PATH tweak to sa_config.sh

LD_LIBRARY_PATH="$SQLANY17/lib32:${LD_LIBRARY_PATH:-}"
LD_LIBRARY_PATH="$SQLANY17/lib64:${LD_LIBRARY_PATH:-}"
LD_LIBRARY_PATH="/lib/x86_64-linux-gnu/:${LD_LIBRARY_PATH:-}"
export LD_LIBRARY_PATH

and restart the machine to apply the changes then any.py (above) continues to work, but pyo.py (above, unmodified) works, too:

$ python3 pyo.py 
[('success', )]

Answer 4

Looks like you have driver issue, run the following command and it should work

sudo apt-get install tdsodbc

update content of odbcinst.ini

$ sudo nano /etc/odbcinst.ini 
  [FreeTDS]
  Description = TDS Driver for MSSQL
  driver = path/to/libtdsodbc.so
  setup =  path/to/libtdsS.so

[EDIT]

Kindly share output after running this

  import pyodbc
  print(pyodbc.drivers())

Also kindly check if followed all steps here for target OS:

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017