SQL Server 2008远程连接只能工作一次
当我远程连接到 SQL Server 2008 时，它只能工作一次，之后服务器就会挂起。 该服务无法停止或重新启动，并且在尝试再次连接时会出现“超时”错误。
服务器已启用 TCP/IP 连接。 默认端口设置为 1433，我从动态端口中清除了 0。 我启用了 127.0.0.1 IP 和公共 IP，并将 1433 IP 设置为它们。 命名管道和其他协议（共享内存或其他协议）被禁用。
我使用“sa”用户和强密码从远程计算机进行连接。 服务器设置为接受两种身份验证模式。
第一次从远程机器连接效果非常好。 查询工作和数据可以从数据库中检索。 断开连接并尝试再次连接后，出现超时错误。 产生此错误的原因是 SQL Server 在某处挂起。 此时无法从服务计算机停止或重新启动 SQL Server 服务。 唯一的解决办法是重新启动计算机。 但是，从 SQL Management Studio 本地连接到服务器仍然有效。
我认为这与在某个地方进入无限循环有关，或者在与远程计算机断开连接后它不会断开 1433 端口上的连接，并且仍然等待来自远程计算机的输入。
When I connect to the SQL Server 2008 remotely it only works once, after that the server hangs. The service cannot be stopped or restarted and when trying to connect again it gives a 'Timeout' error.
The server has TCP/IP connections enabled. The default port is set to 1433 and I cleared the 0 from the dynamic ports. I enabled the 127.0.0.1 IP and the public IP and set the 1433 IP to them. Named pipes and the other protocol (Shared Memory or something) are disabled.
I am connecting from the remote machine using the 'sa' user and a strong password. The server is set to accept both authentication modes.
Connecting for the first time from the remote machine works perfect. Queries work and data can be retrieved from the databases. After disconnecting and trying to connect again it gives a timeout error. This error is generated because the SQL Server is hanged somewhere.
At this point it is impossible to Stop or Restart the SQL Server service from the service machine. The only solution is to restart the computer. However, connecting to the server locally from SQL Management Studio still works.
I think it has something to do with going into an infinite loop somewhere, or it doesn't drop the connection on the 1433 port after disconnecting from the remote machine and it still waits for input from it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您是否排除了网络层的任何东西，例如软件或硬件防火墙、NAT、代理等？
您是否将 SQL Server 作为默认实例或命名实例运行？
如果您在运行时执行 netstat 那么当你有时间休息时，你会看到什么？
尝试在服务器上运行网络监视器或wireshark以查看请求是否通过& 如果是，服务器有响应吗？
编辑：
有点令人担忧的是，当 sql server 未运行时，您可以通过端口 1433 连接到服务器，您应该会收到连接被拒绝（无防火墙）或超时（有防火墙）的信息
。 审核登录/注销您应该能够看到客户端连接吗？ 它可以帮助您解决问题吗？
尝试使用钝器，例如重新安装 sql server 连接驱动程序。 mdac，客户端上原生的 sql。
have you ruled out anything at the network layer such as software or hardware firewalls, NAT'ing, proxies ect?
Are you running SQL Server as a default or named instance?
if you do a netstat while things are working & then when you get a time out, what do you see?
Try running network monitor or wireshark on the server to see if the request is getting through & if so is the server responding?
EDIT:
It's a bit of a concern that you can connect to the server on port 1433 when sql server isn't running you should be getting a connection refused (no firewall) or a timeout (with a firewall)
Run profiler on the server & audit logins/logouts you should be able to see the client connect? it may help you troubleshoot the issue?
Try a blunt instrument like re-installing the sql server connectivity driver eg. mdac, sql native on the client.