astronomer/ap-airflow

Microsoft ODBC Driver for SQL Server not supported in Astronomer Certified 2.2.0-1

alexbegg opened this issue · 1 comments

alexbegg commented

While testing out an upgrade from Airflow 2.1.4 to 2.2.2 in my dev environment I noticed a breaking change while the image was building.

The reason the image failed to build is because I am installing msodbcsql17 in my Dockerfile using the described method as shown here: https://www.astronomer.io/docs/enterprise/v0.26/astronomer-certified/install-packages. The build fails because it is unable to locate the package msodbcsql17. If I take out installing msodbcsql17 the image builds successfully. This was not an issue with Astronomer Certified 2.1.4.

I found out Astronomer Certified 2.2.0-1 upgrades from Debian 10 ("Buster") to Debian 11 ("Bullseye") (PR for this is #298), however the Microsoft ODBC Driver for SQL Server is not supported on Debian Bullseye (see MicrosoftDocs/sql-docs#6804), so we can't use Airflow’s OdbcHook with Microsoft SQL Server anymore in Astronomer Certified 2.2.0+ until Microsoft releases a msodbcsql version that supports Bullseye (possibly in January 2022 according the twice a year release frequency).

Also, is it possible you guys might have prematurely upgraded to Debian Bullseye as Airflow seems like it currently does not support Debian Bullseye due to this same MSSQL issue? (see discussion on this Airflow PR: apache/airflow#18279)

Question: Is it possible to keep Astronomer Certified 2.2.0+ images for Buster until there is a msodbcsql version that supports Bullseye?

In the meantime I am switching all of my DAGs from using OdbcHook to MsSqlHook so that I can upgrade Airflow past 2.1.4.

kaxil commented

The following will work just fine @alexbegg

Solution

FROM quay.io/astronomer/ap-airflow:2.2.0-4-onbuild

USER root

RUN apt-get update \
  && apt-get -y install gcc gnupg2 unixodbc-dev wget unixodbc

RUN wget https://packages.microsoft.com/debian/10/prod/pool/main/m/msodbcsql17/msodbcsql17_17.8.1.1-1_amd64.deb \
  && wget https://packages.microsoft.com/debian/10/prod/pool/main/m/mssql-tools/mssql-tools_17.8.1.1-1_amd64.deb \
  && ACCEPT_EULA=Y dpkg -i msodbcsql17_17.8.1.1-1_amd64.deb \
  && ACCEPT_EULA=Y dpkg -i mssql-tools_17.8.1.1-1_amd64.deb

RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc \
  && echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc \
  && source ~/.bashrc

RUN pip install pyodbc

USER astro

Context:

The issue is that Microsoft ODBC Driver for SQL Server does not yet support Debian 11 which is what we use for our 2.2.0+ images (quay.io/astronomer/ap-airflow:2.2.1-onbuild) . 🤦

The problem is that Microsoft hasn't published yet an SQL server driver (msodbcsql17) for Debian 11 (bullseye).

Compare (Debian 10): https://packages.microsoft.com/debian/10/prod/pool/main/m/
with (Debian 11): https://packages.microsoft.com/debian/11/prod/pool/main/m/

So using the above solution till Microsoft has official support should work

  • 👍1