How to connect Azure SQL database from Python Function App using managed identity or access token

This blog will demonstrate on how to connect Azure SQL database from Python Function App using managed identity or access token. If you are looking for how to implement it in Windows App Service, you may refer to this post: https://techcommunity.microsoft.com/t5/apps-on-azure-blog/how-to-connect-azure-sql-database-from-azure-app-service-windows/ba-p/2873397.

Note that Azure Active Directory managed identity authentication method was added in ODBC Driver since version 17.3.1.1 for both system-assigned and user-assigned identities. In Azure blessed image for Python Function, the ODBC Driver version is 17.8. Which makes it possible to leverage this feature in Linux App Service.

Briefly, this post will provide you a step to step guidance with sample code and introduction on the authentication workflow.

Steps:

1. Create a Linux Python Function App from portal

2. Set up the managed identity in the new Function App by enable Identity and saving from portal. It will generate an Object(principal) ID for you automatically.

3. Assign role in Azure SQL database.

Search for your own account and save as admin.

Note: Alternatively, you can search for the function app's name and set it as admin, then that function app would own admin permission on the database and you can skip step 4 and 5 as well.

4. Got to Query editor in database and be sure to login using your account set in previous step rather than username and password. Or step 5 will fail with below exception.

"Failed to execute query. Error: Principal 'xxxx' could not be created. Only connections established with Active Directory accounts can create other Active Directory users."

5. Run below queries to create user for the function app and alter roles. You can choose to alter part of these roles per your demand.

CREATE USER "yourfunctionappname" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "yourfunctionappname"
ALTER ROLE db_datawriter ADD MEMBER "yourfunctionappname"
ALTER ROLE db_ddladmin ADD MEMBER "yourfunctionappname"

6. Leverage below sample code to build your own project and deploy to the function app.

Sample Code:

Below is the sample code on how to use Azure access token when run it from local and use managed identity when run in Function app. The token part needs to be replaced with your own. Basically, it is using "pyodbc.connect(connection_string+';Authentication=ActiveDirectoryMsi')" to authenticate with managed identity.

Also,  "MSI_SECRET" is used to tell if we are running it from local or function app, it will be created automatically as environment variable when the function app is enabled with Managed Identity.

The complete demo project can be found from: https://github.com/kevin808/azure-function-pyodbc-MI

import logging
import azure.functions as func
import os
import pyodbc
import struct

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    server="your-sqlserver.database.windows.net"
    database="your_db"
    driver="{ODBC Driver 17 for SQL Server}"
    query="SELECT * FROM dbo.users"
    # Optional to use username and password for authentication
    # username = 'name' 
    # password = 'pass'
    db_token = ''
    connection_string = 'DRIVER='+driver+';SERVER='+server+';DATABASE='+database
    #When MSI is enabled
    if os.getenv("MSI_SECRET"):
        conn = pyodbc.connect(connection_string+';Authentication=ActiveDirectoryMsi')
    
    #Used when run from local
    else:
        SQL_COPT_SS_ACCESS_TOKEN = 1256

        exptoken = b''
        for i in bytes(db_token, "UTF-8"):
            exptoken += bytes({i})
            exptoken += bytes(1)

        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken
        conn = pyodbc.connect(connection_string, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })
        # Uncomment below line when use username and password for authentication
        # conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

    cursor = conn.cursor()
    cursor.execute(query) 
    row = cursor.fetchone()

    while row:
        print(row[0])
        row = cursor.fetchone()

    return func.HttpResponse(
            'Success',
            status_code=200
    )

Workflow:

Below are the workflow in these two authentication ways, with them in mind, we can understand what happened under the hood.

• Managed Identity:

When we enable the managed identify for function app, a service principal will be generated automatically for it, then it follows the same steps as below to authenticate in database.

Function App with managed identify -> send request to database with service principal -> database check the corresponding database user and its permission -> Pass authentication.

• Access Token:

The access toke can be generated by executing ‘az account get-access-token --resource=https://database.windows.net/ --query accessToken’ from local, we then hold this token to authenticate. Please note that the default lifetime for the token is one hour, which means we would need to retrieve it again when it expires.

az login -> az account get-access-token -> local function use token to authenticate in SQL database -> DB check if the database user exists and if the permissions granted -> Pass authentication.

Thanks for reading. I hope you enjoy it.