[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

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. — Gord Thompson, May 07 '22 at 18:42
@GordThompson This seems strange `libodbcinst.so.2`. It refers to my sybase project `sibp` (another django project using sybase database) — xralf, May 07 '22 at 20:40
Does the Sybase driver mess with your `LD_LIBRARY_PATH` like [this](https://github.com/mkleehammer/pyodbc/issues/678#issuecomment-573385714)? — Gord Thompson, May 07 '22 at 21:03
@GordThompson I did [this](https://stackoverflow.com/questions/71054425/django-how-to-correctly-install-library-without-pip-in-venv-to-work-after-depl) 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). — xralf, May 07 '22 at 21:29
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. — Gord Thompson, May 07 '22 at 21:41
Do you get the error if you do `$ LD_LIBRARY_PATH= python manage.py inspectdb --database=mssql_database` ? — Gord Thompson, May 08 '22 at 14:35
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. — Gord Thompson, May 13 '22 at 14:24
@GordThompson I tried it, but the error is the same. Thank you. — xralf, May 13 '22 at 18:45

score 3 · Answer 1 · answered May 14 '22 at 12:57

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

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. — xralf, May 14 '22 at 16:06

araldhafeeri · Answer 2 · 2022-05-11T13:39:02.233

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

edited May 11 '22 at 13:39

answered May 10 '22 at 19:47

araldhafeeri

179
9

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)")` – xralf May 11 '22 at 05:33
`$ odbcinst -j unixODBC 2.3.7 odbcinst: symbol lookup error: odbcinst: undefined symbol: odbcinst_system_file_name ` – xralf May 11 '22 at 05:34
Dear provide full path for (libtdsodbc.so ) ( libtdsS.so) in /etc/odbcinst.ini and see if that works. – araldhafeeri May 11 '22 at 06:20
Full path doesn't work either. – xralf May 11 '22 at 06:30
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 https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15 – araldhafeeri May 11 '22 at 08:41
Also kindly try pyodbc.drivers() it should return list of drivers. If not there was miss step in setup or configuraiton. – araldhafeeri May 11 '22 at 08:45
The driver is installed. – xralf May 11 '22 at 10:37
Kindly check my edit... – araldhafeeri May 11 '22 at 13:39
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. – xralf May 11 '22 at 17:31
If you're interested you can see [this](https://stackoverflow.com/questions/72125405/odbcinst-symbol-lookup-error-odbcinst-undefined-symbol-odbcinst-system-file) subproblem. – xralf May 11 '22 at 17:31

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

2 Answers2

Linked