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:[email protected]:1433`
Also tried with below Connection String
`mssql://userid:[email protected]: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": "[email protected]",
"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:
[email protected]
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
