ASP.NET Core 8 Web API（Windows身份验证）连接SQL Server遇异常

问题背景 #

原本在.NET 6环境下，带Windows身份验证的Web API连接SQL Server完全正常，迁移至.NET 8后出现两个矛盾问题：

• 添加app.UseAuthentication();并在控制器加[Authorize]注解时，运行时反复弹出Windows凭据窗口，无法完成授权
• 移除上述身份验证代码后，DAO层返回空连接，无法正常查询数据库

已通过依赖注入注册IDBFactory为Transient生命周期，相关代码如下：

原DBFactory代码 #

public SqlConnection Get()
{
    #if RELEASE
    var theConnectionString = "Data Source=serverName; Initial Catalog=dbName; Integrated Security=true;";
    #else
    var theConnectionString = "Data Source=serverName; Initial Catalog=dbName; Integrated Security=true;";
    #endif

    return new SqlConnection(theConnectionString);
}

原DAO代码 #

private readonly IDBFactory _factory;

public LocationsDAO(IDBFactory factory)
{
    _factory = factory;
}

public List<LocationData> GetAllLocations()
{
    using SqlConnection connection = _factory.Get();

    return connection.Query<LocationData>(SQLQueries.GetAllLocations).ToList();
}

原API控制器代码 #

[ApiController]
[Route("api/[controller]/")]
public class LocationsController : ControllerBase
{
    private readonly ILocationsDAO _locationDAO;
    private readonly IHttpContextAccessor httpC;

    public LocationsController(ILocationsDAO locationDAO, IHttpContextAccessor _httpC)
    {
        _locationDAO = locationDAO;
        httpC = _httpC;
    }

    [HttpGet]
    public List<LocationData> GetAllLocations()
    {
        var locations = _locationDAO.GetAllLocations();
        return locations;
    }
}

解决方案 #

1. 修正中间件配置顺序 #

.NET 8对中间件顺序要求更严格，必须保证身份验证中间件在授权中间件之前，且两者都要放在路由映射之前。修改Program.cs配置：

var builder = WebApplication.CreateBuilder(args);

// 注册服务
builder.Services.AddAuthentication(NegotiateDefaults.AuthenticationScheme)
    .AddNegotiate();
builder.Services.AddAuthorization();
builder.Services.AddHttpContextAccessor();
builder.Services.AddTransient<IDBFactory, DBFactory>();
builder.Services.AddTransient<ILocationsDAO, LocationsDAO>();

var app = builder.Build();

// 中间件顺序不能错
app.UseAuthentication();
app.UseAuthorization();

app.MapControllers();

app.Run();

2. 完善数据库连接逻辑 #

原DAO代码未打开数据库连接，这是返回空结果的核心原因；同时统一连接字符串配置，确保身份传递：

// 修正DBFactory
public SqlConnection Get()
{
    // 环境区分可保留，但连接字符串一致时可简化
    var theConnectionString = "Data Source=serverName; Initial Catalog=dbName; Trusted_Connection=True;";
    return new SqlConnection(theConnectionString);
}

// 修正DAO
public List<LocationData> GetAllLocations()
{
    using SqlConnection connection = _factory.Get();
    connection.Open(); // 必须显式打开连接
    return connection.Query<LocationData>(SQLQueries.GetAllLocations).ToList();
}

3. 配置控制器授权 #

在控制器上添加[Authorize]注解，确保只有通过Windows身份验证的用户可访问：

[ApiController]
[Route("api/[controller]/")]
[Authorize]
public class LocationsController : ControllerBase
{
    // 原有代码不变
}

4. 服务器与权限配置 #

• 若部署在IIS：启用站点的Windows身份验证，禁用匿名验证；应用池身份设置为ApplicationPoolIdentity或有权限访问SQL Server的域账户
• 若使用Kestrel：确保运行程序的账户拥有SQL Server登录权限，且数据库已映射对应用户权限

内容的提问来源于stack exchange，提问作者Кристиан Спасов