Isolated Azure Function Managed Identity for SQL Server Auth

Question

I have a .NET 5 Azure Function running with a FUNCTIONS_WORKER_RUNTIME config value of dotnet-isolated.

The function app needs to connect to an Azure SQL database using EF Core 5.0.6.

I followed guidance from this post for EF configuration.

My custom dbcontext is now:

public class SmsOrderContext : DbContext
{
    private readonly AzureServiceTokenProvider azureServiceTokenProvider;

    public SmsOrderContext(DbContextOptions<SmsOrderContext> options, AzureServiceTokenProvider azureServiceTokenProvider) : base(options)
    {
        RelationalDatabaseCreator databaseCreator =
                    (RelationalDatabaseCreator)this.Database.GetService<IDatabaseCreator>();
        databaseCreator.EnsureCreated();
        this.azureServiceTokenProvider = azureServiceTokenProvider;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        SqlConnection connection = new SqlConnection();
        string? envConString = Environment.GetEnvironmentVariable(ConfigConstants.SqlSvrConnString);
        connection.ConnectionString = envConString ?? "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=SmsRouter";
        if (azureServiceTokenProvider != null)
            connection.AccessToken = azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/").Result;
        optionsBuilder.UseSqlServer(connection);
    }
}

The condition for checking SqlSvrConnString environment variable is there so that I can run the app locally - where it uses localdb (this works fine) rather than Azure

In program.main I have:

.ConfigureServices(s =>
{
    s.AddSingleton<AzureServiceTokenProvider>(new AzureServiceTokenProvider());
    s.AddDbContext<SmsOrderContext>();
}

On my function app, the "Status" toggle for Identity\System assigned set to "On"

When I trigger the Azure function (from a http request), I see the following exception in Application Insights:

Failure Exception: Microsoft.Data.SqlClient.SqlException (0x80131904):
Login failed for user ''

I think this suggests the identity is not being passed to Sql Server? Can anyone see where I went wrong please?

Answer 1

If you want to use Azure MSI to access Azure SQL, please refer to the following steps

1. Enable MSI

2. Create Azure AD group

Connect-AzureAD
New-AzureADGroup -DisplayName "My new group" -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet"

3. Add the MSI as the group's member

Add-AzureADGroupMember -ObjectId "the id of the group" -RefObjectId "the id of the msi"

4. Set the group as SQL server Azure AD admin

5. Code

My DbContext

using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
using Microsoft.Azure.Services.AppAuthentication;
namespace httpfun{

   public class BloggingContext : DbContext
        {
           private readonly AzureServiceTokenProvider azureServiceTokenProvider;

    public BloggingContext(DbContextOptions<BloggingContext> options, AzureServiceTokenProvider azureServiceTokenProvider) : base(options)
    {
       
        this.azureServiceTokenProvider = azureServiceTokenProvider;
    }
            public DbSet<Blog> Blogs { get; set; }
             

        protected override void OnConfiguring(DbContextOptionsBuilder options)
          {
            SqlConnection connection= new SqlConnection();
            connection.ConnectionString="Server=tcp:<>database.windows.net,1433;Database=<>;";
            connection.AccessToken = azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/").Result;
            options.UseSqlServer(connection);
          }
         }

        public class Blog
        {
            public int BlogId { get; set; }
            public string Url { get; set; }

            
        }

}

My Program.cs

using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Azure.Functions.Worker.Configuration;
using Microsoft.Azure.Services.AppAuthentication;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;

namespace httpfun
{
    public class Program
    {
        public static void Main()
        {
            var host = new HostBuilder()
                .ConfigureFunctionsWorkerDefaults()
                .ConfigureServices(services=>{
                    services.AddSingleton<AzureServiceTokenProvider>(new AzureServiceTokenProvider());
                    services.AddDbContext<BloggingContext>();
                    
                })
                .Build();

            host.Run();
        }
    }
}

My function code(I use HTTP trigger for the test)

using System.Collections.Generic;
using System.Net;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Extensions.Logging;

namespace httpfun
{
    public  class HttpTrigger1
    {
        private   readonly  BloggingContext _context;

        public HttpTrigger1(BloggingContext context){
               this._context=context;
        }

        [Function("HttpTrigger1")]
        public  HttpResponseData Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req,
            FunctionContext executionContext)
        {
            var logger = executionContext.GetLogger("HttpTrigger1");
            logger.LogInformation("C# HTTP trigger function processed a request.");

            
                logger.LogInformation("Inserting a new blog");
                _context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                _context.SaveChanges();
            

            var response = req.CreateResponse(HttpStatusCode.OK);
            response.Headers.Add("Content-Type", "text/plain; charset=utf-8");

            response.WriteString("Welcome to Azure Functions!");

            return response;
        }
    }
}