ASP.NET Core 6 Razor Pages应用Hangfire连接SQL Server失败问题

问题背景 #

• 现有ASP.NET Core 6 Razor Pages应用已正常配置业务逻辑数据库、身份认证数据库两个SQL Server实例，连接与运行均无异常
• 按照官方文档完成Hangfire推荐配置后，应用可正常启动，但访问Hangfire仪表盘时抛出数据库不存在错误，手动创建对应Hangfire数据库后报错没有消除
• 数据库部署在本地环境，日常使用Microsoft SQL Server Management Studio做数据库管理
• 此前调试确认必须在端点映射前调用app.UseSession()方法，否则会导致应用其他功能异常，暂时不确定该配置是否和本次连接错误有关
• 项目全局业务都在使用HttpContext服务，无法移除该配置，初步判断是未排查到的基础配置错误导致故障

相关配置 #

appsettings.json中Hangfire连接字符串配置 #

"HangfireConnection": "Server=(localdb)\\MSSQLocalDB;Database=Hangfire;Integrated Security=SSPI; Trusted_Connection=True"

Program.cs完整配置代码 #

using Microsoft.EntityFrameworkCore;
using RentARouter.Data;
using Microsoft.AspNetCore.Identity;
using RentARouter.Areas.Identity.Data;
using RentARouter.Services;
using Newtonsoft.Json.Converters;
using Hangfire;
using Hangfire.SqlServer;

var builder = WebApplication.CreateBuilder(args);

/*************************************************************
 * 身份认证数据库连接与配置
 *************************************************************/
var connectionString = builder.Configuration.GetConnectionString("IdentityDBContextConnection") ?? throw new InvalidOperationException("Connection string 'IdentityDBContextConnection' not found.");
builder.Services.AddDbContext<IdentityDBContext>(options =>
    options.UseSqlServer(connectionString));;
builder.Services.AddDefaultIdentity<RentARouterUser>(options =>
    options.SignIn.RequireConfirmedAccount = false)
    .AddRoles<IdentityRole>()
    .AddEntityFrameworkStores<IdentityDBContext>();

// 配置Cookie过期时间与默认跳转页
builder.Services.ConfigureApplicationCookie(options =>
{
    options.Cookie.HttpOnly = true;
    options.ExpireTimeSpan = TimeSpan.FromMinutes(20);
    options.LoginPath = "/Identity/Account/Login";
    options.AccessDeniedPath = "/Identity/Account/AccessDenied";
    options.SlidingExpiration = true;
});

/*************************************************************
 * 业务逻辑数据库连接配置
 *************************************************************/
builder.Services.AddDbContext<ApplicationDBContext>(options => options.UseSqlServer(
        builder.Configuration.GetConnectionString("DefaultConnection")
        ));
/*************************************************************
 * 注册基础服务
 *************************************************************/
builder.Services.AddRazorPages();
builder.Services.AddHttpContextAccessor();


/*************************************************************
 * 分布式缓存与Session服务配置
 *************************************************************/
builder.Services.AddDistributedMemoryCache();
builder.Services.AddSession(options =>
{
    options.IdleTimeout = TimeSpan.FromMinutes(20);
    options.Cookie.HttpOnly = true;
    options.Cookie.IsEssential = true;
});

builder.Services.AddTransient<CacheService>();

/*************************************************************
 * 注册事务日志服务
 *************************************************************/
builder.Services.AddTransient<TransactionService>();

/*************************************************************
 * Hangfire服务配置
 *************************************************************/
builder.Services.AddHangfire(configuration => configuration
.SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
.UseSimpleAssemblyNameTypeSerializer()
.UseRecommendedSerializerSettings()
.UseSqlServerStorage(builder.Configuration.GetConnectionString("HangfireConnection"), new SqlServerStorageOptions
{
    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
    QueuePollInterval = TimeSpan.Zero,
    UseRecommendedIsolationLevel = true,
    DisableGlobalLocks = true
}));
builder.Services.AddHangfireServer();

/*************************************************************
 * 构建Web应用实例
 *************************************************************/
var app = builder.Build();

/*************************************************************
 * 配置HTTP请求管道
 *************************************************************/
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error");
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthentication();;
app.UseAuthorization();
app.UseSession();

app.UseEndpoints(endpoints =>
{
    endpoints.MapControllers();
    endpoints.MapHangfireDashboard();
});

app.MapRazorPages();

app.Run();

报错信息 #

访问Hangfire仪表盘时抛出的核心错误提示：

SqlException: 建立与SQL Server的连接时发生网络相关或实例特定的错误。服务器未找到或无法访问。请验证实例名称是否正确，且SQL Server已配置为允许远程连接。(provider: SQL Network Interfaces, error: 50 - 发生Local Database Runtime错误。指定的LocalDB实例不存在。)

完整错误调用栈：

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: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. The specified LocalDB instance does not exist. )
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling)
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection)
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource<DbConnectionInternal> retry, DbConnectionOptions userOptions, out DbConnectionInternal connection)
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningObject, TaskCompletionSource<DbConnectionInternal> retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, out DbConnectionInternal connection)
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource<DbConnectionInternal> retry, DbConnectionOptions userOptions)
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource<DbConnectionInternal> retry)
System.Data.SqlClient.SqlConnection.Open()
Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection()
Hangfire.SqlServer.SqlServerStorage.UseConnection<T>(DbConnection dedicatedConnection, Func<DbConnection, T> func)
Hangfire.Dashboard.Pages.HomePage.Execute()
Hangfire.Dashboard.RazorPage.TransformText(string body)
Hangfire.Dashboard.RazorPageDispatcher.Dispatch(DashboardContext context)
Hangfire.Dashboard.AspNetCoreDashboardMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Builder.Extensions.UsePathBaseMiddleware.InvokeCore(HttpContext context, PathString matchedPath, PathString remainingPath)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

问题排查结论与解决方法 #

• 该故障和app.UseSession()配置、HttpContext服务注册没有关联，核心原因是连接字符串中配置的(localdb)\MSSQLocalDB为私有LocalDB实例，Hangfire服务运行时的进程上下文无法访问该私有实例，因此始终报实例不存在的错误，和是否手动创建Hangfire数据库无关。
• 修复方案：将连接字符串中使用的私有LocalDB实例替换为本地可访问的共享LocalDB实例，或直接指向本地正常运行的SQL Server实例地址即可。修改配置后Hangfire可自动创建所需表结构，仪表盘可正常访问。

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