How to fix timeout error in connection using pyodbc in airflow

Xavier Langa 0

Hi,

I am running an airflow DAG in docker, where I connect to sql server 2019 using a pyodbc connection function.

My connection function is as:

def get_db_connection():
    return pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server};'
        'SERVER=xxx.xxx.x.xx\\XXX2024;'
        'DATABASE=somedatabase;'
        'UID=username;'
        'PWD=password;'
    )

When the connection exceeds 15 seconds, I get this error:

return pyodbc.connect( pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

even when setting:

connection.timeout = 30

Can someone help me?

Erland Sommarskog 113.6K Reputation points MVP

2024-11-23T10:48:40.4233333+00:00

"When the connection exceeds?" That kind of suggests that sometimes it does not and connection succeeds? Or does it always fail?

I don't have too much of an idea, but I played a little, and it seems that it the server and/or instance name is wrong, you get a different error message. I also did a silly test where I added a logon trigger with a WAITFOR. In this case, I got this error message:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Shared Memory Provider: Timeout error [258]. (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Unable to complete login process due to delay in login response (258)')

You did not get the second part, but possibly this is because you get stuck earlier.
Xavier Langa 0 Reputation points

2024-11-24T12:38:16.1666667+00:00

Hi Erland Sommarskog, it does not always fail. It only fails when the connection attempt exceeds 15 seconds.
Erland Sommarskog 113.6K Reputation points MVP

2024-11-24T13:33:46.5433333+00:00

And you increased the timeout to 30 seconds you still got the error? After 30 seconds? Or already after 15 seconds?

When connection succeeds, are there any notable delays, like sometimes taking 14 seconds? Or is it always quick?

I'm asking these questions to give you inspiration for your troubleshooting.

1 answer

LiHongMSFT-4306 29,191 Reputation points

2024-11-25T03:00:13.19+00:00

Hi @Xavier Langa

it does not always fail. It only fails when the connection attempt exceeds 15 seconds.

Maybe you could try connecting using other tools like SSMS to verify if the issue still exists.

In other hand, you could check the SQL Server error logs which may provide more details.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to fix timeout error in connection using pyodbc in airflow

1 answer

Your answer