DOCKER + PyODBC - [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)

[sharathsridhar]

Environment

(All versions/instances within Docker. SQL Server is also running from Docker)

• Python: 3.8.10
• pyodbc: 4.0.34
• OS: ubuntu:20.04/Docker
• DB: MS SQL Server
• driver: ODBC Driver 17 for SQL Server

Issue

Observed Behavior

Dockerfile

FROM ubuntu:20.04

RUN apt-get update -y && \      
    apt-get install -y \    
    libpq-dev \    
    gcc \
    python3-pip \
    unixodbc-dev

RUN apt-get update && apt-get install -y \
    curl apt-utils apt-transport-https debconf-utils gcc build-essential g++-7\
    && rm -rf /var/lib/apt/lists/*

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN apt-get install -y unixodbc-dev

RUN pip3 install pyodbc

WORKDIR /app

COPY requirements.txt ./

COPY Main.py ./

RUN pip3 install --upgrade pip && pip3 install --no-cache-dir -r requirements.txt

ENV FLASK_APP=Main.py
ENV FLASK_ENV=production

ENTRYPOINT ["gunicorn", "--bind", "0.0.0.0:5000", "wsgi:app"]

Connectionstring - error occurs here

import pyodbc
sourceDbConnectionString = 'DRIVER={ODBC Driver 17 for SQL Server}; SERVER=%s; Database=%s; UID=%s; PWD=%s; MARS_Connection=Yes' \
                                       % ( self.sourceDbServer, self.sourceDb, self.sqlServerUserName
                                          , self.sqlServerPassword)
sourceDbConnection = pyodbc.connect(sourceDbConnectionString)

isql - Connection Successful from Docker Terminal

isql -v -k 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=127.0.0.1;DATABASE=db;UID=sa;PWD=pwd'

File exists when command ls -la /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1 is run

-rwxr-xr-x 1 root root 2059792 Jun 22 17:29 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1

odbcinst -j

unixODBC 2.3.7
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

pyodbc.drivers() returns ['ODBC Driver 17 for SQL Server']

cat /etc/odbcinst.ini

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

Error '01000', \"[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)

Followed instruction from (https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17) to install ODBC 17 on Ubuntu

Expected Behavior

To be able to connect to MS SQL Server/databases without any errors, but currently the error, as mentioned, pops up

This error does not occur when running the code in PyCharm (outside docker) which has PyODBC installed and is running Python 3.7, but fails from within Docker. Am I missing something obvious in the Dockerfile/Python code?

Thank you.

[v-chojas]

4.0.34 was broken because it contains old version of unixODBC. Try a previous version of pyODBC. You can also try strace'ing the connect to see what file it doesn't find.

[sharathsridhar]

@v-chojas Thank you.

1. Same Error on 4.0.30 - File not found error
2. I started strace from another container, but not sure on what command to run or what to look for. Any help please?

[v-makouz]

So it works when using isql but when using the same connection string in Python it gives the file not found error?

[sharathsridhar]

@v-makouz Yes, when using isql from within Docker terminal, it connects, but when the execution comes to the below lines,

sourceDbConnectionString = 'DRIVER={ODBC Driver 17 for SQL Server}; SERVER=%s; Database=%s; UID=%s; PWD=%s; MARS_Connection=Yes' \
                                       % ( self.sourceDbServer, self.sourceDb, self.sqlServerUserName
                                          , self.sqlServerPassword)
sourceDbConnection = pyodbc.connect(sourceDbConnectionString)

it does not connect and throws the file not found error

[v-chojas]

Run strace on the Python process and it should tell you what file is not found in the output.

[sharathsridhar]

@v-chojas Sorry, I don't know how to run strace from within Docker/terminal. Could you please tell me how or which commands to run?

[v-chojas]

Please read the man page first.

[sharathsridhar]

I will read and get back to you. Please do not close this ticket.

[gordthompson]

@sharathsridhar - Any progress?