MsSQLHook and pymssql Adaptive Server connection failed #42642
Description
Apache Airflow version
2.10.2
If "Other Airflow 2 version" selected, which one?
No response
What happened?
Connecting to mssql server caused an adaptive server issue and database could not be accessed.
pymssql.exceptions.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed
What you think should happen instead?
Connection hook should be successful if the Airflow connection variable is readable and filled in correctly.
Logs below:
[2024-10-02, 02:05:48 UTC] {taskinstance.py:3310} ERROR - Task failed with exception
Traceback (most recent call last):
File "src/pymssql/_pymssql.pyx", line 650, in pymssql._pymssql.connect
File "src/pymssql/_mssql.pyx", line 2158, in pymssql._mssql.connect
File "src/pymssql/_mssql.pyx", line 712, in pymssql._mssql.MSSQLConnection.init
File "src/pymssql/_mssql.pyx", line 1884, in pymssql._mssql.maybe_raise_MSSQLDatabaseException
File "src/pymssql/_mssql.pyx", line 1901, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed \n')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/etl/anaconda3/lib/python3.9/site-packages/airflow/models/taskinstance.py", line 767, in _execute_task
result = _execute_callable(context=context, **execute_callable_kwargs)
File "/home/etl/anaconda3/lib/python3.9/site-packages/airflow/models/taskinstance.py", line 733, in _execute_callable
return ExecutionCallableRunner(
File "/home/etl/anaconda3/lib/python3.9/site-packages/airflow/utils/operator_helpers.py", line 252, in run
return self.func(*args, **kwargs)
File "/home/etl/anaconda3/lib/python3.9/site-packages/airflow/models/baseoperator.py", line 406, in wrapper
return func(self, *args, **kwargs)
File "/home/etl/airflow/dags/platypus_etl/mssqltopostgresoperator.py", line 59, in execute
with closing(source.get_conn()) as conn:
File "/home/etl/anaconda3/lib/python3.9/site-packages/airflow/providers/microsoft/mssql/hooks/mssql.py", line 98, in get_conn
conn = pymssql.connect(
File "src/pymssql/_pymssql.pyx", line 659, in pymssql._pymssql.connect
pymssql.exceptions.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed
How to reproduce
apache-airflow-providers-microsoft-mssql-3.9.1
pymssql-2.3.1
apache-airflow-providers-common-sql-1.17.1
Use MsSQL hook to connect to database using a MSSQL connection from the Airflow connection menu.
Operating System
CentOS 7
Versions of Apache Airflow Providers
apache-airflow-providers-microsoft-mssql-3.9.1
pymssql-2.3.1
apache-airflow-providers-common-sql-1.17.1
Deployment
Virtualenv installation
Deployment details
No response
Anything else?
Problem occurs using apache-airflow-providers-microsoft-mssql-3.9.1 and pymssql-2.3.1 and apache-airflow-providers-common-sql-1.17.1
Reverting back to apache-airflow-providers-microsoft-mssql-3.4.2, pymssql-2.2.4 and apache-airflow-providers-common-sql-1.7.2 fixed my issue.
See similar issue here: pymssql/pymssql#913
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