ASP.NET Core Web API连接SQL Server报错：远程主机强制关闭连接

错误信息 #

System.InvalidOperationException: 引发了一个可能由暂时性故障导致的异常。请考虑在UseSqlServer调用中添加'EnableRetryOnFailure'以启用暂时性错误恢复。

Microsoft.Data.SqlClient.SqlException (0x80131904): 已成功与服务器建立连接，但在登录过程中发生错误。(provider: TCP Provider, error: 0 - 远程主机强制关闭了一个现有的连接。)

System.ComponentModel.Win32Exception (10054): 远程主机强制关闭了一个现有的连接。

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.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) ClientConnectionId:ce87e397-45ed-4c7e-8f2f-64d7dca58bd6 Error Number:10054,State:0,Class:20 --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at Project2_Web_API.Controllers.CustomersController.GetCustomers() in C:\Users\Cherish\Documents\CMPG\CMPG 3year\2nd semester\Cmpg 323\Project 2\Project2-Web-API\Controllers\CustomersController.cs:line 31
at lambda_method5(Closure , Object )
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

解决步骤 #

1. 启用EF Core暂时性错误重试机制 #

按照错误提示，在UseSqlServer配置中添加重试逻辑，针对10054这类连接错误自动重试：

// 在Program.cs或Startup.cs的DbContext配置段中
builder.Services.AddDbContext<YourDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("YourConnectionString"),
        retryOptions => retryOptions
            .EnableRetryOnFailure(
                maxRetryCount: 5, // 最大重试次数
                maxRetryDelay: TimeSpan.FromSeconds(30), // 单次重试最大延迟
                errorNumbersToAdd: new[] { 10054 } // 指定需要重试的错误码
            )
    ));

2. 验证SQL Server远程连接配置 #

• 打开SQL Server配置管理器，确认SQL Server网络配置下的TCP/IP协议已启用
• 检查TCP/IP属性，确认监听端口为默认1433（或自定义端口），IP地址设置正确
• 确认服务器端防火墙已开放对应端口，可通过telnet 服务器地址 端口测试连通性

3. 检查登录凭证与权限 #

• 核对连接字符串中的服务器地址、认证方式（Windows/SQL认证）、用户名/密码（SQL认证场景）是否完全正确
• 若用SQL认证，确保SQL Server已启用混合认证模式，且账号未被锁定、拥有目标数据库的访问权限
• 查看SQL Server错误日志（SSMS中管理->SQL Server日志），获取登录失败的具体原因

4. 调整连接池参数 #

在连接字符串中添加连接池优化参数，避免连接耗尽或超时：

Server=你的服务器地址;Database=目标数据库;User Id=账号;Password=密码;Max Pool Size=100;Min Pool Size=5;Connection Timeout=30;

• Max Pool Size：连接池允许的最大连接数
• Min Pool Size：连接池保持的最小空闲连接数
• Connection Timeout：延长连接超时时间，适配网络延迟场景

5. 确保连接正确释放 #

检查代码中DbContext的使用，用using语句自动释放连接，避免连接池资源耗尽：

using (var context = new YourDbContext())
{
    var customers = await context.Customers.ToListAsync();
    // 业务逻辑处理
}

内容的提问来源于stack exchange，提问作者CM Ngokana