KarthikRajashekaran opened a new issue #17831: URL: https://github.com/apache/airflow/issues/17831
<!-- Welcome to Apache Airflow! Please complete the next sections or the issue will be closed. --> **Apache Airflow version**: v2.0.2 **OS**: MWAA **Apache Airflow Provider versions**: apache-airflow-providers-microsoft-mssql==1.0.1 apache-airflow-providers-odbc==1.0.1 pymssql==2.2.1 **Deployment**: MWAA Managed Airflow Service **What happened**: Connections not established Gave connection in secret manager as below mssql_conn = `mssql://userid:pwd.2...@host-wv78.abc.company.com:1433` Also tried with below Connection String `mssql://userid:pwd.2...@host-wv78.abc.company.com:1433/DB?driver=ODBC+Driver+17+for+SQL+Server` I also tried connecting via PythonOperator, it worked fine ``` from airflow import DAG from airflow.operators.python import PythonOperator from datetime import datetime, timedelta from airflow.providers.microsoft.mssql.operators.mssql import MsSqlOperator default_args = { "owner": "airflow", "email_on_failure": False, "email_on_retry": False, "email": "ad...@localhost.com", "retries": 1, "retry_delay": timedelta(minutes=5) } def initialize_etl(): import pymssql conn = pymssql.connect(server="servername", user="uid", password="pwd", database="DB", port=1433) cursor = conn.cursor() cursor.execute ("SELECT @@VERSION") row = cursor.fetchone() print(f"\n\nSERVER VERSION:\n\n{row[0]}") cursor.close() conn.close() with DAG( dag_id="connection_test_dag", start_date=datetime(2021, 7, 1), schedule_interval=None, default_args=default_args, catchup=False ) as dag: mssql_select = MsSqlOperator( task_id='mssql_select', mssql_conn_id='mssql_conn', sql=f"SELECT CAST( GETDATE() AS Date );", autocommit=False, database='my_dummy_db' ) initialize = PythonOperator( task_id='initialize_etl_mssql', python_callable=initialize_etl ) initialize >> mssql_select ``` **Log**: ``` ** -------------------------------------------------------------------------------- [2021-08-25 13:42:06,853] {{taskinstance.py:1089}} INFO - Executing <Task(MsSqlOperator): mssql_select> on 2021-08-25T12:11:43.803259+00:00 [2021-08-25 13:42:06,875] {{standard_task_runner.py:52}} INFO - Started process 3974 to run task [2021-08-25 13:42:06,879] {{standard_task_runner.py:76}} INFO - Running: ['airflow', 'tasks', 'run', 'connection_test_dag', 'mssql_select', '2021-08-25T12:11:43.803259+00:00', '--job-id', '55', '--pool', 'default_pool', '--raw', '--subdir', 'DAGS_FOLDER/example_dags/connection_test_dag.py', '--cfg-path', '/tmp/tmpssmw0vtq', '--error-file', '/tmp/tmp3adjk1d4'] [2021-08-25 13:42:06,907] {{logging_mixin.py:104}} WARNING - Traceback (most recent call last): [2021-08-25 13:42:06,977] {{logging_mixin.py:104}} WARNING - File "/usr/local/airflow/config/cloudwatch_logging.py", line 106, in emit self.handler.emit(record) [2021-08-25 13:42:06,994] {{logging_mixin.py:104}} WARNING - File "/usr/local/lib/python3.7/site-packages/watchtower/__init__.py", line 217, in emit self._submit_batch([cwl_message], stream_name) [2021-08-25 13:42:07,012] {{logging_mixin.py:104}} WARNING - File "/usr/local/lib/python3.7/site-packages/watchtower/__init__.py", line 185, in _submit_batch self.sequence_tokens[stream_name] = response["nextSequenceToken"] [2021-08-25 13:42:07,028] {{logging_mixin.py:104}} WARNING - KeyError: 'nextSequenceToken' [2021-08-25 13:42:07,477] {{standard_task_runner.py:77}} INFO - Job 55: Subtask mssql_select [2021-08-25 13:42:07,686] {{logging_mixin.py:104}} INFO - Running <TaskInstance: connection_test_dag.mssql_select 2021-08-25T12:11:43.803259+00:00 [running]> on host ip-10-118-34-237.us-west-2.compute.internal [2021-08-25 13:42:07,808] {{taskinstance.py:1283}} INFO - Exporting the following env vars: AIRFLOW_CTX_DAG_EMAIL=ad...@localhost.com AIRFLOW_CTX_DAG_OWNER=airflow AIRFLOW_CTX_DAG_ID=connection_test_dag AIRFLOW_CTX_TASK_ID=mssql_select AIRFLOW_CTX_EXECUTION_DATE=2021-08-25T12:11:43.803259+00:00 AIRFLOW_CTX_DAG_RUN_ID=manual__2021-08-25T12:11:43.803259+00:00 [2021-08-25 13:42:07,841] {{mssql.py:91}} INFO - Executing: SELECT CAST( GETDATE() AS Date ); [2021-08-25 13:42:08,868] {{arrow_result.pyx:0}} INFO - Failed to import optional packages, pyarrow [2021-08-25 13:42:09,053] {{taskinstance.py:1482}} ERROR - Task failed with exception Traceback (most recent call last): File "src/pymssql/_pymssql.pyx", line 646, in pymssql._pymssql.connect File "src/pymssql/_mssql.pyx", line 2126, in pymssql._mssql.connect File "src/pymssql/_mssql.pyx", line 716, in pymssql._mssql.MSSQLConnection.__init__ File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.maybe_raise_MSSQLDatabaseException File "src/pymssql/_mssql.pyx", line 1852, in pymssql._mssql.raise_MSSQLDatabaseException pymssql._mssql.MSSQLDatabaseException: (18456, b"Login failed for user 'qa_view'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (*****)\nDB-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 "/usr/local/lib/python3.7/site-packages/airflow/models/taskinstance.py", line 1138, in _run_raw_task self._prepare_and_execute_task_with_callbacks(context, task) File "/usr/local/lib/python3.7/site-packages/airflow/models/taskinstance.py", line 1311, in _prepare_and_execute_task_with_callbacks result = self._execute_task(context, task_copy) File "/usr/local/lib/python3.7/site-packages/airflow/models/taskinstance.py", line 1341, in _execute_task result = task_copy.execute(context=context) File "/usr/local/airflow/.local/lib/python3.7/site-packages/airflow/providers/microsoft/mssql/operators/mssql.py", line 94, in execute sql=self.sql, autocommit=self.autocommit, parameters=self.parameters File "/usr/local/lib/python3.7/site-packages/airflow/hooks/dbapi.py", line 173, in run with closing(self.get_conn()) as conn: File "/usr/local/airflow/.local/lib/python3.7/site-packages/airflow/providers/microsoft/mssql/hooks/mssql.py", line 51, in get_conn port=conn.port, File "src/pymssql/_pymssql.pyx", line 652, in pymssql._pymssql.connect pymssql._pymssql.OperationalError: (18456, b"Login failed for user 'qa_viewer'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (uw2prdsql-wv78.sanjuan.avalara.com)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (uw2prdsql-wv78.sanjuan.avalara.com)\n") [2021-08-25 13:42:09,152] {{taskinstance.py:1532}} INFO - Marking task as UP_FOR_RETRY. dag_id=connection_test_dag, task_id=mssql_select, execution_date=20210825T121143, start_date=20210825T134206, end_date=20210825T134209 ``` **What you expected to happen**: The mssqlhook is not able to get connection passed with secret manager connection url -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@airflow.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org