Azure SQL Server Database connect using Azure Managed Service Identity (MSI)

Question

I want to Access the Azure SQL Database using python Azure Functions with MSI (Managed Service Identity) authentication.

I am trying to find out the how to connect Azure sql with MSI from azure functions for python but i didn't get any information.

Is there any way to access the Azure SQL Server database using MSI in Azure Functions?

I want to Access the Azure SQL Database without the passing the credentials in my code using azure function for python.

I enabled the identity option from azure functions for python.

Answer 1

If you just want to hide your Azure SQL connection string in your Azure function , using Azure Key Vault and MSI will be the best practices here : just saving your Azure SQL connection string as a secret in Azure key vault and follow this guide to do some configs on your Azure function will meet your requirement: your creds will never appears in your Azure function.

I created a key vault and stored my SQL sever connection string in Azure key vault as a secret,note the secret identifier as we will use it later :

Go to your key vault,config a access policy for your function msi to make sure that your function can access the secret : save it after your config :

This is my python demo code , it is easy as you can see , I am reading "SQLConn" from Azure web app :

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

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    cnxn = pyodbc.connect(os.environ["SQLConn"])
    cursor = cnxn.cursor()
    cursor.execute("select @@version")
    row = cursor.fetchall()
    return func.HttpResponse(str(row))

Let's set its value in app settings :

The value should be :

@Microsoft.KeyVault(SecretUri=<secret_uri_with_version which you noted from key valut>)

With the steps done , your azure function will be able to get SQL connection string from key vault and it will not appreared in your function app settings and there is no code need to change .

Btw, if you still want to use MSI to get access token to connect to your Azure SQL , I have a new demo posted here , which will be helpful for you.

Answer 2

I just did a quick test. Full step by step in here: https://github.com/crgarcia12/azure-function-msi-python

Summary:

You need to:

1. Enable Azure Function Managed Service Identity (MSI)
2. Enable AAD integration for Azure SQL Server
3. Add The Azure Function MSI User to the DB
4. Use Authentication=ActiveDirectoryMsi in your pyodbc.connect.

To add the MSI user to the DB you must connect using the AAD admin and then run this query:

CREATE USER "<MSI user display name>" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "<MSI user display name>" -- grant permission to read to database
ALTER ROLE db_datawriter ADD MEMBER "<MSI user display name>" -- grant permission to write to database

<MSI user display name> is usually the Azure Function Name. You can also get it using Get-AzureADObjectByObjectId -ObjectIds in PowerShell

This is the source code of a hello-world function:

import logging
import azure.functions as func

# Sql driver
import pyodbc

def main(req: func.HttpRequest) -> func.HttpResponse:

    try:

        logging.info('Python HTTP trigger function processed a request.')

        # Connecting to Azure SQl the standard way
        server = 'tcp:<servername>.database.windows.net' 
        database = '<dbname>' 
        driver = '{ODBC Driver 17 for SQL Server}' # Driver 13 did not work for me

        with pyodbc.connect(
            "Driver="
            + driver
            + ";Server="
            + server
            + ";PORT=1433;Database="
            + database
            + ";Authentication=ActiveDirectoryMsi", # This is important :)
        ) as conn:

            logging.info("Successful connection to database")

            with conn.cursor() as cursor:
                #Sample select query
                cursor.execute("SELECT Name FROM People;") 

                peopleNames = ''
                row = cursor.fetchone() 
                while row: 
                    peopleNames += str(row[0]).strip() + " " 
                    row = cursor.fetchone()

                return func.HttpResponse(f"Hello {peopleNames}!")
    except Exception as e:
        return func.HttpResponse(str(e))