sql - 部署到 Azure 应用服务时，ASP.NET Core Razor Pages Web 应用程序中的 Active Directory 交互式身份验证不起作用

Question

我有一个 Web 应用程序，用于与 Azure SQL 数据库交互。它按预期工作，但是当我从在连接字符串中使用 SQL 用户/密码更改为使用 Active Directory Interactive 时，当 Web 应用程序应提示登录时，我会收到错误消息。

请注意，只有当应用程序作为 Azure 应用服务发布时才会发生这种情况，它在本地按预期工作。

我缺乏经验，所以很难理解这个错误。

下面是我的连接字符串。

"Connection1": "Server=xxx.database.windows.net; Initial Catalog=xxx; Authentication=Active Directory Interactive;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

这是当用户在开发模式下加载新页面时使用连接字符串时收到的错误。

说实话，我对此感到非常不知所措。这似乎是某种机器配置问题，我不知道从哪里开始。

任何建议表示赞赏!堆栈如下:

System.AggregateException: One or more errors occurred. (One or more errors occurred. (An HttpListenerException occurred while listening on http://localhost:56718/ for the system browser to complete the login. Possible cause and mitigation: the app is unable to listen on the specified URL; run 'netsh http add iplisten 127.0.0.1' from the Admin command prompt.))
 ---> System.AggregateException: One or more errors occurred. (An HttpListenerException occurred while listening on http://localhost:56718/ for the system browser to complete the login. Possible cause and mitigation: the app is unable to listen on the specified URL; run 'netsh http add iplisten 127.0.0.1' from the Admin command prompt.)
 ---> MSAL.NetCore.4.39.0.0.MsalClientException: 
    ErrorCode: http_listener_error
Microsoft.Identity.Client.MsalClientException: An HttpListenerException occurred while listening on http://localhost:56718/ for the system browser to complete the login. Possible cause and mitigation: the app is unable to listen on the specified URL; run 'netsh http add iplisten 127.0.0.1' from the Admin command prompt.
 ---> System.Net.HttpListenerException (5): Access is denied.
   at System.Net.HttpListener.SetupV2Config()
   at System.Net.HttpListener.Start()
   at Microsoft.Identity.Client.Platforms.Shared.DefaultOSBrowser.HttpListenerInterceptor.ListenToSingleRequestAndRespondAsync(Int32 port, String path, Func`2 responseProducer, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.Identity.Client.Platforms.Shared.DefaultOSBrowser.HttpListenerInterceptor.ListenToSingleRequestAndRespondAsync(Int32 port, String path, Func`2 responseProducer, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Platforms.Shared.Desktop.OsBrowser.DefaultOsBrowserWebUi.InterceptAuthorizationUriAsync(Uri authorizationUri, Uri redirectUri, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Platforms.Shared.Desktop.OsBrowser.DefaultOsBrowserWebUi.AcquireAuthorizationAsync(Uri authorizationUri, Uri redirectUri, RequestContext requestContext, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.AuthCodeRequestComponent.FetchAuthCodeAndPkceInternalAsync(IWebUI webUi, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.AuthCodeRequestComponent.FetchAuthCodeAndPkceVerifierAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.Requests.InteractiveRequest.GetTokenResponseAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.Requests.InteractiveRequest.ExecuteAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.Requests.RequestBase.RunAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.ApiConfig.Executors.PublicClientExecutor.ExecuteAsync(AcquireTokenCommonParameters commonParameters, AcquireTokenInteractiveParameters interactiveParameters, CancellationToken cancellationToken)
   at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenInteractiveDeviceFlowAsync(IPublicClientApplication app, String[] scopes, Guid connectionId, String userId, SqlAuthenticationMethod authenticationMethod)
   at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.<>c__DisplayClass17_0.<<AcquireTokenAsync>b__0>d.MoveNext()
Inner Exception: System.Net.HttpListenerException (5): Access is denied.
   at System.Net.HttpListener.SetupV2Config()
   at System.Net.HttpListener.Start()
   at Microsoft.Identity.Client.Platforms.Shared.DefaultOSBrowser.HttpListenerInterceptor.ListenToSingleRequestAndRespondAsync(Int32 port, String path, Func`2 responseProducer, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
   at System.Threading.Tasks.Task`1.get_Result()
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass146_1.<GetFedAuthToken>b__1()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__271_0(Object obj)
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
   --- End of inner exception stack trace ---
   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at xxx.Pages.xxx.IndexModel.OnGetAsync() in C:\Users\xxx\source\repos\xxx\xxx\Pages\xxx\Index.cshtml.cs:line 37
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Convert[T](Object taskAsObject)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Execute(Object receiver, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

MSAL.NetCore.4.39.0.0.MsalClientException: 
    ErrorCode: http_listener_error
Microsoft.Identity.Client.MsalClientException: An HttpListenerException occurred while listening on http://localhost:56718/ for the system browser to complete the login. Possible cause and mitigation: the app is unable to listen on the specified URL; run 'netsh http add iplisten 127.0.0.1' from the Admin command prompt.
 ---> System.Net.HttpListenerException (5): Access is denied.
   at System.Net.HttpListener.SetupV2Config()
   at System.Net.HttpListener.Start()
   at Microsoft.Identity.Client.Platforms.Shared.DefaultOSBrowser.HttpListenerInterceptor.ListenToSingleRequestAndRespondAsync(Int32 port, String path, Func`2 responseProducer, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.Identity.Client.Platforms.Shared.DefaultOSBrowser.HttpListenerInterceptor.ListenToSingleRequestAndRespondAsync(Int32 port, String path, Func`2 responseProducer, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Platforms.Shared.Desktop.OsBrowser.DefaultOsBrowserWebUi.InterceptAuthorizationUriAsync(Uri authorizationUri, Uri redirectUri, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Platforms.Shared.Desktop.OsBrowser.DefaultOsBrowserWebUi.AcquireAuthorizationAsync(Uri authorizationUri, Uri redirectUri, RequestContext requestContext, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.AuthCodeRequestComponent.FetchAuthCodeAndPkceInternalAsync(IWebUI webUi, CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.AuthCodeRequestComponent.FetchAuthCodeAndPkceVerifierAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.Requests.InteractiveRequest.GetTokenResponseAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.Requests.InteractiveRequest.ExecuteAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.Internal.Requests.RequestBase.RunAsync(CancellationToken cancellationToken)
   at Microsoft.Identity.Client.ApiConfig.Executors.PublicClientExecutor.ExecuteAsync(AcquireTokenCommonParameters commonParameters, AcquireTokenInteractiveParameters interactiveParameters, CancellationToken cancellationToken)
   at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenInteractiveDeviceFlowAsync(IPublicClientApplication app, String[] scopes, Guid connectionId, String userId, SqlAuthenticationMethod authenticationMethod)
   at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.<>c__DisplayClass17_0.<<AcquireTokenAsync>b__0>d.MoveNext()
Inner Exception: System.Net.HttpListenerException (5): Access is denied.
   at System.Net.HttpListener.SetupV2Config()
   at System.Net.HttpListener.Start()
   at Microsoft.Identity.Client.Platforms.Shared.DefaultOSBrowser.HttpListenerInterceptor.ListenToSingleRequestAndRespondAsync(Int32 port, String path, Func`2 responseProducer, CancellationToken cancellationToken)

Answer 1

我最终让事情开始工作。在部署的连接字符串的应用程序设置中将其设置为

Authentication=Active Directory Managed Identity;User Id='your azure object/principal id'

确保用户 ID 是 Azure 对象/主体 ID，而不是 Azure 客户端 ID

我创建了一个帮助程序，因此我不必直接在连接字符串中设置 userId，但在部署到 azure 时它会作为环境变量被选取。您可以从配置中获取连接字符串，在我的例子中，我有一个租户服务来获取连接字符串，因为我的网站是 Multi-Tenancy

public static SqlConnection Create(ITenantService tenantService, IConfiguration configuration)
{
    var tenantConnectionString = tenantService.GetConnectionString();
    var connectionStringBuilder = new SqlConnectionStringBuilder(tenantConnectionString);
    if (string.IsNullOrEmpty(connectionStringBuilder.UserID))
    {
        var clientId = configuration.GetValue<string>("AZURE_OBJECT_ID");
        if (clientId != null) connectionStringBuilder.UserID = clientId;
    }

    return new SqlConnection(connectionStringBuilder.ConnectionString);
}

然后就在你的启动中使用

 optionsBuilder.UseSqlServer(MyUtility.Create(TenantService, Configuration));