Sql Server Connection Irritations (again)

  • Thread starter Thread starter Shawn B.
  • Start date Start date
S

Shawn B.

We recently migrated from .NET framework 1.x to 2.0 with a code base that is
4 years old. The application is a mix between Windows Services, Web
Application, and some internal WinForms tools. We never had a problem using
framework 1.x and even the same code that fails in framework 2.0, pasted
into a 1.x project succeeds every time. This is a C# 2005 project.

All our servers are Windows Server 2003 and all our SQL Server databases are
SQL Server 2000 (we do not have an installation of 2005 on our network). We
recently started receiving the following error message:

A connection was successfully established with the server, but then an error
occurred during the pre-login handshake. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections. (provider: TCP Provider,
error: 0 - A request to send or receive data was disallowed because the
socket is not connected and (when sending on a datagram socket using a
sendto call) no address was supplied.)

We have been attempting to troubleshoot this for 3 weeks now with minimal
results. This problem happens during the SqlConnection.Open() command and
always returns instantly as far we can tell. We have done the following:

* Added "np:" (named pipes) prefix to the connection string. This usually
solves the problem but our production SQL Servers are seperated over the
network from the application servers via VLAN and so named pipes is not an
option, must be TCP/IP.

* Specified "tcp:x.x.x.x, xxxx;" in the connection string to no effect.

* Toggled various other connection string settings such as pooling,
integrated security, and so on, to no effect in endless combination.

* Tested our application builds in DEBUG and RELEASE modes. DEBUG mode
always fails while RELEASE build always succeeds. There is nothing in our
project/solution settings that we changed apart from the defaults when
created, and there is nothing in our code that distinquishes from RELEASE
and DEBUG mode.

* Ported our c# code to VB.NET to see what happens and the problem happens
exactly the same in VB.NET.

* Ported our c# code to VS.NET 2003 and it succeeds.

* Disabled the inclusion of VSHOST.EXE in our builds. This had a
significant impact. On some client machines the problem dissappeared, on
others it intensified, in others, it reduced the occurrance but not
completely.
We have toggled (in various combinations) the network adapter card CHECKSUM
settings (on/off) to no appearant effect.

We have many database servers in production. When the user logs in, they
always go to the same database which authenticates them and then returns
with a connection string that they should use throughout the rest of their
session. The first authentication connection NEVER fails but the second
connection, where they are actually in the application, fails most of the
time. These connections happen on the same server, BTW, just different
databases. The assigned connectionstring never changes. Sometimes it
works, sometimes it doesn't. In framework 1.x, it ALWAYS works.

According to this article from Siraj Lala at the Data Programmability team
blog for data access in ADO.NET,
http://blogs.msdn.com/dataaccess/archive/2005/06/09/426957.aspx the new
format of an exception for ADO.NET 2.0 as the following:

<sqlclient message> (provider:<SNIX provider>, error: <SNIX error code> -
<SNIX error message>)

where SNIX is the SQL network transport later used by the SqlClient
<namespace>. The exception will be thrown by the data provider via the
generic DbException object. In our case, we are recieving the SqlException
exception directly, which derives from DbException. This indicates
something happening with the data provider.

The <SNIX error message> portion of our error message is:

A request to send or receive data was disallowed because the socket is not
connected and (when sending on a datagram socket using a sendto call) no
address was supplied.

This is a socket message: WSAENOTCONN (10057):
<http://kb.globalscape.com/article.aspx?id=10140>

This leads me to believe that something is happening internally with the SQL
client data provider for Framework 2.0, primarily because it happens with
the same code in VB.NET 2005 and C# 2005.

Our connection string is of the form: "Data Source=192.168.1.x,1433;Initial
Catalog=databaseName;User ID=userName;Password=password;"

Any help tracking this source of the problem and potential solutions would
be greatly appreciated.



Thanks,
Shawn
 
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
 
Show your connection string. The user context is different under Debug vs
Release modes

Jeff
 
Show your connection string. The user context is different under Debug vs
Release modes
Click to expand...

It was at the end of the original post:

"Data Source=192.168.1.x,1433;Initial Catalog=databaseName;User
ID=userName;Password=password;"

The only difference is I removed company-specific information. The
connection string is the same regardless of DEBUG/RELEASE mode.

What I didn't mention in the email (wasn't thinking of it) was that in
RELEASE mode the problem rarely ocurrs from ASP.NET web application but does
happen. WinForm and Windows Services won't experience the problem in
RELEASE mode but the problem always happen during a DEBUG build.

When I say DEBUG, I mean compiled in DEBUG mode but executed with or without
the IDE or with or without a debugger attached.

Thanks,
Shawn
 
The important part is Network Library=...

Shawn B. said:
It was at the end of the original post:

"Data Source=192.168.1.x,1433;Initial Catalog=databaseName;User
ID=userName;Password=password;"

The only difference is I removed company-specific information. The
connection string is the same regardless of DEBUG/RELEASE mode.

What I didn't mention in the email (wasn't thinking of it) was that in
RELEASE mode the problem rarely ocurrs from ASP.NET web application but
does happen. WinForm and Windows Services won't experience the problem in
RELEASE mode but the problem always happen during a DEBUG build.

When I say DEBUG, I mean compiled in DEBUG mode but executed with or
without the IDE or with or without a debugger attached.

Thanks,
Shawn
Click to expand...
 
The important part is Network Library=...
We did this. Instead of erroring out immediately, it takes a while and then
it never works after that while.

