解决SqlException错误26：ASP.NET Core连接SQL Server失败

场景说明 #

搭建了新的ASP.NET Core Web应用并连接到本地运行的SQL Server数据库，通过程序包管理器控制台执行add migrations和update-database操作均正常，在Visual Studio 2022和SQL Server Management Studio（SSMS）中也能正常查看、查询数据库表。

但运行应用查询数据库数据时，持续抛出以下SqlException异常：

SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found or was not accessible. 
Verify that the instance name is correct and that SQL Server is configured to allow remote connections. 
(provider: TCP Provider, error: 26 - Error Locating Server/Instance Specified)

Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection)
Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(bool errorsExpected, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(bool errorsExpected, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, bool errorsExpected)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable<TResult>+ConfiguredValueTaskAwaiter.GetResult()

在另一个项目的Swagger中执行操作也会出现相同错误。

已尝试的排查操作 #

• 添加了入站连接的防火墙规则（TCP1433和UDP1434）
• 通过services.msc确认SQL Server（sqlexpress）服务正在运行
• 通过services.msc确认SQL Server Browser（sqlexpress）服务正在运行
• 尝试了多种连接字符串
• 重新安装了SQL Server
• 启用了TCP/IP协议
• 将IP1的TCP端口修改为1433
• 将IPAll的TCP端口修改为1433
• 多次重启SQL Server服务及电脑

连接字符串测试情况 #

程序包管理器控制台可用但Web应用运行时失效的连接字符串 #

• options.UseSqlServer("Server=LAPTOP-LIDY\\SQLEXPRESS;Database=recruitmentdb;Trusted_Connection=True;Trust Server Certificate=True;");
• options.UseSqlServer("Server=localhost\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");

Web应用和程序包管理器控制台均报错的连接字符串 #

• options.UseSqlServer("Server=host.docker.internal\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");
• options.UseSqlServer("Server=172.18.0.1\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");
• options.UseSqlServer("Server=172.17.0.1\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");

相关代码片段 #

控制器代码 #

public class StudentsController : Controller
{
    private readonly Schoolcontext _context;

    public StudentsController(Schoolcontext context)
    {
        _context = context;
    }

    // GET: Students
    public async Task<IActionResult> Index()
    {
        //var checkvar = await _context.Students.ToListAsync();
        //return View(await _context.Students.ToListAsync());
        var test = "tested with breakpoint";
        var firstItem = await _context.Students.FirstOrDefaultAsync();
        return View(firstItem);
    }
}

Program.cs代码 #

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<Schoolcontext>(options =>
    {    
        options.UseSqlServer("Server=localhost\\SQLEXPRESS01;Database=recruitmentDbNew;Trusted_Connection=True;Trust Server Certificate=True;");
    });

已通过Update-Database验证多种连接字符串的可用性，推测遗漏了某个简单配置。

修复方案 #

为程序%ProgramFiles%\Microsoft SQL Server\MSSQL16.SQLEXPRESS01\MSSQL\Binn\sqlservr.exe创建新的防火墙允许规则，同时删除Docker容器后，问题得以解决。

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