Connect Python Azure Function to Azure SQL Using Managed Identity

Azure, Function, Managed Identity, Python

An Azure Function is a lightweight, serverless compute option in Azure that lets you execute small chunks of code in response to events or schedules without managing servers. When connecting Azure Functions to Azure SQL Database, you need a secure way to authenticate without storing credentials. That’s where Managed Identity steps in. Managed Identity allows Azure resources (like Functions, VMs, Logic Apps) to access other Azure services securely by leveraging Azure Active Directory (AAD).In this article, we’ll explore how to connect Python Azure Function to Azure SQL using Managed Identity. You’ll learn step-by-step setup, Python code implementation, troubleshooting, and best practices to ensure your solution is secure and scalable.

Why Use Managed Identity with Azure SQL?

Storing passwords in configuration files is risky. Managed Identity solves this problem by:

  • Eliminating Secrets → No passwords, no connection strings with credentials.
  • Centralized Identity Management → Uses Azure AD authentication for consistency.
  • Least Privilege Principle → Assigns only the required SQL permissions.
  • Automatic Rotation → No need to update or rotate credentials manually.

This makes it the recommended authentication method when connecting Azure Functions to SQL Database.

Prerequisites:
Before implementing, ensure you have:
1. An Azure subscription.
2. An Azure SQL Database already created.
3. An Azure Function App (Python runtime).
4. Azure CLI or Portal access.

This guide assumes you already have a basic understanding of creating an Azure Function. If you need a refresher, please refer to our previous article on How to Create a Python Azure Function.

Let’s now see how to connect Python Azure Function to Azure SQL database using managed identity authentication. In this example we will fetch data from a public API and securely write it to an Azure SQL Database table..

Step 1: Set Up Your Azure SQL Database

Creating WeatherData Table

In this guide, we’ll simulate fetching weather data from an API and storing it in Azure SQL.

Run the following SQL script inside your Azure SQL database:

CREATE TABLE WeatherData (
    Id INT PRIMARY KEY IDENTITY(1,1),
    City VARCHAR(100),
    Temperature DECIMAL(5,2),
    Humidity INT,
    ForecastDate DATETIME
);

Step 2: Enabling Managed Identity for Azure Function

INavigate to your Function App in Azure Portal.

Under Settings → Identity, enable System Assigned Managed Identity.

Azure will create an identity in Microsoft Entra representing your Function.

Connect Python Azure Function to Azure SQL

Step 3: Grant the Managed Identity Access to Azure SQL

Configuring Azure Entra Admin

To allow Managed Identity to authenticate, assign an Azure Entra admin for your SQL server.

  1. Go to Azure Portal → SQL Server → Active Entra Admin.
  2. Assign an Azure Entra user/group as the admin.
  3. Save changes.

Grant the permissions

Now, connect to to your Azure SQL Database using the Entra ID authentication. You need to grant the Managed Identity permission to write to the database. Use the following T-SQL commands, replacing <your-azure-function-name> with the name of your function:

CREATE USER [your-azure-function-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datawriter ADD MEMBER [your-azure-function-name];
  • CREATE USER creates the identity inside SQL.
  • ALTER ROLE gives write permissions to insert/update/delete.

Learn how to grant access to Azure SQL databases in this post.

Step 4: Configure Application Settings

In the Function App Environment variables, define following variables.

  • SqlConnectionInfo → Server=<your-sql-server>.database.windows.net;Database=<your-db>;
  • WEATHER_API_URL → https://api.open-meteo.com/v1/forecast?latitude=60.17&longitude=24.94&hourly=temperature_2m,relative_humidity_2m
  • TIMER_SCHEDULE → 0 * * * * * (Runs every hour).
Azure Function configuration settings

Step 5: Writing Python Azure Function Code

Now, it’s time to write the code that will perform the data fetching and insertion. For this example, we’ll use a Python HTTP-triggered function and the requests and pyodbc libraries. You will also need the azure-identity package to handle the Managed Identity authentication.

1. Installing Required Packages

Add to requirements.txt:

azure-functions
requests
pypyodbc

2. Write the function code (__init__.py):

import azure.functions as func
import logging, os, requests, pypyodbc

app = func.FunctionApp()

@app.timer_trigger(schedule="%TIMER_SCHEDULE%", arg_name="myTimer")
def load_weather_data_to_sql(myTimer: func.TimerRequest):
    if myTimer.past_due:
        logging.info("The timer is past due!")

    try:
        url = os.getenv("WEATHER_API_URL")
        response = requests.get(url)
        data = response.json().get("current", {})
        
        temperature = data.get("temperature_2m")
        humidity = data.get("relative_humidity_2m")
        city = "Berlin"

        conn = get_sql_connection()
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO WeatherData (City, Temperature, Humidity, ForecastDate) VALUES (?, ?, ?, GETDATE())",
            (city, temperature, humidity)
        )
        conn.commit()
        cursor.close()
        conn.close()
        logging.info("Weather data inserted successfully")

    except Exception as e:
        logging.error(f"Error: {e}")

def get_sql_connection():
    conn_info = os.getenv("SqlConnectionInfo")
    parts = dict(item.split("=",1) for item in conn_info.split(";") if "=" in item)
    server, db = parts.get("Server"), parts.get("Database")
# Depending on the python version, select appropriate ODBC Driver. For latest version 3.12, use ODBC Driver 18. For previous versions, try ODBC Driver 17.
    conn_str = f"DRIVER={{ODBC Driver 18 for SQL Server}};Server={server};Database={db};Authentication=ActiveDirectoryMsi;"
    return pypyodbc.connect(conn_str)

Step 6: Testing the Integration

Running Function Locally

  • Use Azure Functions Core Tools.
  • Set local.settings.json with required env variables.
  • Test the API call and SQL insertion.

Deploying to Azure

  • Push code to Azure Function App.
  • Check logs in Application Insights or Log Stream.
  • Common Issues and Troubleshooting.

Best Practices for Secure and Scalable Integration

  • Use least privilege for SQL roles.
  • Store configs in Azure Key Vault.
  • Monitor using Azure Monitor + Application Insights.
  • Implement retry logic for transient failures.

FAQs

Conclusion

By following these steps, you’ve securely connected a Python Azure Function to an Azure SQL Database using Managed Identity. This approach avoids hardcoding credentials, ensures automatic rotation, and aligns with cloud security best practices.

With this foundation, you can extend the Function to handle more APIs, perform ETL pipelines, or automate reporting tasks, all while staying secure and compliant.

Pro tips:
1. Always prefer pypyodbc in Functions to avoid dependency issues.
2. Use retry policies when inserting data into SQL.
3. Keep Functions lightweight, offload heavy processing to Azure Data Factory or Microsoft Fabric.
4. If scaling out, validate SQL performance with connection pooling.
5. Learn how to connect to Azure SQL from Azure Data Factory using managed identity.

See more

Download Now
Kunal Rathi

With over 14 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.

Shopping Cart
Scroll to Top