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.
Latzox
/
quickstart-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.
Quickstart Backend with SQL Database Connection
This use case involves deploying an Azure App Service with an Azure SQL Database to provide 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.
Objectives
- Deploy a scalable and secure web backend on Azure for testing new application features.
- Automate the infrastructure provisioning using Bicep.
- Integrate continuous deployment for the application for frequent testing and easy updates.
Components Overview
- Azure App Service - Deploy a simple backend API.
- Azure SQL Database - Set up a SQL database for persistence.
- Azure Container Registry (Optional) - Store container images for versioning (if you're using a containerized version).
- Continuous Integration/Continuous Deployment (CI/CD) - Automate deployment using GitHub Actions.
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.
Top comments (0)