Azure Web App Deployment: SQLAlchemy OperationalError with SQL Server Connection for Python Flask Application

Question

I'm deploying a Python Flask Application to Azure for the first time (free version). I have a Python Flask application that connects to a SQL Server database using SQLAlchemy. The connection to the database and tables happen as the website is loading. Locally, this runs perfectly. However, when I deploy the application to an Azure Web App, I encounter a connection timeout issue when trying to connect to the SQL Server. The application fails with an OperationalError indicating a login timeout expired. Below is the error traceback:

Traceback (most recent call last):
  File "/tmp/8dc4e3d6e3306fa/antenv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  ...
pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

This issue only occurs when the application is deployed to Azure, and not when running locally. Here are some specifics about the environment and configuration:

Environment: Azure Web App Database: SQL Server Connection Method: SQLAlchemy using pyodbc as the driver Versions of dependencies in requirements.txt file:

• cnos-connector==0.1.9
• pyodbc==5.1.0
• SQLAlchemy==2.0.28 Error: Login timeout expired

I have confirmed that the application connects to the database successfully from my local environment, so it seems like an issue specific to the Azure deployment.

Here is how I'm setting up the SQLAlchemy engine in my application:

user = os.environ["User_Name"]
password = os.environ["Password"]
hostName = os.environ["hostName"]
port = os.environ["port"]
db = os.environ["database"]
db_context = os.environ["context"]

# Establishing the database connection URI
database_uri = f"mssql+pyodbc://{user}:{password}@{hostName}:{port}/{db}?driver=ODBC+Driver+17+for+SQL+Server"
engine = create_engine(database_uri)

The environment variables are all properly spelt and correctly added to my Azure web app as environment variables under app settings.

I've tried increasing the connection timeout parameter (albeit I could try an even larger number). When run locally, the loading of the flask app takes about 20-35 seconds due to the database connection and queries I run to set things up. For more context/info here is the startup command used for the flask app:

gunicorn --bind=0.0.0.0 --timeout 600 app:app

I've checked and confirmed that the application works locally and that the environment variable names all match with what I have written on the Azure web app dashboard. I have not tried adding a connection string within the web app dashboard as I don't think that would help since the app works perfectly locally. Furthermore, the SQL server is up and running smoothly for myself and others.

UPDATE:

• I have added VNet integration to the web app and it should be able to communicate with the SQL Server without any issues, yet I am still getting the same error. I have confirmed that the environment variables are correct (all capitalized), and I use os.environ['var1'] to grab them in my code. The website must connect to the SQL database/server right as the page as being loaded (the page must be loaded in with a parameter that is passed on to a query of the db in the future) so I also set all the environments for the connection string in my environment variables as deployment slot settings. There is a default page that the application is meant to redirect to if the parameter is missing.

Furthermore, these are the latest error logs:

2024-03-29T13:31:08.492306143Z [2024-03-29 13:31:08 +0000] [78] [INFO] Starting gunicorn 21.2.0

2024-03-29T13:31:08.494285752Z [2024-03-29 13:31:08 +0000] [78] [INFO] Listening at: http://0.0.0.0:8000 (78) 2024-03-29T13:31:08.495537458Z [2024-03-29 13:31:08 +0000] [78] [INFO] Using worker: sync 2024-03-29T13:31:08.513749140Z [2024-03-29 13:31:08 +0000] [79] [INFO] Booting worker with pid: 79

2024-03-29T13:40:20.623Z ERROR - Container remi2_1_322f8881 for site remi2 did not start within expected time limit. Elapsed time = 600.4550416 sec 2024-03-29T13:40:20.631Z ERROR - Container remi2_1_322f8881 didn't respond to HTTP pings on port: 1433, failing site start. See container logs for debugging. 2024-03-29T13:40:20.675Z INFO - Stopping site remi2 because it failed during startup.

I'm a beginner to this so I am not sure what to do. I increased the startup time for the web app from 230 to 600 seconds and its still timing out.

Answer 1

The below Flask code interacts with a SQL Server database using the pyodbc library, utilizing routes to handle CRUD (Create, Read, Update, Delete) operations on a Persons table within the database.

# app.py
from flask import Flask, jsonify, request
import pyodbc

app = Flask(__name__)

# Function to get connection
def get_conn():
    try:
        conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server=tcp:your_server_address,1433;Database=your_database_name;Uid=your_username;Pwd=your_password;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')
        return conn
    except pyodbc.Error as e:
        raise Exception(f"Error connecting to the database: {e}")

# Check if table exists
def table_exists(conn):
    try:
        cursor = conn.cursor()
        tables = cursor.tables(table='Persons', tableType='TABLE').fetchall()
        return len(tables) > 0
    except pyodbc.Error as e:
        raise Exception(f"Error checking if table exists: {e}")

# Create table if not exists
with get_conn() as conn:
    if not table_exists(conn):
        try:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE Persons (
                    ID int NOT NULL PRIMARY KEY IDENTITY,
                    FirstName varchar(255),
                    LastName varchar(255)
                );
            """)
            conn.commit()
        except pyodbc.Error as e:
            raise Exception(f"Error creating table: {e}")

@app.route("/all")
def get_persons():
    try:
        rows = []
        with get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM Persons")

            for row in cursor.fetchall():
                rows.append({"id": row.ID, "first_name": row.FirstName, "last_name": row.LastName})
            return jsonify(rows)
    except Exception as e:
        return jsonify({"error": f"Error fetching persons: {e}"}), 500

@app.route("/person/<int:person_id>")
def get_person(person_id):
    try:
        with get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM Persons WHERE ID = ?", person_id)

            row = cursor.fetchone()
            if row:
                return jsonify({"id": row.ID, "first_name": row.FirstName, "last_name": row.LastName})
            else:
                return jsonify({"message": "Person not found"}), 404
    except Exception as e:
        return jsonify({"error": f"Error fetching person: {e}"}), 500

@app.route("/person", methods=["POST"])
def create_person():
    try:
        data = request.json
        first_name = data["first_name"]
        last_name = data.get("last_name")
        
        with get_conn() as conn:
            cursor = conn.cursor()
            cursor.execute("INSERT INTO Persons (FirstName, LastName) VALUES (?, ?)", (first_name, last_name))
            conn.commit()

        return jsonify({"message": "Person created successfully", "first_name": first_name, "last_name": last_name}), 201
    except KeyError as e:
        return jsonify({"error": f"Missing required field: {e}"}), 400
    except Exception as e:
        return jsonify({"error": f"Error creating person: {e}"}), 500

if __name__ == "__main__":
    app.run(debug=True, port=5000)

Create a virtual environment for the app:

python3 -m venv .venv
source .venv/bin/activate

requirements.txt:

Flask
pyodbc
pymssql
python-dotenv

web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <system.web>
        <customErrors mode="Off"/>
    </system.web>
</configuration>