Azure function using Azure Active Directory managed identity to authenticate with Azure SQLServer in Azure and working transparently locally. Using Dapper as ORM.

"Your account control strategy should rely on identity systems for controlling access rather than relying on network controls or direct use of cryptographic keys" - Microsoft Well Architected framework Security pillar

Context and steps

Create Azure SQLServer and database, only allow AAD auth
Set AAD admin, preferably a group account and add whoever needs admin access
Set firewall rules
Test connection with e.g. SSMS using AAD - universal with mfa login
Create a table ('test' is used here)
Create function app, http triggered VS 2022, copy code from here
Create Azure function app, set system assigned identity
Add func app managed identity (function app name) as db user + roles

Code

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Dapper;
using Microsoft.Data.SqlClient; // this and using the Auth attrib in conn str is where all the managed identity magic comes from
using Microsoft.Extensions.Logging;
using System;
using System.Threading.Tasks;

namespace SecMI
{
    public static class SecMI
    {
        [FunctionName("SQLServer")]
        public static async Task<IActionResult> SQLServer(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "sql")] HttpRequest req,
            ILogger log)
        {
            string sql = "SELECT * FROM test";
            using (var conn = new SqlConnection(Environment.GetEnvironmentVariable("DbConnStr")))
            {
                var result = await conn.QueryAsync<Data>(sql);
                return new OkObjectResult(result);
            }   
        }
    }
}

and the beauty of passwordless configs:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "DbConnStr": "Server=localhost;Database=test;Trusted_Connection=True;TrustServerCertificate=true;", // as of v4 of ms.data.sqlclient all conns are encrypted
    "_DbConnStr": "Server=<dbservername>.database.windows.net; Authentication=Active Directory Default; Database=test;"
  }
}

This setup allows for local func app querying azure db (using 2nd conn str) as well as local db (using 1st conn str)

ErikAndreas/sql.md

Context and steps

Code

Sources, links and additional reading