Apache Airflow Provider(s)

microsoft-mssql

Versions of Apache Airflow Providers

apache-airflow-providers-microsoft-mssql==2.0.1

Apache Airflow version

2.2.2

Operating System

Ubuntu 20.04

Deployment

Official Apache Airflow Helm Chart

Deployment details

No response

What happened

MsSqlHook.get_sqlalchemy_engine uses the default mssql driver: pyodbc instead of pymssql.

• If pyodbc is installed: we get sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError)
• Otherwise we get: ModuleNotFoundError

PS: Looking at the code it should still apply up to provider version 3.0.0 (lastest version).

What you think should happen instead

The default driver used by sqlalchemy.create_engine for mssql is pyodbc.

To use pymssql with create_engine we need to have the uri start with mssql+pymssql:// (currently the hook uses DBApiHook.get_uri which starts with mssql://.

How to reproduce

>>> from contextlib import closing
>>> from airflow.providers.microsoft.mssql.hooks.mssql import MsSqlHook
>>>
>>> hook = MsSqlHook()
>>> with closing(hook.get_sqlalchemy_engine().connect()) as c:
>>>     with closing(c.execute("SELECT SUSER_SNAME()")) as res:
>>>         r = res.fetchone()

Will raise an exception due to the wrong driver being used.

Anything else

Demo for sqlalchemy default mssql driver choice:

# pip install sqlalchemy
... Successfully installed sqlalchemy-1.4.39
# pip install pymssql
... Successfully installed pymssql-2.2.5

>>> from sqlalchemy import create_engine
>>> create_engine("mssql://test:pwd@test:1433")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 2, in create_engine
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/create.py", line 560, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/connectors/pyodbc.py", line 43, in dbapi
    return __import__("pyodbc")
ModuleNotFoundError: No module named 'pyodbc'

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct