如何通过SqlClient/SqlConnection正确连接SQL Server 2014数据库？

正在将Java应用迁移至.NET和C#，从JDBC切换到SqlClient。测试数据库连接时随机出现以下两种错误之一：

错误场景1 #

调试信息：

//Debug info
Loading filename: BREConfig.txt
Loading properties from file: BREConfig.txt
Properties Loaded: 23
Setting Config Values...
Server=tcp:###;Initial Catalog=Wc3Online;User ID=####;Password=####;TrustServerCertificate=True; Encrypt=False;

错误内容：

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=13952; handshake=1056;

错误场景2 #

调试信息：

//Debug info
Loading filename: BREConfig.txt
Loading properties from file: BREConfig.txt
Properties Loaded: 23
Setting Config Values...
Server=tcp:####;Initial Catalog=###;User ID=####;Password=###;TrustServerCertificate=True; Encrypt=False;

错误内容：

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

已确认可通过RDC使用SQL Server身份验证成功连接该数据库，当前使用SqlClient的连接代码如下：

测试代码 #

SqlConnection testConn = BREDBConnectionUtil.GetConnection();
Console.WriteLine(BREDBConnectionUtil.GetConnectionString());  

try
{
    testConn.Open();
    Console.WriteLine("Database server connected, using " + testConn.Database);
    testConn.Close();
    Console.ReadLine(); 
} 
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace);
    Console.ReadLine();
}

连接工具类代码 #

// connection string values pulled from text file and stored in objects
private static SqlConnection con = new();
private static readonly BREConfiguration config = BRESession.GetConfiguration();
private static readonly Logger logger = new();

// Connect to SQL Server 2014
private static readonly string connectionString = "Server=tcp:" + config.GetDbConnection() + ";Initial Catalog=" + config.GetDbName() + ";User ID=" + config.GetDbUser() + ";Password=" + config.GetDbPassword() + ";TrustServerCertificate=True; Encrypt=False;";

// returns a SqlConnection to connect to SQL Server 2014 database
[MethodImpl(MethodImplOptions.Synchronized)]
public static SqlConnection GetConnection()
{
    con.ConnectionString = connectionString;      

    try 
    {
        if (con.ConnectionString.Length == 0) 
        {
            con = new SqlConnection(connectionString);
        }
    } 
    catch (Exception e) 
    {
        //print err
    } 

    return con;
}

1. 移除静态SqlConnection实例，每次请求新建连接 #

SqlConnection是轻量级对象，.NET自带连接池会自动管理连接复用，复用静态实例会导致连接状态混乱（比如连接已被关闭、处于异常状态），这是随机错误的核心原因。

修改GetConnection方法，直接返回新的SqlConnection实例：

// 移除静态con字段
private static readonly BREConfiguration config = BRESession.GetConfiguration();
private static readonly Logger logger = new();

// 使用字符串插值简化连接串构建，避免拼接错误
private static readonly string connectionString = $"Server=tcp:{config.GetDbConnection()};Initial Catalog={config.GetDbName()};User ID={config.GetDbUser()};Password={config.GetDbPassword()};TrustServerCertificate=True;Encrypt=False;";

public static SqlConnection GetConnection()
{
    try 
    {
        return new SqlConnection(connectionString);
    } 
    catch (Exception e) 
    {
        // 补充实际日志记录逻辑
        logger.Error("Failed to create SqlConnection", e);
        throw; // 抛出异常让调用方处理，不要静默吞掉
    } 
}

2. 使用using语句自动管理连接生命周期 #

using语句会自动调用Dispose方法，确保连接被正确释放回连接池，避免连接泄漏：

Console.WriteLine(BREDBConnectionUtil.GetConnectionString());  

try
{
    using (SqlConnection testConn = BREDBConnectionUtil.GetConnection())
    {
        testConn.Open();
        Console.WriteLine("Database server connected, using " + testConn.Database);
    } // 此处自动关闭并释放连接
    Console.ReadLine(); 
} 
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine(ex.StackTrace);
    Console.ReadLine();
}

3. 优化连接字符串配置 #

• 明确指定连接超时时间（默认15秒，可根据网络情况调整）：添加Connection Timeout=30;
• 确认SQL Server 2014的TCP端口（默认1433），如果是自定义端口，连接串需改为Server=tcp:###,端口号;
• 移除连接串中的多余空格，比如Encrypt=False;前的空格

修改后的连接串示例：

Server=tcp:your-server,1433;Initial Catalog=Wc3Online;User ID=your-user;Password=your-pass;TrustServerCertificate=True;Encrypt=False;Connection Timeout=30;

4. 排查网络层面问题 #

虽然RDC能连接，但仍需确认：

• 应用服务器到SQL Server的TCP 1433端口是否稳定（可使用tracert或telnet测试连通性）
• SQL Server的最大连接数是否被耗尽（执行SELECT @@MAX_CONNECTIONS;检查，或查看SQL Server日志）
• SQL Server的TLS版本兼容情况（SQL Server 2014默认支持TLS 1.0/1.1，若应用服务器禁用这些版本会导致握手失败）

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