SQL Server doesn't work with Azure SQL Databases

[jelther]

To use against SQL Server Azure databases, the freetds and pymssql must be compile from source.

Changed some configurations in Dockerfile and worked for me after that:

Dockerfile.zip

[jelther]

Forked the project as well. Here's a version that works

https://github.com/jelther/docker-airflow

[gfelot]

Hey man you may save my a**. I try to connect to mssql on azure and go the issue : DB-Lib error message 20004, severity 9: Read from SQL server failed.

From my understanding there is an issue with freetds. How can I use your version ? Also you should submit your work as a PR.

[jelther]

Working with my fork should work. I saw that you already submitted a PR with those changes. I didn't submit one because I was still testing but I guess it's working flawlessly.

Thanks!

[mypantz0r]

There's a bunch of warning for using FreeTDS over official Microsoft, here's what I have done on the latest build to get MSFT drivers working.

Since the image was updated to Debian 10 I have spent the last 2 days trying to get the official MSFT drivers working, as I was getting errors like:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)') 

Today I got it going, and I wanted to share just in case I can save someone else the time. Here is what I have added to my dockerfile

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

RUN sed -i 's/TLSv1.2/TLSv1.0/g' /etc/ssl/openssl.cnf
RUN sed -i 's/SECLEVEL=2/SECLEVEL=1/g' /etc/ssl/openssl.cnf

You'll notice the sed commands are reverting the TLS level back to 1.0 from 1.2. This was the issue and apparently it's some configuration issue with MFST.

[jelther]

The main issue is that Airflow uses pymssql (not for a long time, though https://github.com/apache/airflow/pull/6850). That's why we need freeTDS.

Since pymssql is not being developed anymore (https://github.com/pymssql/pymssql/issues/668) guess it's onlt a matter of time to theses fixes not being necessary anymore.

[akashvjoshi]

There's a bunch of warning for using FreeTDS over official Microsoft, here's what I have done on the latest build to get MSFT drivers working.

Since the image was updated to Debian 10 I have spent the last 2 days trying to get the official MSFT drivers working, as I was getting errors like:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)') 

Today I got it going, and I wanted to share just in case I can save someone else the time. Here is what I have added to my dockerfile

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

RUN sed -i 's/TLSv1.2/TLSv1.0/g' /etc/ssl/openssl.cnf
RUN sed -i 's/SECLEVEL=2/SECLEVEL=1/g' /etc/ssl/openssl.cnf

You'll notice the sed commands are reverting the TLS level back to 1.0 from 1.2. This was the issue and apparently it's some configuration issue with MFST.

I have been trying to install msodbcsql17 and I tried the code you have provided there. My docker build image still runs into issues which I am unable to figure out. Could you please help?

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0Warning: apt-key output should not be parsed (stdout is not a terminal)
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
curl: (60) SSL certificate problem: unable to get local issuer certificate
More details here: https://curl.haxx.se/docs/sslcerts.html

curl performs SSL certificate verification by default, using a "bundle"
 of Certificate Authority (CA) public keys (CA certs). If the default
 bundle file isn't adequate, you can specify an alternate file
 using the --cacert option.
If this HTTPS server uses a certificate signed by a CA represented in
 the bundle, the certificate verification probably failed due to a
 problem with the certificate (it might be expired, or the name might
 not match the domain name in the URL).
If you'd like to turn off curl's verification of the certificate, use
 the -k (or --insecure) option.
gpg: no valid OpenPGP data found.

[hec033]

Hi guys, I tried doing exactly what you guys did but I am still getting this error? I used the version in this one

https://github.com/puckel/docker-airflow/pull/445/files#

But I am still getting this error...

Broken DAG: [/usr/local/airflow/dags/test.py] No module named 'pymssql'

Could you please point me to what I am doing wrong? I am doing

docker-compose -f .\docker-compose-LocalExecutor.yml up -d

to start the process.

[hec033]

There's a bunch of warning for using FreeTDS over official Microsoft, here's what I have done on the latest build to get MSFT drivers working.

Since the image was updated to Debian 10 I have spent the last 2 days trying to get the official MSFT drivers working, as I was getting errors like:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLDriverConnect)') 

Today I got it going, and I wanted to share just in case I can save someone else the time. Here is what I have added to my dockerfile

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

RUN sed -i 's/TLSv1.2/TLSv1.0/g' /etc/ssl/openssl.cnf
RUN sed -i 's/SECLEVEL=2/SECLEVEL=1/g' /etc/ssl/openssl.cnf

You'll notice the sed commands are reverting the TLS level back to 1.0 from 1.2. This was the issue and apparently it's some configuration issue with MFST.

So if you do this then I have to use the ODBC hook and operator is that correct?