The connectionstring you provided looks like an OLEDB connection string as
SqlConnection doesn't accept Provider=. It might be worth it for us to see
if it happens with the OleDbConnection as well.


Thanks,
Shawn
 
Also, as a test, make sure your SQL is listening via TCP/IP only (turn OFF
named pipes on the server, if you can)

Jeff
 
The important part is Network Library=...

Okay, we did try this parameter and we tried replacing the SqlConnection()
with OleDbConnection() and the same problem happens, except it is a "General
network error" in OleDbConnection() which is consistent with the article:

http://blogs.msdn.com/dataaccess/archive/2005/06/09/426957.aspx

Which explains that what used to be called "General Network Error" now takes
the form of the message we are receiving in SqlConnection()

A connection was successfully established with the server, but then an error
occurred during the pre-login handshake. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections. (provider: TCP Provider,
error: 0 - A request to send or receive data was disallowed because the
socket is not connected and (when sending on a datagram socket using a
sendto call) no address was supplied.).


Thanks,
Shawn
 
Also, as a test, make sure your SQL is listening via TCP/IP only (turn OFF
named pipes on the server, if you can)
Click to expand...

Yes, our SQL Servers are listening TCP/IP only. Named Pipes is indeed off,
which is why its so perplexing that when we specify NP; in the prefix
connectionstring it succeeds.


Thanks,
Shawn
 
So this same thing happens when you create a brand new one-page project?

Is it only with this one SQL Server? Keep in mind, the error isn't implying
that you are using SQL 2005. I read it to say "IF you are running 2005..."

Jeff
 
So this same thing happens when you create a brand new one-page project?

I don't know, it isn't very easy to connect a one-page project into our
infrastructure.

However, when we create a one-form 2 lines-of-code project that opens a
SqlConnection() WinForms application it fails on the developer workstations
and a few non-developer workstations where we just execute the executable.
Is it only with this one SQL Server? Keep in mind, the error isn't
implying that you are using SQL 2005. I read it to say "IF you are running
2005..."
Click to expand...

We know of at least 7 of our servers (dev, staging, QA, pre-production) that
are having this problem (these are the SQL servers). The web servers, we
know of at least 3 that are having the problem (communicating with various
of the sql servers listed above) and so far every individual developer
machine has the problem communicating with any of the sql servers mentioned
above.

Anyway, that's weird that it would mean "if you are running 2005". It
should know better. In any case, I'm not hung up on the 2005 in the
message, I'm hung up on the fact that there's a problem in the first place.


Thanks,
Shawn
 
I wonder if Windows Firewall or similar is blocking communction on that port

You should run a netmon trace
 
I wonder if Windows Firewall or similar is blocking communction on that
port

You should run a netmon trace
Click to expand...

Windows firewall (confirmed) is not active on any of our machines. We do
have a network firewall but our DataCenter manager insists that's not the
problem. We have run NetMon traces up and down on the success and failures
and we have seen some interesting anomoly, on the failures, there are
CHECKSUM errors and then a connection reset (which is consistent with people
who get the same 10057 error in their socket programming where they act upon
the connection after it has been closed) but then, we get the same thing on
successes, also. So its hard to really know whats going on.

In any case, assuming that's it, which is a possibility, why would ADO.NET
internally be closing the connection and then trying to use it? Remember,
this happens before the SqlConnection.Open() command returns with an
exception.


Thanks,
Shawn
 
After working on MS support on this issue, it appears to be a fault in their
programming and not a fault in our programming or network. They asked us to
download SP1 for VS 2005 (which I wasn't even aware was released yet) and
see if it helps. I'll keep this thread informed of whatever results.



Thanks,
Shawn
 
After working on MS support on this issue, it appears to be a fault in
their programming and not a fault in our programming or network. They
asked us to download SP1 for VS 2005 (which I wasn't even aware was
released yet) and see if it helps. I'll keep this thread informed of
whatever results.
Click to expand...

Actually, advice to download SP1 was premature as there isn't one.

However, we (us and Microsoft) have identified problems that may (or may
not) actually be in the .NET framework or somewhere lower-level than that at
the winsock layer; certainly not at our application-code layer, though. No
workaround or solution has been discovered yet.


Thanks,
Shawn
 
However, we (us and Microsoft) have identified problems that may (or may
not) actually be in the .NET framework or somewhere lower-level than that
at the winsock layer; certainly not at our application-code layer, though.
No workaround or solution has been discovered yet.
Click to expand...

We found the problem. The solution (for the WinForms project experiencing
the problem) is to disable the tooltip on the standard .NET TreeView control
used in the application GUI. For whatever reason, having the Tooltip active
was causing a heap corruption to occur, which somehow effected the database
provider internal to the .NET framework or beyond.

In any case, bizarre as it sounds, it corrected the problem. MS is working
on solving the problem but for now we have a viable workaround.


Thanks,
Shawn
 
I have the same Problem.
What do you mean by disable the tooltip?
As far as I know you can set or get the tooltip, so how do you disable
it?

Thanks.

Michel
 
I have the same Problem.
What do you mean by disable the tooltip?
As far as I know you can set or get the tooltip, so how do you disable
it?
Click to expand...

The way we "disable" the tooltip is by not setting the text of it in the
first place. Previously, we were always setting it. Once we were asked to
not set the text (hence, disable) then the problem went away completely.


Thanks,
Shawn
 
Back
Top