MsSQLHook and pymssql Adaptive Server connection failed

[anthony-joyce]

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

[gordthompson]

@anthony-joyce - As in pymssql/pymssql#913 , are you using an environment variable similar to this?

export AIRFLOW_CONN_MSSQL_DEFAULT='mssql+pymssql://usr:[email protected]/dbname'

[anthony-joyce]

@gordthompson Hi Gord. Thanks for the reply. I am not, currently. I was using the connection variable from the Airflow environment (Airflow menu --> Connections). Unless that connection is getting rendered incorrectly before being passed to mssql.....

Should I be using an environment variable similar to what you have posted?

Thanks!

[gordthompson]

I have zero experience with Airflow so I don't know the best approach to use in your case. I was really asking if you were using a connection URL of that form. If you were, then I would suggest trying

mssql+pymssql://usr:[email protected]/dbname?tds_version=7.0

to see if that helps. Can you add the extra tds_version connection parameter using your method?

[anthony-joyce]

Added {tds_version: 7.0} to the connection but still receiving errors.

Connection URI looks like: INFO - mssql+pymssql://{username}:***@{host}:{port}/{db_name}?tds_version=7.0
I blocked out the important DB info for security.
Connection URI looks exactly the same as when I revert to get things working again.

Hopefully others will chime in as the is getting triaged. Thanks again @gordthompson.

[gordthompson]

@ in a password will always be escaped to %40 in a stringified URL so it won't be misinterpreted as the actual @

import sqlalchemy as sa

url = sa.engine.URL.create(
    "mssql+pymssql",
    username="bob",
    password="pass@word",
    host="localhost",
    database="mydb",
)
print(url.render_as_string(hide_password=False))
# mssql+pymssql://bob:pass%40word@localhost/mydb

Do both the older and newer Airflow components behave the same way w.r.t. password escaping/un-escaping?

[anthony-joyce]

Got it! I am working on that now. Will respond back when I have an answer.

[anthony-joyce]

@gordthompson Looks like prior installation does the same thing, but I can connect just fine. I'll have to look at the client end logs to see if something is happening during authorization into the DB.

[gordthompson]

Just in case you haven't heard: Setting the TDSDUMP environment variable before running your test script, e.g.,

export TDSDUMP=/tmp/tdsdump.txt

logs all of the gory details to the specified file. For example, if I try connecting to SQL Server 2008 without tds_version=7.0 I see

net.c:391:Connecting with protocol version 7.4
…
tls.c:1036:handshake failed with -1 3 1
tls.c:1080:handshake failed
login.c:670:login packet rejected
query.c:3804:tds_disconnect() 

whereas with tds_version=7.0 I see

net.c:391:Connecting with protocol version 7.0
net.c:318:Connecting to 192.168.0.100 port 1433
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
login.c:1124:quietly sending TDS 7+ login packet
token.c:418:tds_process_login_tokens()
packet.c:410:Received packet

[anthony-joyce]

Thanks @gordthompson !

I'll try that.

[anthony-joyce]

@gordthompson Thank you! After checking the logs, I found it was using a free_tds.conf file located in a directory I didn't expect. Anywho, I was able to change the tds_version via that conf file and fix the server name (this one was outdated unintentionally) and we are back in business!

Thanks so much. I also learned a lot from you. Very, very appreciated.

[potiuk]

This is at most a discussion at this stage, not "airflow issue". Hopefully someone will help you - but without having details of the issue and you looking at and digging out server logs explaining what was the root cause of the issue. There are lots of similar issues if you google for it - for example including wrong or bad passwords or too long ones etc., and the error you see might be indication of any pof those - and I believe the only way to know the reason is to actually look at server logs - because for security reasons the reason why your authentication failed is never explained to the client, so you are the only person in the world who can look at your server logs and see why connection attempt was rejected.

Most likely however you have some problem with URL-encoding of your URL (See more details about it in the documentation of Airflow https://airflow.apache.org/docs/apache-airflow/stable/howto/connection.html - where URI format and some tools to generate the right URI are described and explained. But this is a wild guess and you need to experiment and look in your logs and attempt to figure it out on your own.