Re: Access 2007->SQL Server2005 "connection was forcibly closed",G

Hi Erland,

"Erland Sommarskog" wrote:

Christian Coppes (ChristianCoppes@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I followed your recommendation and testet to cut off the network
connection in a running client. The result is that Access only shows
"ODBC--call failed Communication link failure (#0)". Next is that Access
crashes completely ("Microsoft Office Access has encountered a
problem..."). So this is not the same behaviour as with the reported
error. So I guess that this is not a lost connection to the server, it
means what it said: The connection was forcibly closed by the host. Some
service, whatever it is, stops the connection and gives this error back
to the ODBC driver.

Of course the difference is that when you pull the cable all connections
go down. Then again, if there is a faulty network thing somewhere, this
is exactly what would happen.

I've think I've seen this error when I have been running a query-stress
tool which hammers the server with request, and I think has been when
I have had the client and SQL Server on a different machines. I've then
only had a few of them.

Something occurred to me, and I went on a Google search the error
message. The culprit may be in Windows. What is the SQL Server machine
running? Windows 2003 has a protection against something known as a
"SYN flooding attack", and maybe your Access application is taken for
one. (Now, is that a sign that you need to redesign or what? :-)

Look here, and see if you can get the admin to add this to registry
on the SQL Server box:
http://technet.microsoft.com/en-us/library/cc781167(WS.10).aspx

Thanks for the hint, I already read about this and wrote this to the admin
but have no reply until now. I'll ask him again.

What I did today: I downloaded "TCPView" from Sysinternals. Here's the link:
http://technet.microsoft.com/en-us/sysinternals/bb897437.aspx

I tested the application again using this tool and opened all linked tables
one by one as fast is I could click. The result look like this for SQL Server
2005:

MSACCESS.EXE:3264 TCP MyComputer:1342 SQLServer2005:2594 ESTABLISHED
[System Process]:0 TCP MyComputer:1347 SQLServer2005:2594 TIME_WAIT

(changed the full qualified names in the list).

The most processes are opened like the first line, some are opened as
"System Process" with "TIME_WAIT". After a certain timeout (which I can
extend by using the following Registry hack:
http://www.pctools.com/guides/registry/detail/878) the "TIME_WAIT" processes
will be stopped and removed. If they all have been removed, the MSACCESS
processes begins to being removed. Result is, after this happens the first
time the error occurs in Access. It doesn't stop here, they are being removed
until nearly 10 processes left, after this it takes longer until the next is
being removed.
Result is in all cases that every time a "MSACCESS" process is being removed
one error comes up in Access.
I tried this with OLEDB (normal Windows SQL Server driver) and SQL Native
Client, the error occurs in both cases so it's not a special driver problem.

Then I tried to use the older SQL Server 2000 which was used before for the
same application. Nearly everything is the same here, same tables,
structures, access rights and so on. I tested the same as above with this
one, it looks like this in TCPView (only two samples, as in the example above
you must think of about 25 entries in this way):

MSACCESS.EXE:3744 TCP MyComputer:1407 SQLServer2000:ms-sql-s ESTABLISHED
[System Process]:0 TCP MyComputer:1408 SQLServer2000:ms-sql-s TIME_WAIT

The behaviour is the same as in the above case: After opening some of the
tables with doubleclick, the first tasks were listet like the first line
above, all following as "TIME_WAIT" like the second line.
And as above after a special timeout the "TIME_WAIT" processes were removed
one by one. After another timeout, the MSACCESS processes were removed one by
one until around 10 left - same as above. But the difference here: No error
is shown, all opened tables are accessible as before.

I checked the configuration with SSMS and the only difference I could find
is that SQL Server 2000 has a Max Worker Thread value of "255" and SQL Server
2005 a value of "0". Following the books online guide I saw that this is the
value of the maximum number of threads which SQL server works on at the same
time, independent how much connections are made to the server. And that it
could be better to set a special value instead of "0" which means the number
is handled dynamically internal. I don't know if this is the reason, I wrote
it to the admin now.

Next I saw is the Configuration Manager of SQL Server tools, there are the
protocols described. Here is the possibility to set values for "Keep alive"
and "Keep Alive internal", maybe these values can help adjust the problem? I
wrote the admin to compare these values.

And I saw comparing the trace of TCPView above that SQL Server 2005 always
connects to port "2594" and SQL Server 2000 to port "ms-sql-s". Does this
mean that 2005 is using TCP and 2000 is using named pipes? Can that make a
difference and can the order of the used protocols in Configuration Manager
be a problem? As there are 34 other databases on the server it should not
create a problem to all the other clients...:-)

Yesterday I made a trace with Wireshark to test the network traffic between
both computers, but I couldn't find anything what could be helpful. I'm not
so deep in analyzing traffic to say if there's something problematic in there.
I could see that there is a regular "TCP Keep-Alive" traffic as expected.
From time to time a "TCP ZerWindowProbe" occurs and a lot of acknowledge
packets on the way to the server, I guess these are all the network
components on the way to the server.
I also switched off all other network tasks on my computer, Symantec
Altiris, Antivirus and Windows Firewall, didn't change the problem.
And I was in contact with someone of the network hardware support who helped
me testing the hardware environment on the way to the server to check if
there are any problems but there was no error in the time when my Access
error occured. So I don't think that it is a network hardware problem, I
guess it is a SQL Server configuration or TCP configuration problem on the
server.

It's OK that you and Mary want to show me the advantages of "real" clients -
but I don't think it's a solution...:-)
Fact is, Microsoft has made Access as a possible SQL Server client and the
ODBC- method of accessing SQL Server is not only made for Access, it is made
for all client applications which want to get access to any sort of database
server. This is working since years in many versions of Access and other
software, and the SQL Server 2000 in my case prooves that it is a possible
and good solution to create stable applications - this server never had such
problems using the same client. And I don't believe that SQL Server 2005
(which is on the market since four years now) is a step backward...
Moreover in me search for this problem I found a very high number of
programmers who tried to find a solution for this problem using ADO.NET
applications or Java, even the same problem when accessing MySQL server with
Java, ASP, IIS, whatever. So this is not really a problem of the used
programming language, client or server, it is a communication problem between
them and needs exact investigation to find the special reason - in all of the
found cases. The number of possible solutions I found (but unfortunately
don't helped in my case) shows that I'm not alone with this. The
documentation about this problem is very poor from Microsoft, they say
something like "yes, it means it is a network problem", and some independent
knowledge base articles (like the one you showed above) which are only
special solutions in special cases.
Yes it is impossible to show all possible solutions for all cases as there
are too many possibilities but a LITTLE more investigation tips would be very
helpful..

And my last test: I created an Access project connection to the 2005
database. Access projects doesn't use ODBC to connect, they work directly
with the SQL Server without any use of the Jet DB machine Access normally
uses.
If I make the same test as above, opening all tables I could find on the
server (a lot more than used in the application) to which I have access to,
the result in TCPView is that only three connections are made, one I guess is
always for Access internal use as this happens with ODBC, too.
I can open and close them as fast as I can, I can see that one of the three
processes is "TIME_WAIT", too, it ends after the timeout like before but then
only these two connections are there and works stable. No problem.

I know that ADP is not preferred by Microsoft - but I don't know why because
in my opinion it is the very best, stable and fast method to access a server
- because it uses the server directly and no sensefree try to translate Jet
SQL into "real" SQL with complete other data types and programming methods.
The only problem I found until know is the fact that in every form the
records are delimited to 1000 records. This can be adjusted but you must
change it in any form and what if you need more than the limit you once
configured? That makes no real sense for me. That's the only thing Jet can do
better by using dynasets which only retrieves as many records as being
displayed on the screen.

But to come back to the problem: No, unfortunately I cannot change the
application to an ADP version, because it would take more time to do that
than the expected lifetime of the application is. In the time the application
has from now on the programming time must be used to make it better wherever
possible, no time is given to change it into a real database application.
The following product is planned to be a SAP application which has nothing
to do with this server or application. So there will be no real "follow-up".

That brings me back to my problem, I must find a solution for this in the
way it uses the server and SQL Server 2000 shows that it is possible so it
must be possible for SQL Server 2005 - my opinion.

Thanks for any help and patience...:-)

Christian
.

Relevant Pages

  • Re: Connection Error: ADO.Net and SQL Server 2005
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... I can connect to the server through an ODBC connection ... It is very puzzling because this client doesn't seem to have anything ...
    (microsoft.public.sqlserver.connect)
  • RE: Error 17835 - When encryption and Shared Memory are enabled
    ... Did you encounter this error when you connected to your SQL Server ... Did your SQL Server instance use a certificate explicitly specified by ... you configure your client connection settings "Force Protocol Encryption" ...
    (microsoft.public.sqlserver.connect)
  • Re: Problems with SQLEXEC
    ... handler sometimes is reporting SQL Server communication ... network conditions but nothing more. ... When we have a client that experiences this sort of thing and we suspect the ... that has no network connection at all. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Losing connections with SQL 2005
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... SQL Server automatically detects if the client falls into the sea or gets ... Does the Connection pooling mechanism do this on its ... Upgrading our app is a really big task, ...
    (microsoft.public.sqlserver.connect)
  • Re: netbios and named pipes sql replication.
    ... Once the client switched to named pipes, ... > connection with. ... > When we took named pipes support off the SQL server with the MS patch, ... > We had to manually walk thru the ODBC setup and configure the CLIENT ...
    (microsoft.public.sqlserver.replication)