EF DbContext连接远程SQL Server失败但SqlConnection连接正常

在Windows服务器上运行以下.NET 6.0代码，SQL Server数据库位于另一台服务器，MyDbContext由Entity Framework脚手架生成：

var connectionString = "Password=...;Persist Security Info=True;User ID=webuser;Initial Catalog=MyDB;Data Source=10.0.1.215;Encrypt=False;TrustServerCertificate=True";
var conn = new SqlConnection(connectionString);

// 此调用成功
conn.Open(); 

var options = new DbContextOptionsBuilder<MyDbContext>()
    .UseSqlServer(connectionString)
    .Options;

var m_ctx = new MyDbContext(options);

// 此调用抛出异常
foreach (var employee in m_ctx.Employees) 
{
    Console.WriteLine(employee.FullName);
}

SqlConnection.Open()调用成功，但遍历Employees时抛出如下异常：

Microsoft.Data.SqlClient.SqlException (0x80131904): 建立与SQL Server的连接时发生与网络相关或特定于实例的错误。找不到服务器或无法访问。验证实例名称是否正确，以及SQL Server是否配置为允许远程连接。(provider: Named Pipes Provider, error: 40 - 无法打开到SQL Server的连接)
System.ComponentModel.Win32Exception (2): 系统找不到指定的文件。

异常堆栈信息：

at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)  
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)  
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.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, 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.ProviderBase.DbConnectionClosed.TryOpenConnection(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.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()    

开发PC连接本地SQL Server（连接字符串Data Source=(local)）时，两者调用均成功。请问为何连接本地数据库时EF DbContext与SqlConnection表现一致，但连接远程服务器时表现不同？

原因分析与解决方案 #

核心原因 #

1. 连接字符串被隐性覆盖：EF Core的UseSqlServer方法可能读取了配置文件（如appsettings.json、环境变量或机器配置）中的同名连接字符串，优先级高于你代码中传入的值。脚手架生成的MyDbContext有时会默认读取配置源的连接信息，而非完全使用代码传入的参数。
2. 连接协议差异：直接调用SqlConnection.Open()时，因为指定了IP地址，会默认使用TCP/IP协议连接；但EF Core首次查询时可能从连接池获取连接，若连接池中的连接被配置为使用Named Pipes（而远程服务器未开启该协议），就会触发连接错误。
3. DbContext构造函数逻辑问题：部分脚手架生成的MyDbContext包含默认构造函数，会自动读取配置文件中的连接字符串，即使你传入了options参数，也可能被构造函数内的逻辑覆盖。

解决步骤 #

• 检查MyDbContext构造函数：打开脚手架生成的MyDbContext.cs文件，确认构造函数是否存在默认读取配置的逻辑。例如：

  public MyDbContext()
  {
      // 可能存在读取appsettings.json的逻辑
  }
  

  确保实例化时传入的options被正确使用，或修改构造函数强制优先使用传入的配置。
• 显式指定连接协议：在连接字符串中添加tcp:前缀，强制使用TCP/IP协议，避免EF自动切换到Named Pipes：

  Data Source=tcp:10.0.1.215;Password=...;Persist Security Info=True;User ID=webuser;Initial Catalog=MyDB;Encrypt=False;TrustServerCertificate=True
  

• 排查配置源优先级：检查应用的appsettings.json、环境变量等配置源，确保没有同名的连接字符串覆盖代码中传入的值。
• 清理连接池：在实例化DbContext前，调用SqlConnection.ClearAllPools()清理可能存在的错误连接池条目，避免复用错误配置的连接。

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