Access to Azure SQL DB using UMI via Azure Functions

Question

We aim to use a User-Managed Identity (UMI) to access a SQL database, with the UMI linked to a service account that has the appropriate permissions on the database. We're utilizing the connection string provided in the Microsoft documentation (https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-active-directory-managed-identity-authentication) to connect to the database from Azure Functions. However, it's not working as expected. Could you provide support to help us resolve this issue?

Connection string used : Driver={ODBC Driver 18 for SQL Server};Server=xxxxxx.database.windows.net;Database=xxxxxxxx; Authentication=ActiveDirectoryMSI;User Id=xxxxxxxxxxxxxxxx;

Answer 1

Hi @Melili Ismail Thank you for sharing the additional information.

In order to be able to connect with User Managed Identity from Python Function App, you should use the connection string in the following format server=Server;database=Database;UID=myObjectId;Authentication=ActiveDirectoryMsi;Encrypt=yes;

The object ID in the above connection has to replaced with the Client Id of your User Managed Identity. Please refer the document Connection String samples and find this format outlined in sample 8.

I have tested the User Managed Identity connectivity using the approach specified in HTTP trigger, get multiple rows. Following is my Python App code

import azure.functions as func
import json
import logging

app = func.FunctionApp(http_auth_level=func.AuthLevel.FUNCTION)

@app.route(route="http_trigger_sql")
@app.sql_input(arg_name="todo",
               command_text="select [Id], Name from dbo.Test",
               command_type="Text",
               connection_string_setting="SqlConnectionString",
               binding_name="todo")
def http_trigger_sql(req: func.HttpRequest, todo: func.SqlRowList) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    try:
        rows = list(map(lambda r: json.loads(r.to_json()), todo))
        return func.HttpResponse(
            json.dumps(rows),
            status_code=200,
            mimetype="application/json"
        )
    except Exception as e:
        logging.error(f"Error processing SQL input: {e}")
        return func.HttpResponse(
            "Error processing request",
            status_code=500
        )

In your local development, you need create a function.json file with the following bindings set in it.

{
  "bindings": [
    {
      "type": "httpTrigger",
      "authLevel": "function",
      "direction": "in",
      "name": "req",
      "route": "http_trigger_sql"
    },
    {
      "type": "sql",
      "name": "todo",
      "commandText": "select [Id], Name from dbo.Test",
      "connectionStringSetting": "SqlConnectionString",
      "commandType": "Text",
      "direction": "in"
    },
    {
      "type": "http",
      "direction": "out",
      "name": "$return"
    }
  ]
}

You would also need to create an _init_.py file with the following code to provide the SQL row object, named as todo in my case, to the function app.

import logging
import azure.functions as func

def main(req: func.HttpRequest, todo: func.SqlRow) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
    # ...existing code...
    return func.HttpResponse(f"Todo item: {todo}")

Once the code is deployed to the Function app, create an environment variable with a name SqlConnectionString as this is configured to hold the connection string in the function. json file, and provide the connection string in the above suggested format. Please refer the following image for reference

Please make sure that you have also added User Managed Identity, which you are trying to authenticate to the SQL DB, to your function app. You can do this by navigating to the Settings --> Identity --> User Managed Identity --> Add option from the Function app.

Once all the configurations are in place, you should be able to hit the function app URL and get the results from the DB. Please refer the following invocation log and returned result from the Function app

Hope this helps! Please let us know if you run into any additional issues or need further assistance.

---

If the response helped, please do click Accept Answer and Yes for the answer provided. Doing so would help other community members with similar issue identify the solution. I highly appreciate your contribution to the community.

Answer 2

Hello,

Thank you for your feedback.

I used the suggested connection string, but I encountered the same error as before:

Could you assist me in resolving this issue?