Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

Question

I am fairly new to Python and Azure web apps. Any help is appreciated.

My setup:

• Program: Visual Studio code
• Language: Python-Flask
• Cloud provider: Microsoft Azure
• Database: Azure SQL DB
• Deployment option: Docker image > Azure container registry > Deploy the image to the Web app
• Web App OS: Linux (I think Alpine?)

In my code, I am using pyodbc to connect to the Azure SQL DB. The code runs successfully locally in the terminal. However, when it runs on the web, it encounters the following error:

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

I followed several troubleshooting posts, however, I have not been successful.

I tried using the $sudo ln to create a symbolic link that resulted in permission denied. I think this is a known limitation of the Azure web app.

I tried to look for the driver in etc/odbcinst.ini to see if the driver name exists, however, I am pretty new to Azure / VS Code so I do not even know how to open the file that is in the, etc/ folder. I do see it in the BASH command prompt when I navigate to the etc/ folder but not sure how to open the file.

I ran the following command in the BASH to install PYODBC, but that didn't resolve the issue.

python -m pip install pyodbc

The result from odbcinst -j

    unixODBC 2.3.4
    DRIVERS............: /etc/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/odbc.ini
    FILE DATA SOURCES..: /etc/ODBCDataSources
    USER DATA SOURCES..: /home/a49d42b0d7b8ce200a4f7e74/.odbc.ini
    SQLULEN Size.......: 8
    SQLLEN Size........: 8
    SQLSETPOSIROW Size.: 8

My dockerFile:

# Pull a pre-built alpine docker image with nginx and python3 installed
FROM tiangolo/uwsgi-nginx-flask:python3.6-alpine3.7

ENV LISTEN_PORT=8000
EXPOSE 8000

COPY /app /app

# Uncomment to install additional requirements from a requirements.txt file
COPY requirements.txt /
RUN pip install --no-cache-dir -U pip
RUN pip install --no-cache-dir -r /requirements.txt

RUN apk add g++
RUN apk add unixodbc-dev
RUN pip install pyodbc

My requirements.txt. I commented out pyodbc; I think that's okay since I am installing it in the docker file.

click==6.7
Flask==0.12.2
itsdangerous==0.24
Jinja2==2.10
MarkupSafe==1.0
Werkzeug==0.14.1
#pyodbc==4.0.28

Additional questions:

1. Should I be using PYODBC? or is there something better / more compatible I should be using?
2. Should I use MYSQL instead of Azure SQL DB?
3. Is there a way for me to open the odbcinst.ini file that is on the web app?

Answer 1

First, if you want to know what os release is in your docker, you can command cat /etc/os-release to get it, such as run it in my ubuntu as the figure below.

At here, I'm sure your web app os is Alpine Linux, due to the first line of your docker file FROM tiangolo/uwsgi-nginx-flask:python3.6-alpine3.7. Your base image is based on Alpine 3.7.

Second, according to your error info Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") and the content of your docker file and requirements.txt, I think the issue was caused by missing MS SQL Server ODBC driver for Linux which not be installed in your docker image, but pyodbc required it to connect Azure SQL Database.

However, for ODBC Driver 17 for SQL Server, the offical document Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS shows there is not a released v17 package for Alpine. So the workaround is to change your DockerHub base image from tiangolo/uwsgi-nginx-flask:python3.6-alpine3.7 to tiangolo/uwsgi-nginx-flask:python3.6 to use debian as OS, then you can easily install MS ODBC driver 17 for SQL Server in it.

For your additional questions, as below.

1. Except for using pyodbc, pymssql is the other one of Python SQL Driver, please see the offical document Python SQL Driver, but The Pymssql Project is Being Discontinued. And SQLAlchemy as ORM framework can be used to connect Azure SQL Database, which also requires pyodbc or pymssql.

2. Use MySQL or Azure SQL Database, it's up to you. I think the only difference is that MySQL may be installed easiler than Azure SQL DB in Alpine.

3. The way to open odbcinst.ini file on webapp is to use vim over SSH to connect to your docker OS. Considering for the custom docker image you used, please see the section Enable SSH of the offical document Configure a custom Linux container for Azure App Service and replace the command apk with apt for Debian Linux.

Answer 2

The following instructions in the official website helped me solve my problem: Install the Microsoft ODBC driver for SQL Server (Linux)

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Debian 8 (only supported up to driver version 17.6)
curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Debian 9
curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Debian 10
curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev
# optional: kerberos library for debian-slim distributions
sudo apt-get install -y libgssapi-krb5-2

Answer 3

The official website instructions did not work for me as the packages were reported as missing:

$ sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package msodbcsql18

The following worked for me:

sudo apt-get install unixodbc

Navigate with a browser too:

https://packages.microsoft.com/ubuntu/21.04/prod/pool/main/m/

Download the deb files in:

• msodbcsql18
• mssql-tools18
  Or
• msodbcsql
• msodbcsql17

Pick the version you want to install. I picked 18.

Install the two deb files:

sudo dpkg -i mssql-tools18_18.0.1.1-1_amd64.deb
sudo dpkg -i msodbcsql18_18.0.1.1-1_amd64.deb

If you had not run the script on the official website then you need to run the following (for 18 rather than 17):

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc