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

sql.md

"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

1. Create Azure SQLServer and database, only allow AAD auth
2. Set AAD admin, preferably a group account and add whoever needs admin access
3. Set firewall rules
4. Test connection with e.g. SSMS using AAD - universal with mfa login
5. Create a table ('test' is used here)
6. Create function app, http triggered VS 2022, copy code from here
7. Create Azure function app, set system assigned identity
8. 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)

Sources, links and additional reading

• https://docs.microsoft.com/en-us/azure/architecture/framework/security/security-principles
• https://docs.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet
• https://devblogs.microsoft.com/azure-sdk/azure-identity-with-sql-graph-ef/
• https://www.techwatching.dev/posts/sqlclient-active-directory-authent
• https://weblog.west-wind.com/posts/2021/Dec/07/Connection-Failures-with-MicrosoftDataSqlClient-4-and-later
• https://blog.novanet.no/passwordless-connectionstring-to-azure-sql-database-using-managed-identity/