实体框架核心使用托管标识连接Azure SQL时出现“Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON”的错误。

Question

我们已经按照 此教程 启用了托管标识，以连接到Azure上的Identity Server数据库，但在一段时间后（大约7小时）出现以下错误。

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnection
PoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at IdentityServer4.EntityFramework.Stores.ResourceStore.GetAllResourcesAsync()
   at IdentityServer4.Stores.IResourceStoreExtensions.GetAllEnabledResourcesAsync(IResourceStore store)
   at IdentityServer4.ResponseHandling.DiscoveryResponseGenerator.CreateDiscoveryDocumentAsync(String baseUrl, String issuerUri)
   at IdentityServer4.Endpoints.DiscoveryEndpoint.ProcessAsync(HttpContext context)
   at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)
   at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)
 at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

连接字符串: 数据源=tcp:{sqlServerUrl},1433;初始目录={databaseName};

var identityServer = services.AddIdentityServer(options =>
    {
        options.Events.RaiseErrorEvents = true;
        options.Events.RaiseInformationEvents = true;
        options.Events.RaiseFailureEvents = true;
        options.Events.RaiseSuccessEvents = true;
    })

    // this adds the config data from DB (clients, resources, CORS)
    .AddConfigurationStore(configureStoreOptions =>
    {
        // uses the MSI to connect to SQL.
        SqlConnection conn = new SqlConnection(connectionString);
        conn.AccessToken = (new AzureServiceTokenProvider()).
            GetAccessTokenAsync("https://database.windows.net/").Result;

        configureStoreOptions.ConfigureDbContext = builder =>
            builder.UseSqlServer(
                conn,
                sql => sql.MigrationsAssembly(migrationsAssembly));
    })
)

- Luuk

---

你的意思是一开始它运行得很好，但过了一会儿就出现了错误？ - Joy Wang

是的，在几个小时后，例如在“/.well-known/openid-configuration”端点上返回此错误。 - Luuk

服务器在空闲或长时间无活动后会关闭连接。 - jdweng

连接字符串没问题，重启后可以正常工作，但几个小时后就会出现错误。 - Luuk

@Luuk，你能否更新一下你的问题，使其更易于搜索：我建议标题为：“使用托管标识连接Azure SQL的Entity Framework Core在一段时间后会出现用户“NT AUTHORITY\ANONYMOUS LOGON”的登录失败”，并添加标签：“sql-server，azure，asp.net-core，identityserver4，entity-framework-core”。但由于我没有2K声望，并且该问题包含太多代码，系统阻止了我的编辑。 - d_f

Answer 1

你的实现只创建了一个连接，而令牌在一个小时后过期。

一种解决方案是将连接初始化器移动到Configure函数中：

.AddConfigurationStore(configureStoreOptions =>
{
    configureStoreOptions.ConfigureDbContext = builder =>
    {
      // uses the MSI to connect to SQL.
      SqlConnection conn = new SqlConnection(connectionString);
      conn.AccessToken = (new AzureServiceTokenProvider()).
          GetAccessTokenAsync("https://database.windows.net/").Result;
      builder.UseSqlServer(
            conn,
            sql => sql.MigrationsAssembly(migrationsAssembly));
    }
})

- d_f