[Python] SQL database access with managed identity from Azure Web App / Functions

How to secure your database connections with azure

6 min readJun 4, 2021

When we work on a data project, we want it to be as secure as possible, especially for access to databases that may contain confidential data. However, you need to be able to give access to your data to certain resources. This is where managed identities for Azure resources comes in.

There are several ways to secure a connection to a SQL database on Azure In this article we will focus on managed identities, which helps developers to manage credentials by providing an identity for the Azure resource in Azure AD and using it to obtain Azure Active Directory tokens. Credentials are not accessible to us, hence the security aspect.

We will take the example of an Azure Web App (or Azure Functions) and use python to connect to an Azure SQL database with managed identities.

Managed Identities

What are managed identities for Azure resources ?

Formerly named Managed Service Identity (MSI), managed identities for Azure Resources let you request tokens via a token endpoint on a particular resource like Azure Key Vault, Azure SQL, Azure Cosmos … More details here.

Benefits of using azure managed identity include:

Not having to manage credentials (they are not accessible to us)
Avoiding using username/password for database connection
Easy to configure
No additional cost

However, since managed identities is related to a specific Azure resource you can’t test your code locally (or in a CI/CD VM) as easily as with a username/password authentication.

It is possible to bypass this restriction by adding managed users to the database like yourself or the service principal you use to deploy projects from Azure DevOps. But this isn’t the purpose of this article.

That’s why, all the python code in this article is made to be run on an Azure resource (Web App or Functions).

MSI Authentication in the background

The Azure App Service gets its credentials from a local endpoint

2. Once the credentials are retrieved, the App Service uses them to get an access token from the Azure AD

3. Finally with this access token, the App Service can connect to our SQL Database.

Check this article for more details.

Enable system-assigned identity

First step is enabling the system assigned identity of your service like Azure Web App.

In this step, Azure creates a Service Principal for the instance of the service in Azure AD, and injects the credentials (client ID and certificate) for the Service Principal into the instance of the service.

To do so, you have to turn on the status identity In the settings menu of your web app.

It can be done directly from code if you are using Terraform for infrastructure management by setting the identity parameters. https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/app_service

identity {type = "SystemAssigned"}

Note : you need to have AAD admin created for you SQL db and add it in it: If this isn’t the case please refer to this article : Configure Azure Active Directory authentication — Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics

Connect and query your database using python

We are in the context of an Azure Web App (or Azure Functions) based on python script that would need access to an Azure SQL database and interact with it.

Once you enabled system assigned identity on your Azure Web App and gave SQL permissions as described in the previous steps you can get access to the database directly from python:

Code available on github :

https://github.com/HediHargam/AzureMSI-connect-webapp-to-sqldb/blob/main/sql_db_conn.py — Connect to preview

import pyodbc
from logzero import loggerwith pyodbc.connect(
        "Driver=" + driver + ";Server=" + server + ";PORT=1433;Database=" + database
         + ";Authentication=ActiveDirectoryMsi",
) as conn:
    logger.info("Successful connection to database")with conn.cursor() as cursor:
        cursor.execute(“select @@version")).fetchall()

There are 3 main parameters:

Driver: you should use : “{ODBC Driver 17 for SQL Server}”
Server: The sql server on which is your database
Database: The name of your database
Authentication: To specify the connection method “ActiveDirectoryMsi”

To sum up, here is an example of a function that allows a connection with the sql database and the creation of a dataframe from a table:

import pyodbc
import pandas as pd
from logzero import loggerdef get_data_from_sql_db(
        server: str, database: str, driver: str, query: str
) -> pd.DataFrame:
    """"Get data from SQL database with MSI Authentication and return df from executed query"""with pyodbc.connect(
            "Driver=" + driver + ";Server=" + server + ";PORT=1433;Database=" + database,
            + ";Authentication=ActiveDirectoryMsi",
    ) as conn:
        logger.info("Successful connection to database")with conn.cursor() as cursor:
            cursor.execute(query).fetchall()
            data = pd.read_sql(query, conn)return dataif __name__ == "__main__":
    df = get_data_from_sql_db(
        server="yourserver.database.windows.net",
        database="yourdatabasename",
        driver="{ODBC Driver 17 for SQL Server}",
        query="SELECT * FROM YOURTABLE",
    )print(df.head())

To go further …

Since all this article is about Azure SQL db, there is some code that might be useful for other sql database like Postgres SQL that can’t handle managed identities as easly as with an Azure SQL db.

import os
import requests
import pyodbc
import structdef get_token_with_msi(resource_uri: str):
    "Retrieve token in the right format (binary structure) for SQL connection with SQL_COPT_SS_ACCES_TOKEN attribute"identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]token_auth_uri = (
        f"{identity_endpoint}?resource={resource_uri}&api-version=2019-08-01"
    )
    head_msi = {"X-IDENTITY-HEADER": identity_header}resp = requests.get(token_auth_uri, headers=head_msi)
    access_token = bytes(resp.json()["access_token"], "utf-8")
    exptoken = b""
    for i in access_token:
        exptoken += bytes({i})
        exptoken += bytes(1)
    struct_token = struct.pack("=i", len(exptoken)) + exptokenreturn struct_token
    
    
token = get_token_with_msi(resource_uri="https://ossrdbms-aad.database.windows.net")

The Identity endpoint and the identity header are automatically created when you enable system-assigned identity on your web app. They are available in the environmental variables.

Then, replace the connection string with :

pyodbc.connect(
        "Driver=" + driver + ";Server=" + server + ";PORT=1433;Database=" + database,
        attrs_before={1256: bytearray(struct_token)},
    )

This part of the code is more complicated, to be able to establish a connection it is necessary to pass the token as a connection attribute as a binary structure. Microsoft documentation specify that to use the access token obtained from Azure AD for authentication we have to set the SQL_COPT_SS_ACCES_TOKEN connection attribute to a pointer to an ACCESTOKEN strcture. That’s why we use bytes and struc package which can handle python and C structures conversion, you can refer to this issue for more details.

Now that we have our token we can connect to the database and query your database: you need to specify the SQL_COPT_SS_ACCES_TOKEN (1256) attribute which is specific to Microsoft ODBC Driver for SQL and not (yet) implemented in pyodbc.

Summary

At the time of writing this article, there is not yet much help or explanation on the database connection part especially in python. I hope I brought you some help and help you to secure your database connection from Azure Web App / Azure Functions.