SQL Server 2000 SP4 client connection problem

On Mon, 18 Feb 2008 08:58:26 -0800, "Rick Byham, \(MSFT\)"
wrote in
:

>Tough one.
>Using Query Analyzer on ClientB, try connecting to: tcp:ipaddress,1433
>The "tcp:" prefix will force the connection to use tcp, to confirm it's not
>trying to use named pipes.

This isn't working either, though I found something else interesting
by accident. I typed the username incorrectly and got an
authentication error very quickly. I can duplicate this using
"tcp:ipaddress,1433", just the IP or just the hostname.

This makes me think that the problem isn't reaching the server, but
rather the communication during authentication. Since the issue only
occurs when communicating with the server from ClientB, it is most
likely a problem on ClientB.

I think that I will reinstall the tools and see what happens.

>Is there any chance you have an alias configured using the ClientB Client
>Network Tools?

The alias list is empty.

>ClientB can ping the server by name, so I think name resolution is OK, but I
>would still check the DNS cache on ClientB by running ipconfig /displaydns.
>If the name is listed with the wrong ip address you can clear the DNS cache
>with ipconfig /flushdns. I admit this is less likely to be the problem.

I checked and found no entry for the server, either by hostname or IP.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Thu, 28 Feb 2008 12:57:08 -0500, Charles Calvert
wrote in :

>On Mon, 18 Feb 2008 08:58:26 -0800, "Rick Byham, \(MSFT\)"
> wrote in
> :
>
>>Tough one.
>>Using Query Analyzer on ClientB, try connecting to: tcp:ipaddress,1433
>>The "tcp:" prefix will force the connection to use tcp, to confirm it's not
>>trying to use named pipes.
>
>This isn't working either, though I found something else interesting
>by accident. I typed the username incorrectly and got an
>authentication error very quickly. I can duplicate this using
>"tcp:ipaddress,1433", just the IP or just the hostname.
>
>This makes me think that the problem isn't reaching the server, but
>rather the communication during authentication. Since the issue only
>occurs when communicating with the server from ClientB, it is most
>likely a problem on ClientB.
>
>I think that I will reinstall the tools and see what happens.

Reinstalling changed nothing. It still hangs during authentication,
either with Windows or SQL Server credentials.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

Have you checked for other errors in both the Windows event log and the SQL
Server log particularly on the SQL Server computer?
In Management Studio on the SQL Server computer, connect Object Explorer,
right-click the server name, and then click Properties.
On the Security tab, select login auditing; Both failed and successful
logins.
Try connecting again, and check both logs.
This will help you determine if SQL Server is getting the login packet but
rejecting it. In this case you might see a message about a malformed login
packet, or something.
If there is no login attempt on the server, then the client is not even
finding the SQL Server.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Charles Calvert" wrote in message

