Lesson Learned #359: TCP Provider: Error code 0x68 (104) (SQLExecDirectW)

[Jose_Manuel_Jurado]

Today, we got a service request that our customer faced the following error message connecting to the database:  (pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)'). This customer is using Python in a Linux environment. Following I would like to share my lessons learned about this error message. 

Checking what does mean 0x68 is 104 in decimal, and that is a Connection Reset by Peer error (i.e. the server abruptly closed the connection) and in Linux errors are listed here: https://github.com/torvalds/linux/blob/master/include/uapi/asm-generic/errno.h

Most probably, this error means that our customer is opening the connection using proxy policy Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Micr... and it was in idle state for more than 30 minutes. In this situation after 30 minutes in idle state the connection will be closed by Gateway - Lesson Learned #214: Hands-On-Labs: How to manage an idle connection state issue - Microsoft Communi... - Understanding Connectivity Issues in Azure SQL Database - Microsoft Community Hub if the application tries to use the connection without checking if the connection is closed you are going to have this error message. 

We have several options to mitigate this issue:

• Enable connection pooling in Linux has to be enabled both by the application and in the driver manager, and is off by default might and also we are going to improve the connection at client side.
• Change to redirect, Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Micr... improving the connection at server side and remove idle connection thershold. 
• Implement the retry logic as follow in Python.

import pyodbc
import time

def PythonRetryLogicConnectToTheDB():
    try:
        nTimes=0
        while nTimes <5:
                nTimes=nTimes+1
                print("Connecting to the DB - Attempt Number: %i " % nTimes)
                start_time = time.time()    
                conn = ConnectToTheDB()
                if( conn != None ):
                    print("Connected to the Database %s seconds ---" % ((time.time() - start_time)) )
                    return conn    
                else:
                  print("------ Next Attempt ----- Waiting for 5 seconds ---")       
                  time.sleep(5)     
        return 
    except Exception as e:
        print("An error occurred connecting to the DB - " + format(e))
        return 


def ConnectToTheDB():
    try:
        return pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};server=tcp:servername.database.windows.net,1433;UID=username;PWD=password;database=dbname;APP=Testing Connection;timeout=30");  
    except Exception as e:
        print("An error occurred connecting to the DB - " + format(e))
        return 


SQL = "select 1"
nLoop=1
while  nLoop<100:
    nLoop=nLoop+1
    conn = PythonRetryLogicConnectToTheDB()
    if( conn != None ):
        cursor = conn.cursor()
        start_time = time.time()    
        cursor.execute(SQL)
        row = cursor.fetchone() 
        print("---------------- Loop:%d - %s seconds ---" % (nLoop,(time.time() - start_time)) )
        conn.close()
    else:
        print("---------------- Loop:%d - (Not able to connect) " % (nLoop))   

Enjoy!