.NET 8 API连接本地SQL Server时出现Internal connection fatal error

问题描述 #

多个.NET 8项目连接本地SQL Server 2019，Winforms、MAUI项目均能正常连接，但API项目抛出System.InvalidOperationException: 'Internal connection fatal error.'。
最初使用Trusted_Connection=True方式连接，尝试改为和Winforms/MAUI相同的账号密码方式后仍报错。已确认连接字符串传递正确，且所有项目调用同一个SQLUtility类。

相关代码 #

appsettings.json #

{
    "Settings": {
        "devconn-local": "Server =.\\MSSQLSERVER01;Database=myDatabase;Trusted_Connection=True"
    }
}

API项目Program.cs #

#if DEBUG
    settingname = "Settings:devconn-local";
#endif

string? connstring = builder.Configuration[settingname];

if (connstring == null)
{
    throw new Exception(settingname + " connection string not found");
}

SQLUtility.SetConnectionString(connstring, true);

SQLUtility类 #

public static void SetConnectionString(string connstring, bool tryopen, string userid = "", string password = "")
{
    ConnectionString = connstring;

    if (userid != "")
    {
        SqlConnectionStringBuilder b = new();
        b.ConnectionString = ConnectionString;
        b.UserID = userid;
        b.Password = password;
        ConnectionString = b.ConnectionString;
    }

    if (tryopen)
    {
        using (SqlConnection conn = new(ConnectionString))
        {
            conn.Open(); // 异常抛出位置
        }
    }
}

完整异常信息 #

System.InvalidOperationException
HResult=0x80131509
Message=Internal connection fatal error.
Source=System.Data.SqlClient
StackTrace:
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.DrainData(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ThrowUnsupportedCollationEncountered(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.GetCodePage(SqlCollation collation, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TryProcessEnvChange(Int32 tokenLength, TdsParserStateObject stateObj, SqlEnvChange& sqlEnvChange)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.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)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at SQLUtility.SetConnectionString(String connstring, Boolean tryopen, String userid, String password) in C:\Users\user\repos\Framework\SQLUtility.cs:line 27
at Program.$(String[] args) in C:\Users\user\repos\myrepo\Software\API\Program.cs:line 42

排查与解决建议 #

从堆栈跟踪中的ThrowUnsupportedCollationEncountered可以确定，核心问题是排序规则/代码页不兼容，结合API与桌面项目的运行环境差异，按以下步骤处理：

1. 更换为Microsoft.Data.SqlClient库
   当前使用的System.Data.SqlClient是旧版组件，.NET 8中推荐使用Microsoft.Data.SqlClient（通过NuGet安装），它对新环境的兼容性更好，能有效避免排序规则相关的内部连接错误。只需将SQLUtility类中的System.Data.SqlClient引用替换为Microsoft.Data.SqlClient即可。

2. 在连接字符串中强制指定兼容参数
   若暂不想更换库，可在连接字符串中添加以下参数：

   Server=.\\MSSQLSERVER01;Database=myDatabase;Trusted_Connection=True;Collation=SQL_Latin1_General_CP1_CI_AS;Encrypt=False;TrustServerCertificate=True
   

   • Collation值替换为你的数据库实际使用的排序规则
   • Encrypt=False和TrustServerCertificate=True可规避因SSL加密触发的兼容性问题

3. 检查API项目的运行身份
   桌面项目以当前Windows用户身份运行，而API项目（如IIS Express启动）默认使用IIS应用池身份，该身份的区域设置/代码页可能与桌面用户不一致：

   • 右键API项目→属性→调试→勾选"使用特定用户"，输入你的Windows账号密码，以当前用户身份启动API
   • 或在SQL Server中给IIS应用池身份（如IIS APPPOOL\你的API应用池名称）添加数据库访问权限，用可信连接测试

4. 验证数据库与实例的排序规则一致性
   在SSMS中执行以下查询，检查数据库与SQL Server实例的排序规则是否匹配：

   -- 查看数据库排序规则
   SELECT name, collation_name FROM sys.databases WHERE name = 'myDatabase';
   -- 查看实例排序规则
   SELECT SERVERPROPERTY('Collation') AS ServerCollation;
   

   若两者不一致，可考虑修改数据库排序规则，或确保客户端代码兼容差异。

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