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.

Are you sure those environment variable names are correct? The key names supplied to Python's os.environ[...] are usually case-sensitive and these are usually uppercase on the host operating systems. i.e.: python -c "import os; print(os.environ['pwd']);" would fail with raise KeyError(key) from None KeyError: 'pwd' whereas python -c "import os; print(os.environ['PWD']);" would successfully print /Users/FooBarBaz. — AlwaysLearning
– AlwaysLearning, Commented Mar 27, 2024 at 11:25
3. Is it possible that the website doesn't have access to the database/server, but my local device does? Yes, that's entirely possible. Azure SQL databases can have firewall rules applied to them just like any other service. Is it possible that you're company's public IP address(es) are granted access but your web application is not? — AlwaysLearning
– AlwaysLearning, Commented Mar 27, 2024 at 11:29
Additional Configurations or Settings in Azure:Check the firewall settings in Azure SQL Server. Ensure that Azure services and resources are allowed to access the SQL Server. You can configure this in the Azure portal under the Firewall settings of your SQL Server. Make sure the credentials (username and password) you're using to connect to the SQL Server are correct and have the necessary permissions. Verify that the Azure Web App has network connectivity to the SQL Server. You can try pinging the SQL Server from within the Azure Web App's environment. — P Sampath
– P Sampath, Commented Mar 27, 2024 at 22:30
VNet integration allows your Azure Web App to access resources in your virtual network. If your SQL Server is hosted within a virtual network and access to it is restricted, you may need to set up VNet integration for your Azure Web App. This would allow the Web App to communicate with resources within the virtual network, including the SQL Server. — P Sampath
– P Sampath, Commented Mar 27, 2024 at 22:30

P Sampath · Accepted Answer · 2024-04-02 15:32:28Z

0

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>

enter image description here

edited Apr 2, 2024 at 15:32

answered Apr 2, 2024 at 13:19

P Sampath

4,2832 gold badges7 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ahad Anjum Over a year ago

Thank you for sharing your code! I tried to utilize it and it did not work because I am saving information from my tables as panda dataframes and I received an error message saying that pandas require the use of SQLAlchemy

P Sampath Over a year ago

Refer to this So for pandas

Ahad Anjum Over a year ago

based on the So, the user is reading a csv file as a df and then inserting that into his SQL table. I am doing something quite different. My function accepts a UserID, employs a recursive CTE to identify company hierarchies, then fetches and filters data from four tables based on user access levels, returning comprehensive pandas data frames. I use pd.read_sql_query and pd.read_sql in my code. Will the So still be helpful given the extra information provided?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related