Here is a follow-up practical exercise that builds directly on the previous lab. Make sure, that you have a Flask web app prepared.

This lab introduces:

• Azure SQL Database (relational database)
• Database connection strings
• Flask + relational database integration
• Basic CRUD operations

By the end of this lab, you will:

• Create an Azure SQL Database
• Configure firewall access
• Connect Flask to a relational database
• Store and retrieve data
• Use environment variables securely
• Redeploy and test a database-backed application

1 Create Azure SQL Database

A relational database stores data in tables. It uses rows and columns and enforces relationships.

Example table:

id	name
1	Alice
2	Bob

Azure SQL Database is a managed relational database service. It is based on Microsoft SQL Server.

Benefits:

• High availability
• Automatic backups
• Secure cloud access
• Scalable

Create SQL Server with command:

az sql server create \
  --name <unique-sql-server-name> \
  --resource-group <resource-group> \
  --location westeurope \
  --admin-user sqladmin \
  --admin-password <StrongPassword123!>

Then, create a SQL Database:

az sql db create \
  --resource-group <resource-group> \
  --server <unique-sql-server-name> \
  --name studentdb \
  --service-objective Basic

You need to configure the firewall to allow access to another Azure service:

az sql server firewall-rule create \
  --resource-group <resource-group> \
  --server <unique-sql-server-name> \
  --name AllowAzureServices \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

This rule allows your Azure Web App to connect to the database. You can find more information about the Azure Firewall in this tutorial.

2 Modify the Flask Application

After the database is ready and running, you can modify the web application to use the relational database:

Inside your project folder, install the database dependencies:

pip install pyodbc sqlalchemy

Note: pyodbc also requires the Microsoft ODBC Driver for SQL Server to be installed at the system level. See the official installation guide for your operating system.

Update requirements:

pip freeze > requirements.txt

Add code that uses sqlalchemy to write and read some data from the database.

Note, that the database is configured using an environment variable.

Replace your existing app.py with:

from flask import Flask, request
from sqlalchemy import create_engine, text
import os

app = Flask(__name__)

# Get connection string from environment variable
DATABASE_URL = os.environ.get("DATABASE_URL")

if not DATABASE_URL:
    raise RuntimeError("DATABASE_URL environment variable is not set")

engine = create_engine(DATABASE_URL)

@app.route("/")
def home():
    return "Database connected Flask App"

@app.route("/init")
def init_db():
    with engine.connect() as conn:
        conn.execute(text("""
            IF OBJECT_ID('students', 'U') IS NULL
            CREATE TABLE students (
                id INT PRIMARY KEY IDENTITY(1,1),
                name VARCHAR(100)
            )
        """))
        conn.commit()
    return "Table created!"

@app.route("/add/<name>")
def add_student(name):
    with engine.connect() as conn:
        conn.execute(text("INSERT INTO students (name) VALUES (:name)"), {"name": name})
        conn.commit()
    return f"Added {name}"

@app.route("/students")
def list_students():
    with engine.connect() as conn:
        result = conn.execute(text("SELECT name FROM students"))
        students = [row[0] for row in result]
    return "<br>".join(students)

if __name__ == "__main__":
    app.run()

3 Configure the application to use the Database Connection String

You have to find out your database connection string.

Usually, it looks like this:

mssql+pyodbc://username:password@server.database.windows.net:1433/studentdb?driver=ODBC+Driver+17+for+SQL+Server

For example:

mssql+pyodbc://sqladmin:StrongPassword123!@myserver.database.windows.net:1433/studentdb?driver=ODBC+Driver+17+for+SQL+Server

TODO: How to find the database connection string? (Hint: Azure Portal → SQL Database → Connection strings)

After you know your database connection string, you should set up the environment variable:

az webapp config appsettings set \
  --name <webapp-name> \
  --resource-group <resource-group> \
  --settings DATABASE_URL="<your-connection-string>"

This securely stores credentials in Azure.

Local testing tip: To test locally before deploying, set the variable in your shell:

export DATABASE_URL="<your-connection-string>"

4 Redeploy and Test the Application

az webapp up

Open:

https://<webapp-name>.azurewebsites.net/init

Then add students:

https://<webapp-name>.azurewebsites.net/add/Alice
https://<webapp-name>.azurewebsites.net/add/Bob

List students:

https://<webapp-name>.azurewebsites.net/students

You should see:

Alice
Bob

You can also monitor the application logs to see what is happening:

az webapp log tail \
  --name <webapp-name> \
  --resource-group <resource-group>

Clean Up

Don't forget to tear down the application to save your credit.

az group delete --name <resource-group> --yes --no-wait

Bonus Tasks

1. Add a new column email to the table.
2. Modify /add to accept both name and email.
3. Add a route to delete a student.
4. Modify the app to return JSON instead of plain text.

Security Discussion

Why credentials should NOT be hardcoded

If you put a password or connection string directly in source code, it will end up in version control (git) and be visible to anyone with access to the repository — including public repositories on GitHub. It is very difficult to fully remove secrets once committed.

Why environment variables are safer

Environment variables are set at runtime in the deployment environment and are never stored in source code. Azure App Service allows you to set them as Application Settings, which are encrypted at rest and injected into the app at startup. This separates secrets from code.

Firewall rules

The firewall rule used in this lab (0.0.0.0 to 0.0.0.0) allows all Azure services to connect. This is convenient but broad. In production, you should restrict access to specific IP addresses or use Private Endpoints to prevent unauthorized access from other Azure tenants.

Principle of least privilege

The database user used by the application should have only the permissions it needs — typically SELECT, INSERT, UPDATE, and DELETE on specific tables. It should not be the server admin account (sqladmin). This limits the damage if credentials are ever compromised.

Previous Post Next Post