.NET8+MSAL+SQL分布式缓存部署K8s时SQL连接错误排查

应用信息 #

• C# .NET 8、Dapper

使用场景 #

尝试将 MSAL 与 SQL 作为分布式缓存结合使用，本地运行正常，但部署到 Kubernetes 后抛出 SQL 连接错误。

问题现象 #

使用同一连接字符串时，Dapper 可正常从数据库获取数据，但 DistributedSession、SessionMiddleware 相关的分布式缓存连接失败，抛出以下错误：

错误栈 1 #

at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
     at Microsoft.Data.SqlClient.TdsParser.EnableSsl(UInt32 info, SqlConnectionEncryptOption encrypt, Boolean integratedSecurity, String serverCertificateFilename)
     at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert)
     at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
     at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
     at Microsoft.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, DbConnectionPool pool)
     at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
     at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
     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.Extensions.Caching.SqlServer.DatabaseOperations.GetCacheItemAsync(String key, Boolean includeValue, CancellationToken token)
     at Microsoft.Extensions.Caching.SqlServer.DatabaseOperations.RefreshCacheItemAsync(String key, CancellationToken token)
     at Microsoft.Extensions.Caching.SqlServer.SqlServerCache.RefreshAsync(String key, CancellationToken token)
     at Microsoft.AspNetCore.Session.DistributedSession.CommitAsync(CancellationToken cancellationToken)
     at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
  ClientConnectionId:f5888c74-0cab-48c8-a5b1-3d9f7ecc5ce1
  Error Number:-2146893019,State:0,Class:20

含证书验证异常的错误栈 #

fail: Microsoft.Identity.Web.TokenCacheProviders.Distributed.MsalDistributedTokenCacheAdapter[103]
  [MsIdWeb] DistributedCache: Write Connection issue. InRetry? False Error message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught) 
  Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
   ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
     at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions)
     at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](Boolean receiveFirst, Byte[] reAuthenticationData, CancellationToken cancellationToken)
     at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
     at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
     at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
     at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
     at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
     at Microsoft.Data.SqlClient.SqlConnection.InternalOpenAsync(CancellationToken cancellationToken)
  --- End of stack trace from previous location ---
     at Microsoft.Extensions.Caching.SqlServer.DatabaseOperations.SetCacheItemAsync(String key, Byte[] value, DistributedCacheEntryOptions options, CancellationToken token)
     at Microsoft.Extensions.Caching.SqlServer.SqlServerCache.SetAsync(String key, Byte[] value, DistributedCacheEntryOptions options, CancellationToken token)
     at Microsoft.Identity.Web.TokenCacheProviders.Utility.Measure(Task task)
     at Microsoft.Identity.Web.TokenCacheProviders.Distributed.MsalDistributedTokenCacheAdapter.L2OperationWithRetryOnFailureAsync(String operation, Func`2 cacheOperation, String cacheKey, Byte[] bytes, Boolean inRetry)
  ClientConnectionId:cf6f7680-cd59-47fd-9c80-a765527b0b30
  Error Number:-2146893019,State:0,Class:20

连接字符串（数据库位于VM内） #

xx.xxx.x.x,xxxx;Database=DBName;User Id=abc;Password=xyz

分布式缓存配置代码 #

builder.Services
    .AddLogging()
    .AddDistributedMemoryCache()
    .AddDistributedSqlServerCache(options =>
    {
        options.ConnectionString = config;
        options.SchemaName = "dbo";
        options.TableName = "TokenCache";
        options.DefaultSlidingExpiration = TimeSpan.FromHours(24);
    });

builder.Services.Configure<MsalDistributedTokenCacheAdapterOptions>(options =>
{
    // Optional: Disable the L1 cache in apps that don't use session affinity
    //                 by setting DisableL1Cache to 'true'.
    options.DisableL1Cache = false;

    // Or limit the memory (by default, this is 500 MB)
    options.L1CacheOptions.SizeLimit = 500; // 1 GB

    // You can choose if you encrypt or not encrypt the cache
    options.Encrypt = false;

    // And you can set eviction policies for the distributed
    // cache.
    options.SlidingExpiration = TimeSpan.FromHours(2);
});

排查方向与解决方案 #

核心原因 #

错误栈明确指向SSL证书验证失败，同一连接字符串在Dapper和分布式缓存中表现不同，根源是组件对SQL连接的默认配置差异：

• Dapper依赖的SQL客户端未强制启用SSL加密，或默认策略宽松
• Microsoft.Extensions.Caching.SqlServer使用的Microsoft.Data.SqlClient在.NET 8容器环境下，默认强制启用Encrypt=True，但Kubernetes环境无法验证数据库证书

解决步骤 #

1. 修改连接字符串，显式配置SSL参数
   根据环境选择合适的参数添加到连接字符串：

   • 自签名/不受信任证书场景：Encrypt=True;TrustServerCertificate=True
   • 内部可信网络无需加密：Encrypt=False
     示例修改后：

   xx.xxx.x.x,xxxx;Database=DBName;User Id=abc;Password=xyz;Encrypt=True;TrustServerCertificate=True
   

2. 检查Kubernetes网络与证书配置

   • 确认Pod到VM数据库的端口连通性，排查防火墙、NetworkPolicy限制
   • 若需信任合法证书，将数据库根证书挂载到容器证书目录（如/usr/local/share/ca-certificates/）并更新系统证书缓存

3. 统一连接配置
   确保AddDistributedSqlServerCache未额外覆盖连接加密设置，所有SQL连接组件使用一致的SSL策略

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