Random Errors with pyodbc and ODBC Driver 17 for SQL Server when Connecting to MSSQL Database

Bill9x 0 Reputation points
2024-03-07T03:33:32.73+00:00

I keep getting random errors while creating connections to mssql db like the below.

Those happen randomly and if I restart my jobs, it works again, until it doesn't and starts throwing similar errors again. I use pyodbc with {ODBC Driver 17 for SQL Server}.

Not sure what the problem is. Any ideas? Most common error i see is the "Communication link failure".

('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (0) (SQLExecDirectW)')

('Connection aborted.', ConnectionAbortedError(10053, 'An established connection was aborted by the software in your host machine', None, 10053, None))

('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (0) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT)); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (0)')

Azure SQL Database
Azure
Azure
A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.
930 questions
{count} votes

2 answers

Sort by: Most helpful
  1. ShaktiSingh-MSFT 13,271 Reputation points Microsoft Employee
    2024-03-07T04:18:38.18+00:00

    Hi Bill9x •,

    Welcome to Microsoft Q&A forum.

    As I understand, you are getting Communication link failure for SQL Server when Connecting to MSSQL Database.

    Try below:

    • Look for network issues.
    • Check performance on target/application server
    • Implement the retry-logic at connection and command execution.

    Refer to complete blogpost to handle such error: https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-215-hands-on-labs-communication-link-failed-error/ba-p/3565011

    Also, update ODBC driver, check firewall, network connectivity, logs etc

    Let us know if this helped.

    If not, we will continue to engage more on this for troubleshoot.

    Thanks

    0 comments No comments

  2. Vahid Ghafarpour 17,700 Reputation points
    2024-03-07T05:14:26.87+00:00

    Because the established connection was aborted by the software in your host machine, you must check your antivirus/firewall to find the reason of interrupting.

    ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **

    0 comments No comments