> On Mon, 18 Feb 2008 08:58:26 -0800, "Rick Byham, \(MSFT\)"
> wrote in
> :
>
>>Tough one.
>>Using Query Analyzer on ClientB, try connecting to: tcp:ipaddress,1433
>>The "tcp:" prefix will force the connection to use tcp, to confirm it's
>>not
>>trying to use named pipes.
>
> This isn't working either, though I found something else interesting
> by accident. I typed the username incorrectly and got an
> authentication error very quickly. I can duplicate this using
> "tcp:ipaddress,1433", just the IP or just the hostname.
>
> This makes me think that the problem isn't reaching the server, but
> rather the communication during authentication. Since the issue only
> occurs when communicating with the server from ClientB, it is most
> likely a problem on ClientB.
>
> I think that I will reinstall the tools and see what happens.
>
>>Is there any chance you have an alias configured using the ClientB Client
>>Network Tools?
>
> The alias list is empty.
>
>>ClientB can ping the server by name, so I think name resolution is OK, but
>>I
>>would still check the DNS cache on ClientB by running ipconfig
>>/displaydns.
>>If the name is listed with the wrong ip address you can clear the DNS
>>cache
>>with ipconfig /flushdns. I admit this is less likely to be the problem.
>
> I checked and found no entry for the server, either by hostname or IP.
> --
> Charles Calvert | Software Design/Development
> Celtic Wolf, Inc. | Project Management
> http://www.celticwolf.com/ | Technical Writing
> (703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

Are you sure the IP port is 1433? With dynamic port assignments it's usually
not. Check Configuration Manager to see what port is assigned to the SQL
Server instance you're trying to connect to.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Charles Calvert" wrote in message

> On Mon, 18 Feb 2008 08:58:26 -0800, "Rick Byham, \(MSFT\)"
> wrote in
> :
>
>>Tough one.
>>Using Query Analyzer on ClientB, try connecting to: tcp:ipaddress,1433
>>The "tcp:" prefix will force the connection to use tcp, to confirm it's
>>not
>>trying to use named pipes.
>
> This isn't working either, though I found something else interesting
> by accident. I typed the username incorrectly and got an
> authentication error very quickly. I can duplicate this using
> "tcp:ipaddress,1433", just the IP or just the hostname.
>
> This makes me think that the problem isn't reaching the server, but
> rather the communication during authentication. Since the issue only
> occurs when communicating with the server from ClientB, it is most
> likely a problem on ClientB.
>
> I think that I will reinstall the tools and see what happens.
>
>>Is there any chance you have an alias configured using the ClientB Client
>>Network Tools?
>
> The alias list is empty.
>
>>ClientB can ping the server by name, so I think name resolution is OK, but
>>I
>>would still check the DNS cache on ClientB by running ipconfig
>>/displaydns.
>>If the name is listed with the wrong ip address you can clear the DNS
>>cache
>>with ipconfig /flushdns. I admit this is less likely to be the problem.
>
> I checked and found no entry for the server, either by hostname or IP.
> --
> Charles Calvert | Software Design/Development
> Celtic Wolf, Inc. | Project Management
> http://www.celticwolf.com/ | Technical Writing
> (703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Fri, 29 Feb 2008 09:25:21 -0800, "Rick Byham, \(MSFT\)"
wrote in
:

>Have you checked for other errors in both the Windows event log and the SQL
>Server log particularly on the SQL Server computer?

I did and found nothing.

>In Management Studio on the SQL Server computer, connect Object Explorer,
>right-click the server name, and then click Properties.
>On the Security tab, select login auditing; Both failed and successful
>logins. Try connecting again, and check both logs.

I'm using 2000, but found the equivalent setting in Enterprise
Manager, set it, restarted and tried again.

The login was recorded as being successful in the server's event log
and the SQL Server log. Even after terminating the hung Query
Analyzer process on ClientB, there were no errors in the server's
event log or SQL Server log.

I have noted one thing; when Query Analyzer gets hung on ClientB, the
server can't seem to create any new Windows Explorer windows or
Management Console windows (such as the event viewer) until the Query
Analyzer process is terminated. This makes me wonder if SQL Server is
locking a mutex or other synchronization object on the server and not
letting go until the connection has completed.

I had another idea. I tried the following:

osql -E -S [ip address] -d master -Q "SELECT name FROM sysobjects"

It also hung, so the trusted vs. untrusted connection doesn't seem to
make a difference.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Sun, 2 Mar 2008 12:26:10 -0800, "William Vaughn"
wrote in
:

>Are you sure the IP port is 1433? With dynamic port assignments it's usually
>not. Check Configuration Manager to see what port is assigned to the SQL
>Server instance you're trying to connect to.

I'm using 2000, not 2005. Where can I find this in 2000? Note that I
can telnet to the server on port 1433 and connect.

Thanks,
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

You're right. Dynamic IPs is a 2005 (and later) feature.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Charles Calvert" wrote in message

> On Sun, 2 Mar 2008 12:26:10 -0800, "William Vaughn"
> wrote in
> :
>
>>Are you sure the IP port is 1433? With dynamic port assignments it's
>>usually
>>not. Check Configuration Manager to see what port is assigned to the SQL
>>Server instance you're trying to connect to.
>
> I'm using 2000, not 2005. Where can I find this in 2000? Note that I
> can telnet to the server on port 1433 and connect.
>
> Thanks,
> --
> Charles Calvert | Software Design/Development
> Celtic Wolf, Inc. | Project Management
> http://www.celticwolf.com/ | Technical Writing
> (703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

Sorry. I can't think of any more troubleshooting steps short of calling
Customer Support.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

"Charles Calvert" wrote in message

> On Fri, 29 Feb 2008 09:25:21 -0800, "Rick Byham, \(MSFT\)"
> wrote in
> :
>
>>Have you checked for other errors in both the Windows event log and the
>>SQL
>>Server log particularly on the SQL Server computer?
>
> I did and found nothing.
>
>>In Management Studio on the SQL Server computer, connect Object Explorer,
>>right-click the server name, and then click Properties.
>>On the Security tab, select login auditing; Both failed and successful
>>logins. Try connecting again, and check both logs.
>
> I'm using 2000, but found the equivalent setting in Enterprise
> Manager, set it, restarted and tried again.
>
> The login was recorded as being successful in the server's event log
> and the SQL Server log. Even after terminating the hung Query
> Analyzer process on ClientB, there were no errors in the server's
> event log or SQL Server log.
>
> I have noted one thing; when Query Analyzer gets hung on ClientB, the
> server can't seem to create any new Windows Explorer windows or
> Management Console windows (such as the event viewer) until the Query
> Analyzer process is terminated. This makes me wonder if SQL Server is
> locking a mutex or other synchronization object on the server and not
> letting go until the connection has completed.
>
> I had another idea. I tried the following:
>
> osql -E -S [ip address] -d master -Q "SELECT name FROM sysobjects"
>
> It also hung, so the trusted vs. untrusted connection doesn't seem to
> make a difference.
> --
> Charles Calvert | Software Design/Development
> Celtic Wolf, Inc. | Project Management
> http://www.celticwolf.com/ | Technical Writing
> (703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Thu, 6 Mar 2008 12:38:48 -0800, "Rick Byham, \(MSFT\)"
wrote in
:

>Sorry. I can't think of any more troubleshooting steps short of calling
>Customer Support.

I thought of something else - I installed SQL Server 2005 Express on
ClientB around the time that access via IP stopped working (access via
hostname never worked).

I'll uninstall that and see if it makes a difference.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Wed, 12 Mar 2008 21:27:10 -0400, Charles Calvert
wrote in :

>On Thu, 6 Mar 2008 12:38:48 -0800, "Rick Byham, \(MSFT\)"
> wrote in
> :
>
>>Sorry. I can't think of any more troubleshooting steps short of calling
>>Customer Support.
>
>I thought of something else - I installed SQL Server 2005 Express on
>ClientB around the time that access via IP stopped working (access via
>hostname never worked).
>
>I'll uninstall that and see if it makes a difference.

Nope. No change. I've uninstalled all of the 2005 and 2000 programs
and I still can't connect (tested by creating a DSN, since QA was
uninstalled).
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Thu, 13 Mar 2008 14:34:00 -0400, Charles Calvert
wrote in :

>On Wed, 12 Mar 2008 21:27:10 -0400, Charles Calvert
>wrote in :
>
>>On Thu, 6 Mar 2008 12:38:48 -0800, "Rick Byham, \(MSFT\)"
>> wrote in
>> :
>>
>>>Sorry. I can't think of any more troubleshooting steps short of calling
>>>Customer Support.
>>
>>I thought of something else - I installed SQL Server 2005 Express on
>>ClientB around the time that access via IP stopped working (access via
>>hostname never worked).
>>
>>I'll uninstall that and see if it makes a difference.
>
>Nope. No change. I've uninstalled all of the 2005 and 2000 programs
>and I still can't connect (tested by creating a DSN, since QA was
>uninstalled).

While thinking about this problem, I remembered that last year a
remote user was unable to connect to the server while VPNed into the
network. At the time I thought that it might be a VPN configuration
problem, but he was able to access all of the other servers on the
network.

I just tested this by remoting into a client on another network (we'll
call this one ClientC), VPNing back into my network from ClientC and
trying to connect to the server. I got exactly the same problem. So
now I have:

ClientA: on local network, can authenticate, can ping
ClientB: on local network, can not authenticate, can ping
ClientC: on remote network, can not authenticate, can ping

It seems that rather than having one client that can't authenticate, I
have only one that can. All seem to connect, but then the
authentication hangs for any client except ClientA.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Fri, 14 Mar 2008 13:00:43 -0400, Charles Calvert
wrote in :

>On Thu, 13 Mar 2008 14:34:00 -0400, Charles Calvert
>wrote in :
>
>>On Wed, 12 Mar 2008 21:27:10 -0400, Charles Calvert
>>wrote in :
>>
>>>On Thu, 6 Mar 2008 12:38:48 -0800, "Rick Byham, \(MSFT\)"
>>> wrote in
>>> :
>>>
>>>>Sorry. I can't think of any more troubleshooting steps short of calling
>>>>Customer Support.
>>>
>>>I thought of something else - I installed SQL Server 2005 Express on
>>>ClientB around the time that access via IP stopped working (access via
>>>hostname never worked).
>>>
>>>I'll uninstall that and see if it makes a difference.
>>
>>Nope. No change. I've uninstalled all of the 2005 and 2000 programs
>>and I still can't connect (tested by creating a DSN, since QA was
>>uninstalled).
>
>While thinking about this problem, I remembered that last year a
>remote user was unable to connect to the server while VPNed into the
>network. At the time I thought that it might be a VPN configuration
>problem, but he was able to access all of the other servers on the
>network.
>
>I just tested this by remoting into a client on another network (we'll
>call this one ClientC), VPNing back into my network from ClientC and
>trying to connect to the server. I got exactly the same problem. So
>now I have:
>
>ClientA: on local network, can authenticate, can ping
>ClientB: on local network, can not authenticate, can ping
>ClientC: on remote network, can not authenticate, can ping
>
>It seems that rather than having one client that can't authenticate, I
>have only one that can. All seem to connect, but then the
>authentication hangs for any client except ClientA.

I ran netstat to see if I could spot any differences between ClientA
(working) and ClientB (nonworking). 19.168.1.21 is the SQL Server.
Here is what I found:

ClientA (working)
-----------------
Proto Local Address Foreign Address State
TCP ClientA:1471 192.168.1.21:netbios-ssn ESTABLISHED


ClientB (nonworking)
--------------------
Proto Local Address Foreign Address State
TCP ClientB:1062 192.168.1.21:ms-sql-s ESTABLISHED
TCP ClientB:1080 192.168.1.21:netbios-ssn TIME_WAIT
TCP ClientB:1083 192.168.1.21:netbios-ssn TIME_WAIT
TCP ClientB:1085 192.168.1.21:netbios-ssn TIME_WAIT


Note that ClientA has no connections listed on port 1433 (ms-sql-s)
but works, while ClientB has a connection listed on port 1433 and
doesn't work.

For comparison purposes, I looked at a client and server belonging to
a customer of mine and that I know works (they're on separate
networks, so don't be fooled by the IPs). I got this:

Proto Local Address Foreign Address State
TCP Client:4899 192.168.1.30:ms-sql-s ESTABLISHED
TCP Client:4900 192.168.1.30:ms-sql-s ESTABLISHED


Finally, I fired up my laptop and was able to connect to my server.
When I checked netstat, I couldn't find any connections on port 1433
from the laptop either.

Is it me, or does it look like the server may be communicating over
named pipes or some mechanism other than TCP on port 1433?

I checked in the SQL Server Network Utility and both named pipes and
TCP/IP are in the "Enabled protocols" list. TCP/IP's "Default port"
property is 1433 and "Hide server" is not checked.

I disabled named pipes and restarted the server. Now on ClientA
(which was working before), I get the error:

Unable to connect to server <ip address>

Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access denied.

If I browse for available servers in the "Connect to SQL Server"
dialog of Query Analyzer, the server no longer appears.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem 

On Fri, 21 Mar 2008 12:39:38 -0400, Charles Calvert
wrote in :
>
>I checked in the SQL Server Network Utility and both named pipes and
>TCP/IP are in the "Enabled protocols" list. TCP/IP's "Default port"
>property is 1433 and "Hide server" is not checked.
>
>I disabled named pipes and restarted the server. Now on ClientA
>(which was working before), I get the error:
>
>Unable to connect to server <ip address>
>
>Server: Msg 17, Level 16, State 1
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
>or access denied.
>
>If I browse for available servers in the "Connect to SQL Server"
>dialog of Query Analyzer, the server no longer appears.

I uninstalled and reinstalled SQL Server on the server, only enabling
TCP/IP this time. Since the server runs W2K3, I had to install SP3.

This has gotten me nowhere. I still get the error listed above from
ClientA, which worked before I disabled named pipes.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research
 >> Stay informed about: SQL Server 2000 SP4 client connection problem