[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

Younts

At a command prompt run ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1 and see if there are any dependencies that it cannot resolve. – Pancho

@GordThompson This seems strange libodbcinst.so.2. It refers to my sybase project sibp (another django project using sybase database) – Younts

Does the Sybase driver mess with your LD_LIBRARY_PATH like this? – Pancho

@GordThompson I did this for my sybase project to work. I realized now that this is probably the reason that one of my projects that work with mssql stopped working at this point, similarly as I can't connect with this question with yet another project. I'd like to possibly solve it somehow without reinstalling the system and having two virtual machines with Ubuntu (one for sybase project and one for mssql projects). – Younts

If the Sybase project and the MSSQL project(s) need different Apache configurations then I wouldn't be surprised if trying to get them to "play nicely" within the same Apache instance would be more trouble than it's worth. However, I haven't had to mess with Apache for many years so I don't really know what the current state of affairs might be in this regard. – Pancho

Do you get the error if you do $ LD_LIBRARY_PATH= python manage.py inspectdb --database=mssql_database ? – Pancho

@GordThompson When I stop apache, I still get this error. – Younts

What is your system environment? – Tran

@Tran Ubuntu 18.04.6 LTS – Younts

Try hacking /opt/sqlanywhere17/bin64/sa_config.sh and after the two existing LD_LIBRARY_PATH= lines add a third one for LD_LIBRARY_PATH="/lib/x86_64-linux-gnu/:${LD_LIBRARY_PATH:-}". That seems to fix the isql error for me, but I can't test the SQL Anywhere part. – Pancho

@GordThompson I tried it, but the error is the same. Thank you. – Younts

Answer 1

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', )]

Pancho

I'm giving it a bounty, but I can test on monday if restart helps (I have problem with VPN now). If restart won't help, I will start another bounty. – Younts

So, for me, after restart it still doesn't work. – Younts

Answer 2

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

Kairouan

after sudo apt-get install tdsodbc It ends with error '01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'libtdsodbc.so' : file not found (0) (SQLDriverConnect)") – Younts

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

Dear provide full path for (libtdsodbc.so ) ( libtdsS.so) in /etc/odbcinst.ini and see if that works. – Kairouan

Full path doesn't work either. – Younts

It is very hard to debug the issue from here given the information. Kindly note, I am trying to be helpful not rude. But please follow the driver instlallation from here learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/… – Kairouan

Also kindly try pyodbc.drivers() it should return list of drivers. If not there was miss step in setup or configuraiton. – Kairouan

The driver is installed. – Younts

Kindly check my edit... – Kairouan

In initial state pyodbc.drivers() showed two drivers installed as you can see them in the output of /etc/odbcinst.ini. Now the situation is different, because I tried to compile unixodbc and it didn't helped and after apt-get install unixodbc I have now broken packages. I'm trying another virtual machine now . Maybe I will get into this state again. But now I'm using snapshots, so will save machine state before this problem occurs. This need really troubleshooting of expert user. It's not so easy. – Younts

If you're interested you can see this subproblem. – Younts

Answer 3

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.

Heiner

Answer 4

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

Bashemath