Managed identities are essential for secure cross-service communication in Azure. They eliminate the need to manage secrets, keys, or connection strings, enabling seamless integration of application components. In this blog, I'll demonstrate how to connect an Azure SQL Database to a Python backend running on Azure App Service using managed identities.

Microsoft Authentication Library

To connect to Azure services using Entra identities, you'll need the Microsoft Authentication Library (MSAL). In this example I‘m using the Python library, but don‘t worry, the MSAL exists for every major programming language.

import msal

Here’s a simple function to connect to an Azure SQL Database:

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)

With these prerequisites in place, you can establish database connections within your code and execute queries, all without handling secrets or connection strings.

Demo Python Backend

For demonstration, I created a simple Python Flask API that returns employee data such as name, position, and salary. Notice how the get_db_connection() function is used to open the database connection and query the data.

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

    # Convert data to a list of dictionaries.
    employees = []
    for row in rows:
        employees.append({
            'ID': row.ID,
            'Name': row.Name,
            'Position': row.Position,
            'Salary': row.Salary
        })

    return jsonify(employees)

This simple approach ensures that your backend interacts securely with the database using managed identities.

Dockerfile

If you're deploying your application in Docker containers, here’s the Dockerfile to install the ODBC Driver for SQL Server:

FROM python:3.13-slim

COPY . /app
WORKDIR /app

# Install Microsoft ODBC Driver 17 for SQL Server and dependencies
RUN apt-get update \
 && apt-get install -y gnupg curl apt-transport-https \
 && curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc \
 && echo "deb [arch=amd64] https://packages.microsoft.com/debian/11/prod bullseye main" | tee /etc/apt/sources.list.d/mssql-release.list \
 && apt-get update \
 && ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev \
 && apt-get install -y build-essential \
 && apt-get clean -y

# Install Python dependencies
RUN pip install -r requirements.txt

EXPOSE 80

CMD ["gunicorn", "-w", "4", "-b", "0.0.0.0:80", "app:app"]

This setup ensures that your container is ready to securely connect to Azure SQL.

SQL Server and Database Deployment

When deploying an Azure SQL server, configure Azure AD Only Authentication. This is a requirement for the managed identity. Below is the Bicep template used to deploy the SQL server and database:

resource sqlServer 'Microsoft.Sql/servers@2023-08-01-preview' = {
  name: serverName
  location: location
  tags: {
    workload: 'Sample Backend with SQL Database'
    topic: 'SQL Server'
    environment: 'Production'
  }
  properties: {
    minimalTlsVersion: '1.2'
    administrators: {
      administratorType: 'ActiveDirectory'
      login: sqlAdminName
      sid: sqlAdminObjectId
      tenantId: tenantId
      principalType: principalType
      azureADOnlyAuthentication: azureADOnlyAuthentication
    }
  }
}

resource sqlDB 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  parent: sqlServer
  name: sqlDBName
  location: location
  sku: {
    name: sqlDBSkuName
    tier: sqlDBSkuTier
    capacity: capacity
  }
}

This template ensures the database is securely configured and ready for use.

Granting Database Roles to Managed Identity

To enable your App Service to access the database without secrets, assign necessary database roles to the managed identity. You can‘t perform this step with Bicep or Terraform. Create a custom script or access the database via the Azure Portal.

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>];
GO

These roles allow the managed identity to perform read, write, and schema-altering operations as needed.

Public Template on GitHub

For the complete code, including CI/CD integration, check out the public template on GitHub. This repository contains everything you need to replicate the setup described in this blog.

Using managed identities simplifies cross-service communication and enhances security by eliminating the need for secrets. This approach is highly recommended for anyone building secure and scalable applications in Azure.