有时可以连接到 SQL Server [英] Connection to SQL Server Works Sometimes

问题描述

ADO.Net 应用程序有时只能连接到本地网络上的另一台服务器.给定的连接尝试是成功还是失败似乎是随机的.连接使用以下形式的连接字符串:

An ADO.Net application is only sometimes able to connect to another server on the local network. It seems random whether a given connection attempt succeeds or fails. The connection is using a connection string in the form:

Server=THESERVERTheInstance;Database=TheDatabase;User Id=TheUser;密码=密码；

Server=THESERVERTheInstance;Database=TheDatabase;User Id=TheUser; Password=ThePassword;

返回的错误是:

连接超时已过期.尝试使用登录前握手确认时超时时间已过.
这可能是因为登录前握手失败或服务器无法及时回复.
尝试连接到此服务器所花费的持续时间为 - [Pre-Login] 初始化 = 42030；握手=0；

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=42030; handshake=0;

.NET 应用程序是一个执行以下代码的小型测试应用程序:

The .NET application is a small test app that executes the following code:

using (SqlConnection conn = new SqlConnection(cs))
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM TheTable", conn))
{
    conn.Open();
    int rowCount = (int)cmd.ExecuteScalar();
}

TheTable 很小，只有 78 行.

TheTable is small, just 78 rows.

但是，在 .NET 应用程序收到此错误的同一台机器上，我可以使用 SSMS 和连接字符串中指定的用户 ID/密码连接到 THESERVER.

However, on the same machine where the .NET application receives this error, I am able to connect to THESERVER using SSMS and the User Id/Password named in the connection string.

为什么来自 ADO.Net 应用的连接可能会失败，但使用来自 SSMS 的相同凭据会成功?

Why might the connection fail from an ADO.Net app, but succeed with identical credentials from SSMS?

推荐答案

事实证明，TCP/IP 已为 THESERVER 的 IPv4 地址启用，但未为 IPv6 地址启用.

It turned out that TCP/IP was enabled for the IPv4 address, but not for the IPv6 address, of THESERVER.

显然，一些连接尝试最终使用了 IPv4，而其他尝试使用了 IPv6.

Apparently some connection attempts ended up using IPv4 and others used IPv6.

为两个 IP 版本启用 TCP/IP 解决了该问题.

Enabling TCP/IP for both IP versions resolved the issue.

事实证明 SSMS 有效的事实是巧合(前几次尝试大概使用了 IPv4).后来一些尝试通过 SSMS 进行连接导致了相同的错误消息.

The fact that SSMS worked turned out to be coincidental (the first few attempts presumably used IPv4). Some later attempts to connect through SSMS resulted in the same error message.

为其他 IP 地址启用 TCP/IP:

To enable TCP/IP for additional IP addresses:

• 启动 Sql Server 配置管理器
• 打开节点 SQL Server 网络配置
• 左键单击 MYSQLINSTANCE 协议
• 在右侧窗格中，右键单击 TCP/IP
• 点击属性
• 选择IP 地址"标签
• 对于列出的每个 IP 地址，确保 Active 和 Enabled 均为 Yes.

这篇关于有时可以连接到 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！