.NET8独立模式Azure Function配置EF Core+Entra认证及令牌续期问题

方向1：使用连接字符串自动处理认证（推荐） #

直接通过Authentication=Active Directory Service Principal连接字符串配置，无需手动管理令牌，SqlClient会自动负责令牌的获取与续期，彻底解决1小时失效问题。

正确连接字符串格式 #

Server={server};Database={database};Authentication=Active Directory Service Principal;User Id={clientId};Password={clientSecret};Tenant Id={tenantId};

• User Id：Entra应用的Client ID
• Password：Entra应用的Client Secret
• Tenant Id：Azure AD租户ID

重构代码 #

1. Program.cs：注入带完整连接字符串的DbContext

var connectionString = $"Server={Environment.GetEnvironmentVariable("DataverseSettings:SqlServer")};" +
                       $"Database={Environment.GetEnvironmentVariable("DataverseSettings:SqlDatabase")};" +
                       $"Authentication=Active Directory Service Principal;" +
                       $"User Id={Environment.GetEnvironmentVariable("DataverseSettings:ClientId")};" +
                       $"Password={Environment.GetEnvironmentVariable("DataverseSettings:ClientSecret")};" +
                       $"Tenant Id={Environment.GetEnvironmentVariable("DataverseSettings:TenantId")};";

services.AddDbContext<DvDbContext>(options =>
{
    options.UseSqlServer(connectionString, providerOptions => providerOptions.EnableRetryOnFailure());
});

2. DbContext.cs：移除手动令牌管理逻辑，简化代码

using MasterdataAPI.Configurations;
using Microsoft.EntityFrameworkCore;
using MasterdataAPI.Models.DataverseTableModels;

namespace MasterdataAPI.EFCore
{
    public class DvDbContext : DbContext
    {
        public DbSet<QuoteSqlTableRowModel> Quote { get; set; }

        public DvDbContext(DbContextOptions<DvDbContext> options) : base(options)
        {
        }

        // 保留原有OnModelCreating逻辑（若需）
    }
}

方向2：修复现有代码的令牌续期逻辑 #

若必须保留手动令牌管理，核心问题是DbContext的OnConfiguring方法仅执行一次，导致SqlConnection的AccessToken无法更新。需改用DbConnectionInterceptor在每次打开连接时动态设置最新令牌。

步骤1：创建连接拦截器 #

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Options;
using Microsoft.Identity.Client;

namespace MasterdataAPI.EFCore
{
    public class DataverseTokenInterceptor : DbConnectionInterceptor
    {
        private readonly IMemoryCache _cache;
        private readonly DataverseSettings _dataverseSettings;

        public DataverseTokenInterceptor(IMemoryCache cache, IOptions<DataverseSettings> dataverseSettings)
        {
            _cache = cache;
            _dataverseSettings = dataverseSettings.Value;
        }

        public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
        {
            if (connection is SqlConnection sqlConn)
            {
                sqlConn.AccessToken = await GetTokenAsync(cancellationToken);
            }
            return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
        }

        public override InterceptionResult ConnectionOpening(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
        {
            if (connection is SqlConnection sqlConn)
            {
                sqlConn.AccessToken = GetTokenAsync(CancellationToken.None).GetAwaiter().GetResult();
            }
            return base.ConnectionOpening(connection, eventData, result);
        }

        private async Task<string> GetTokenAsync(CancellationToken cancellationToken)
        {
            if (!_cache.TryGetValue("dataverseToken", out string? token))
            {
                var authResult = await AuthenticateAsync(cancellationToken);
                token = authResult.AccessToken;
                // 提前5分钟过期，避免令牌在请求过程中失效
                var expirationOffset = authResult.ExpiresOn - DateTimeOffset.UtcNow - TimeSpan.FromMinutes(5);
                _cache.Set("dataverseToken", token, expirationOffset);
            }
            return token ?? throw new InvalidOperationException("Failed to acquire access token");
        }

        private async Task<AuthenticationResult> AuthenticateAsync(CancellationToken cancellationToken)
        {
            var app = ConfidentialClientApplicationBuilder.Create(_dataverseSettings.ClientId)
                .WithAuthority($"{_dataverseSettings.AuthenticationUrl}/{_dataverseSettings.TenantId}")
                .WithClientSecret(_dataverseSettings.ClientSecret)
                .Build();

            return await app.AcquireTokenForClient(
                    new[] { $"{_dataverseSettings.Audience}/.default" })
                .ExecuteAsync(cancellationToken)
                .ConfigureAwait(false);
        }
    }
}

步骤2：注册拦截器并修改DbContext #

1. Program.cs：注册拦截器并配置DbContext

services.AddScoped<DataverseTokenInterceptor>();

services.AddDbContext<DvDbContext>((sp, options) =>
{
    var connectionString = $"Server={Environment.GetEnvironmentVariable("DataverseSettings:SqlServer")};" +
                           $"Database={Environment.GetEnvironmentVariable("DataverseSettings:SqlDatabase")};";

    options.UseSqlServer(connectionString, providerOptions => providerOptions.EnableRetryOnFailure())
           .AddInterceptors(sp.GetRequiredService<DataverseTokenInterceptor>());
});

2. DbContext.cs：移除OnConfiguring中的手动连接创建逻辑

using MasterdataAPI.Configurations;
using Microsoft.EntityFrameworkCore;
using MasterdataAPI.Models.DataverseTableModels;

namespace MasterdataAPI.EFCore
{
    public class DvDbContext : DbContext
    {
        public DbSet<QuoteSqlTableRowModel> Quote { get; set; }

        public DvDbContext(DbContextOptions<DvDbContext> options) : base(options)
        {
        }

        // 保留原有OnModelCreating逻辑（若需）
    }
}

关键修复点 #

• 通过拦截器在每次打开连接时更新AccessToken，确保请求使用最新令牌
• 缓存令牌时提前5分钟过期，避免令牌在请求执行过程中失效
• 移除同步调用Result，改用异步方法避免死锁风险

内容的提问来源于stack exchange，提问作者swaglord mcmuffin'