sql server 2k & win2k3 enterprise; usual solution not working 2

[tigerjade]

We just put SQL Server 2000 with SP3a on Windows Server 2003 Enterprise Edition. Everything's up & sez it's fine, but I can't connect to the SQL server instance through any remote Enterprise Managers.

I tried what KB article 827204 says, to disable & enable the TCP/IP & Named Pipe network libraries, but it didn't work. I've also set up aliases and updated the MDBC on the client machine to 2.8; still no dice. It keeps giving me the following error:

SQL Server Registration failed because of the connection failure displayed below.

SQL Server does not exist or access denied
ConnectionOpen (Connect())

What else can I do to access SQL server through an Enterprise Manager on another system?

Thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[mrdenny]

OK, lets do some checking of some stuff first. Can you telnet from a remote machine to port 1433 on the problem server?

Is there anything in the event log or ERRORLOG? It should tell you that it is listening on port 1433 on 127.0.0.1 and what ever IP(s) the NIC is configured for.

Is there a firewall between you and the server?

Can you telnet from the server in question to port 1433 on it's own IP?

Is this your first box with Windows 2003 or have you done this on Windows 2003 before? (Windows 2003 has some different security stuff).

Can you access the file shares on this machine (\\ServerName), how about pinging it?

Let me know what comes up, and I'll see if I can't point you in the correct direction.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tigerjade]

hey denny here we go:

1. nope, can't telnet to the 1433 port (and i've verified that it's using 1433 as its default port)

2. we might have something here. it only says, "SQL server listening on shared memory, named pipes," even though tcp/ip is enabled. i checked, and the server ip isn't static but dhcp-determined; we're going to change that & see if it makes a difference.

3. no firewalls

4. yes, can telnet to the server itself

5. this is our first win2k3 box. sigh.

6. i can access file shares on this machine, and can ping IT but can't ping the SQL server

i'll let you know what happens with the ip status change...

<crossing fingers>

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[tigerjade]

oh well. we switched the server to a static IP address but the error log still says "SQL server listening on shared memory, named pipes". i've also tried setting up an alias on my client machine that uses named pipes & points to the server, but no luck there either. i think you're on to something, though

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[timscronin]

Do you have both the server and client network utilities set to tcpip and multi protocol. Did you try to connect to the ip through query analyzer instead of server name?

 

[mrdenny]

I take it there was nothing in the error log about not being able to start listening on port 1433?

What shows up if you do a netstat -a -n?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tigerjade]

timscronin: tcp/ip, named pipes & multiprotocol are all enabled on both server & client network libraries. can you tell me how to connect to the ip through query analyzer?

mrdenny: the error log only says it's listening on shared memory & named pipes (and rpc when multiprotocol is enabled); it doesn't say anything either way about any ip address or the tcp/ip protocol.

when i ran netstat -a -n, port 1433 didn't show up at all, so it doesn't appear to have been opened. i checked the services file, and it is active as both a tcp & udp port, designated to sql server. where do we go from here?

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[timscronin]

simple, when you are prompted for the server name in query analyzer, just type the ip address of the sql server. Try both an NT/SQL account and a SQL Account. If this works, it sounds like the server name is not being broadcast through dns/wins. Tell me if this works and I can give you a few options to address it

 

[tigerjade]

nope, it flipped me off in query analyzer too; said the address was invalid. i tried it both with & without port 1433. it looks like sql server isn't using an ip address; is there some way i can kick it in the hiney?

thanks!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[timscronin]

On the client you are trying to connect from, do you know which version of MDAC you have. As a quick try you might try to install sql sp3a on the client. It sounds like sqltools are not able to resolve the sql server's ip. Usually if you can ping it, you can use qa to connect to it. The usual problem if you can't is that it tries to used named pipes, which are blocked by some infrastructure equipment.

 

[tigerjade]

i'm using mdac 2.8 on this client, and sp3a on the server. netstat on the server didn't show port 1433 open at all; that's why i think it's something on that end, rather than the client end. i can't ping the sql server either, although i can ping the main server it's installed on; that one's behaving just fine.

thanks for hanging in there with me!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[mrdenny]

We've definetly got a server issue here.

Is the personal firewall enabled for some reason? (Open the properties of the network connection for the nic, on the advanced tab) If you have more than one nic, or a dial up connection you'll want to check them as well.

I'm assuming that in the Network Settings of the SQL Server the TCP/IP is in the enabled protocols site with 1433 as the port, and hide is unchecked.

Are you sure that SP3a was fully installed correctly? Might want to run through that again.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tigerjade]

yay yay yay & yippee!!! reinstalling sp3a did the trick; all of a sudden, it's listening to the ip address & seeing the port. i had to re-download the sp from microsoft's website to do it right, though; must've gotten hold of a corrupted or incomplete version along the way.

thank you SO MUCH, mr denny & timscronin!

tigerjade

"Always code as if the person who ends up maintaining your code will be a violent psychopath who knows where you live." -- Martin Golding

 

[mrdenny]

I thought that might be it. SQL won't listen for remote connections if SP3 or SP3a is not fully installed. SQL Slammer protection.

Denny

--Anything is possible. All it takes is a little research. (Me)