SQL Server 2008 remote connection only works once

Question

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.

Answer 1

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.