SQL Server: "a connection was successfully established with server....existing connection was forcibly closed by the remote host."

Question

Yes folks, it's this one again.

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

I'm sorry... I have Google'd this, I have read the other StackOverflow articles on this problem, and I have tried all kinds of suggestions, but nothing works.

Here's a few notes about what we're seeing.

1. This issue occurs occassionally in SQL Server Management Studio itself (doing any kind of database activity... getting a list of tables in a database, having a look at a Stored Procedure, etc)

2. It also happens in Visual Studio 2010 itself, when it is trying to get data from the servers (e.g. when creating a .dbml file, etc)

3. It also sometimes happens in our .Net (ASP, WPF, Silverlight) applications.

4. Our SQL Server 2005 & 2008 servers are all based on virtual machines in data centres around the world, and we see sometimes this error on each of them. But most of the time, they all work absolutely fine.

5. When the error does occur, we can just "retry" what caused the error, and then it'll work fine.

6. We think.. if we have an IIS Web Server in a data centre in a particular city, and it accesses a SQL Server in the same data centre, then we don't see the issue.

7. We think.. if we connect to the servers, and specify the UserID and Password to use, it causes this error much more frequently than if we just use Active Directory authentication.

Put all that together, and it sounds to me like some kind of network issue.
But can anyone suggest what to look for ?

This isn't a bug in our .Net applications, as even SQL Server Management Studio "trips up" with this error.

It's baffling us.

Answer 1

Just in case anyone else hits this issue, we finally found the solution.

Our company uses Riverbed software to compress data, when it's being passed between locations, and this was somehow causing some connections to get dropped.

Our IT gurus found a configuration setting which finally fixed this issue.

I believe there's a setting in there to turn off compressing results from SQL Server (or something like that). That fixed it for us.

Answer 2

It could be any number of network issues. ANYTHING that prevents the code from reaching the server even for the few miliseconds it takes to make one query.

it could also be the result of a failover. When we went from a single SQL Server to a clustered environment, we'd see this happen during a failover. In this case, it turned out to be our Connection Pooling. In essence, the SQL cluster has a controller and two servers behind it. A and B.

Say our web app is using server A just fine, Connection pooling creates a connection on both sides. The server is aware of it, and the web app is aware of it. Once the cluster fails over to the second server, the web app is aware of the connection but server B is not, so we get an error.

The point is, any possible cause of network issues imaginable may be the cause. DOS attacks on the server, man-in-the middle attacks intercepting and changing traffic. Someone trips on an ethernet cable and it's loose in the jack. You name it, if it can cause a connection issue, it could be the cause.

Your issue also sounds like one we had recently - we also have a virtual environment, wih software that moves VMs from one host to another as needed for load balancing. Every so often, we'd get bombarded with the same error. It turned out to be an issue with the NIC drivers on one of the hosts, so whenever a VM moved to that particular host, errors would occur.

It's really not a programming issue. It's an environment issue, and you need trained professionals with direct access to your environment to research and resolve this.

Answer 3

My problem was that I was inadvertently using a wireless network to connect to our network because the Ethernet cable was faulty. This after repairing SQL Server, running a Winsock reset as recommended elsewhere ...