Quickstart - Secure AAS Backend with SQL Database using Bicep and Managed Identity

Deploying a scalable, secure backend on Azure with automated infrastructure setup is easier than ever with Bicep templates. Today, I’ll guide you through a quickstart project that demonstrates how to set up a Python-based Azure App Service connected to an Azure SQL Database using a Managed Identity, eliminating the need for managing database credentials. I’ve created a public GitHub repository template with all the necessary code and workflows, so you can clone, adapt, and deploy this setup effortlessly.

GitHub - Latzox/sample-webapp-backend-with-sql-database: A lightweight backend environment for testing new application features. It includes everything from hosting the app to managing data persistence and integrates CI/CD for easy testing and iteration.

A lightweight backend environment for testing new application features. It includes everything from hosting the app to managing data persistence and integrates CI/CD for easy testing and iteration.…

GitHubLatzox

Overview of the Quickstart Project

This project deploys a simple backend with the following components:

• Python API running on Azure App Service, which serves data via an endpoint at /employees.
• Azure SQL Database with sample data, accessed by the App Service using a Managed Identity.
• Bicep templates to automate the entire infrastructure deployment, including:

• Resource group setup
• SQL Server and SQL Database provisioning
• App Service deployment
• Managed Identity setup and Container Registry role assignments

• GitHub Actions CI/CD Pipelines for:

• Building and publishing the Docker container for the app
• Integration testing of Bicep templates
• Deployment of infrastructure to production

The key benefit of this approach is using Managed Identity for secure, passwordless access to the database. Let’s dive into how each part is implemented!

Deploying Infrastructure with Bicep

In the repository, the Bicep templates handle provisioning resources and setting up the required permissions. Here’s a breakdown of the main resources provisioned:

• Resource Group
• Azure SQL Server and Database
• Azure App Service
• Managed Identity and Role Assignments

Permissions for querying data

To allow the App Service to access the database without secrets, the managed identity is granted necessary database roles:

CREATE USER [<displayname-of-appservice>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<displayname-of-appservice>];
ALTER ROLE db_datawriter ADD MEMBER [<displayname-of-appservice>];
ALTER ROLE db_ddladmin ADD MEMBER [<displayname-of-appservice>];

Implementing the Flask API with Managed Identity

The API, which is part of the GitHub repository, exposes an endpoint to retrieve employee data from the database. The connection to Azure SQL is made using the managed identity, making it both secure and streamlined.

Code for Database Connection and Employee Endpoint

The connection to the SQL database is established using Azure Active Directory (AD) authentication with Managed Identity:

def get_db_connection():
    connection_string = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server}.database.windows.net;PORT=1433;DATABASE={database};Authentication=ActiveDirectoryMsi'
    return pyodbc.connect(connection_string)

This function connects to the SQL database without requiring any sensitive information like secrets.

To fetch the employee data, we use a simple query, converting the result to JSON for easy API responses:

def get_employees():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT ID, Name, Position, Salary FROM Employees')
    rows = cursor.fetchall()
    conn.close()

    employees = []
    for row in rows:
        employees.append({
            'ID': row.ID,
            'Name': row.Name,
            'Position': row.Position,
            'Salary': row.Salary
        })

    return jsonify(employees)

This setup ensures that the App Service can seamlessly access the database, providing a secure and efficient backend for our application.

Automating with GitHub Actions

The repository includes three GitHub Actions workflows that automate the CI/CD process:

1. Docker Build and Publish: Builds the Docker image for the Python API and pushes it to a container registry. This step ensures that the API is readily deployable on Azure App Service.
2. Bicep Template Integration Testing: Uses PSRule for Azure to verify the Bicep templates’ integrity and compliance. This workflow checks for best practices and potential issues before deploying resources.
3. Production Deployment: Deploys the resources to Azure using the validated Bicep templates. This workflow deploys a new environment or updates the existing infrastructure automatically.

Using Azure App Service, Azure SQL, and Managed Identity together provides a powerful foundation for secure, scalable backends. This project demonstrates how Bicep can automate the infrastructure setup, while GitHub Actions handle CI/CD, from Docker image creation to production deployment.

The provided GitHub repository makes it easy for you to try this setup, adapt it to your requirements, and leverage it as a template for your next Azure-based project.