Using Azure Function MSI to connect to Azure SQL in Python

Since I had to spend a few hours figuring this out, and all examples/docs are wrong, here’s an example of how to use Python in an Azure Function to connect to an Azure PaaS database without credentials by utilizing the managed identity of the azure function app.

__init__.py:

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

resource_uri="https://database.windows.net/"
sql_server="XXXXXX.database.windows.net"
sql_database="primary"

def get_bearer_token(resource_uri):
    identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]
    logging.info('identity_endpoint: {}'.format(identity_endpoint))
    logging.info('identity_header : {}'.format(identity_header))
    token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&api-version=2017-09-01"
    head_msi = {'X-IDENTITY-HEADER':identity_header}
    resp = requests.get(token_auth_uri, headers=head_msi)
    access_token = resp.json()['access_token']
    logging.info('response received from token endpoint: {}'.format(access_token))
    return access_token  

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Function Starting')
    try:
        access_token = get_bearer_token(resource_uri)
        accessToken = bytes(access_token, 'utf-8')
        exptoken = b""
        for i in accessToken:
                exptoken += bytes({i})
                exptoken += bytes(1)
        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken  
        conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=tcp:{},1433;Database={}".format(sql_server,sql_database), attrs_before = { 1256:bytearray(tokenstruct) })
        logging.info('connected to {} on {}'.format(sql_server,sql_database))
        cursor = conn.cursor()
        cursor.execute("select @@version")
        row = cursor.fetchall()
        logging.info('sql data: {}'.format(row[0])) 
        logging.info('finished')              
    except BaseException as error:
        logging.info('An exception occurred: {}'.format(error))   
    return func.HttpResponse("done!")

requirements.txt:

astroid==2.4.2
azure-functions==1.2.1
certifi==2020.6.20
chardet==3.0.4
colorama==0.4.3
idna==2.9
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
pylint==2.5.3
pyodbc==4.0.30
requests==2.24.0
six==1.15.0
toml==0.10.1
urllib3==1.25.9
wrapt==1.12.1

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments