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

问题描述

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

<块引用>

服务器=THESERVERTheInstance;Database=TheDatabase;用户 ID=TheUser;密码=密码；

返回的错误是:

<块引用>

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

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

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

TheTable 很小，只有 78 行.

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

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

解决方案

原来是THESERVER的IPv4地址启用了TCP/IP，而IPv6地址没有启用.p>

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

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

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

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

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

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; Password=ThePassword;

the error returned is:

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;

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 is small, just 78 rows.

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.

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

解决方案

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

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

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

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.

To enable TCP/IP for additional IP addresses:

• Start Sql Server Configuration Manager
• Open the node SQL Server Network Configuration
• Left-click Protocols for MYSQLINSTANCE
• In the right-hand pane, right-click TCP/IP
• Click Properties
• Select the IP Addresses tab
• For each listed IP address, ensure Active and Enabled are both Yes.

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