You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2021/08/25 14:21:38 UTC
[GitHub] [airflow] KarthikRajashekaran opened a new issue #17831: MSSQL: Adaptive Server connection failed
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.2008@host-wv78.ABC.COMPANY.COM:1433`
Also tried with below Connection String
`mssql://userid:pwd.2008@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": "admin@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=admin@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-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] boring-cyborg[bot] commented on issue #17831: MSSQL: Adaptive Server connection failed
Posted by GitBox <gi...@apache.org>.
boring-cyborg[bot] commented on issue #17831:
URL: https://github.com/apache/airflow/issues/17831#issuecomment-905544763
Thanks for opening your first issue here! Be sure to follow the issue template!
--
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-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] soares7vinicius commented on issue #17831: MSSQL: Adaptive Server connection failed
Posted by GitBox <gi...@apache.org>.
soares7vinicius commented on issue #17831:
URL: https://github.com/apache/airflow/issues/17831#issuecomment-926091811
@KarthikRajashekaran did you find any solution?
--
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-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [airflow] KarthikRajashekaran closed issue #17831: MSSQL: Adaptive Server connection failed
Posted by GitBox <gi...@apache.org>.
KarthikRajashekaran closed issue #17831:
URL: https://github.com/apache/airflow/issues/17831
--
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-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org