Re: SQL Server 2000 SP4 client connection problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

When you attempted to connect using the IP address, did you attempt to connect to: 192.168.1.21,1043
(As a side note, the 192.168.0.0 range of addresses is a private address for Network Address Translation (NAT). You will not be able to connect to that address through a router. However, since you can connect to the Internet Server, you must be on the same subnet, so that is probably not causing the problem.)
(As another aside, port number 1043 is assigned to "BOINC Client Control" by the Internet Assigned Numbers Authority {see http://www.iana.org/assignments/port-numbers} so if you have a BOINC client {whatever that is} that could cause a problem. This is unlikely.)

Let's try to resolve the local TCP connection attempt first, as it should have fewer components to mess up.
Locally (on the same computer as the SQL Server) try forcing a TCP connection through the loopback connector (127.0.0.1) which is the TCP simplest code path by connecting to: TCP:127.0.0.1,1043
If SQL Server is listening on TCP and if 127.0.0.1 is enabled, and if it's listening on port 1043, then it's hard to see how this could fail.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

"Charles Calvert" <cbciv@xxxxxxxxx> wrote in message news:hbspu3hn2m5dl26qltlldi36ovdt9resl9@xxxxxxxxxx
On Wed, 26 Mar 2008 08:49:40 -0700, "Rick Byham, \(MSFT\)"
<rickbyh@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
<2CBB84D5-E5A9-48B4-BE95-C035F4CD729F@xxxxxxxxxxxxx>:

It's difficult to offer more advice since you have checked so much stuff
already, but I don't want to ignore you when you can't connect. I think it's
time to review everything again. Of course I understand if you don't want
to.

No, I'm happy to do so. Thanks for your help.

I think that the problem is that TCP has never worked, but ClientA was
able to connect through named pipes, while ClientB was not. Both
methods were enabled on the server and all clients, with TCP taking
precedence. As soon as I disabled named pipes, I could no longer
connect from any client. My goal is now to make TCP work, since I
need to have people access it over the VPN without giving them local
accounts on the server (I'm running a workgroup, not a domain).

Since you will be connecting using TCP I will ignore named pipes.
On the server:
- Is the SQL Server Service started?

According to both the Services Manager and the Services MMC panel,
yes.

- Can you connect to SQL Server locally?

No, now that named pipes have been disabled. I get the same errors
locally that I do on the clients. The error is:

Unable to connect to server SERVERNAME:

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

Note that when attempting to connect locally, it says [Shared Memory]
instead of [DNETLIB]. That's interesting, because while the server
log indicates that it is monitoring shared memory, the client network
utility does not have shared memory enabled.

- Does the SQL Server error log say that SQL Server is now listening on TCP
port 1433? If another port number what? The expected entry should read: "SQL
server listening on <IP address>: <port number>."

Yes, though I'd been working with someone from Microsoft and we
switched to dynamic ports as a test and it's now listening on 1043
instead. It lists both the static IP of the server (e.g.
192.168.1.21) and the loop-back address (127.0.0.1).

- Does the Windows firewall have an exception for TCP port 1433? (or
whatever port is being used) (or is the firewall stopped)

Windows firewall is disabled. I determined this by looking at the
properties of the network connection, going to the Advanced tab and
clicking on Settings. I got the message:

Windows Firewall cannot run because the Windows Firewall/Internet
Connection Sharing (ICS) service is not running. To use the Windows
Firewall, you must start the Windows Firewall/ICS service. If you
configure exceptions for applications or services that were running
before you started Windows Firewall, you might have to restart your
computer so that these applications and services run properly. Do you
want to start the Windows Firewall/ICS service?

I have always answered "No" to that question, as I am running
ZoneAlarm instead. Note that I disabled ZoneAlarm during my tests,
both on the server and the clients, to make sure that it wasn't
causing the problem.

- Does the firewall have any other restrictions, like only allowing
connections from certain computers or subnets?

N/A.

- Is there another firewall?

Yes, ZoneAlarm, but it is disabled, as I mentioned above. If
necessary, I'll even uninstall it.

- Was SQL Server 2005 or SQL Server 2008 ever installed on this computer? If
so you may have a SQL Server Browser issue.

No. I installed the OS (W2K3) and then SQL Server 2000. I've never
upgraded or downgraded.

- Are there any interesting errors in the SQL Server error log or the
Windows event log?

No. In fact, there are no log entries since the server finished
starting up.


On the Client:
- Can the client successfully ping the TCP address of the server?

Yes.

- Can you connect without resolving the computer name, but just using the IP
address and port in the format: <computer name>,<port number> like
155.155.96.94,1433 ?

No, I get the same error.

On a side note, I am also running IIS on the server, and I can connect
to that successfully both locally and from the clients.

- On the client, are there any client aliases that may be redirecting the
connection?

No.

- Is the client connection attempt using the same credentials as the
successful local connection?

Yes, I had been trying with the sa account to be sure.

- Are you getting any errors except "SQL Server does not exist or access
denied."?

No.

- Are there any interesting errors in the Windows event log?

The only thing I found was from the server and the event ID was 19011,
description "SuperSocket info: (SpnRegister): Error 1355." I have no
idea what this means.

I should also point out that with named pipes disabled, SQL Server
Agent cannot connect to the server either.

If it weren't for the fact that IIS works, I'd suspect some sort of
sockets problem on the server. Since it does work, I don't know. I
think that my next step may be to format the drive and reinstall. It's
a development server, not a production one, so it won't be too big of
a pain.
--
Charles Calvert | Software Design/Development
Celtic Wolf, Inc. | Project Management
http://www.celticwolf.com/ | Technical Writing
(703) 580-0210 | Research

.

Relevant Pages

  • Re: What is the minimum-size UDP packet?
    ... > just keep the TCP connection in place at all times. ... TCP, 1 for UDP) identical to the one you are developing. ... TCP server multi-threaded if you haven't done so. ... of clients, sending/receieving for each client as needed). ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: What is the minimum-size UDP packet?
    ... > just keep the TCP connection in place at all times. ... TCP, 1 for UDP) identical to the one you are developing. ... TCP server multi-threaded if you haven't done so. ... of clients, sending/receieving for each client as needed). ...
    (microsoft.public.win2000.networking)
  • Re: Help with deceiding on best solution
    ... the client is always establishing the connection. ... it *will* establish a TCP connection to interact with a Web server. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Inconsistency in TCP sockets behavior between Windows and Linux
    ... TCP does not guarantee this ... An RST is an indication that the connection should be abandoned as soon as ... There are client and server applications. ... Server creates a listening socket and waits for incoming TCP connection. ...
    (microsoft.public.win32.programmer.networks)
  • Re: .Net Scalability problem
    ... LoadRunner will peak out a server with a few virtual users. ... To get an idea of load, ... Fire off the test client and watch the number of ... > So I think that the MTC generate concurrent connection and per ...
    (microsoft.public.dotnet.framework.adonet)