MSDE ODBC connection fails

I have 3 PCs on a LAN.
PC 1 has MSDE installed
PC 2 has MSDE installed
PC 3 doesn't have MSDE installed
I can set-up an ODBC connection through TCP/IP port and named pipes from PC
1 to PC 2 and vice versa.
The same TCP/IP ODBC details are used on PC 3 to connect to MSDE on PC 1 and
2 but the connection always fails. The named pipe connection works.
Originally PC 2 did not have MSDE installed and a TCP/IP ODBC connection
could not be made to PC 1 either. Once MSDE was installed the TCP/IP ODBC
connection worked.
Any ideas what the problem is here?
I know that by default MSDE is not set-up to allow access from a client
across the network but when I installed MSDE I used these switch commands to
allow it:
cd c:\MSDERelA
setup SAPWD="whatever" INSTANCENAME="PC1MSDE" TARGETDIR="C:\MSDE"
SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 /L*v C:/MSDELog.log
At first I thought perhaps MSDE was listening on a different port to what it
was reporting but the MSDE log that is generated when the service starts and
svrnetcn.exe both indicate the same socket is being used. I have tried using
a different port.
I notice that when MSDE is installed, there is an extra option in the ODBC
TCP/IP dialog window 'dynamically determine port' and wondered if this had
anything to do with the problem? However, it doesn't seem to make a
difference because PC 1 and PC 2 can connect to each other with or without
this option enabled.
I found this article http://support.microsoft.com/kb/306865 which explains
how SQL Server 2000 may not be listening on TCP/IP sockets but the registry
keys again indicate that the correct port is being used.
FYI, I am using MSDE 2000. PC2 has been upgraded to MSDE2000 SP4 but that
hasn't resolved the issue. All 3 PCs are W2K Pro so no firewall issues.
I'm a bit stuck as to what else to try.
Ha, ha, ha - sorted it!
I installed the latest MDAC from
http://www.microsoft.com/downloads/d...DisplayLang=en
on PC 3 which didn't have MSDE installed and I can now make an ODBC
connection to any MSDE on the network.
The Microsoft Access Data Components include an updated SQL Server ODBC
driver.
Good way to end a Friday afternoon.
"Swaffs" wrote:

> I have 3 PCs on a LAN.
> PC 1 has MSDE installed
> PC 2 has MSDE installed
> PC 3 doesn't have MSDE installed
> I can set-up an ODBC connection through TCP/IP port and named pipes from PC
> 1 to PC 2 and vice versa.
> The same TCP/IP ODBC details are used on PC 3 to connect to MSDE on PC 1 and
> 2 but the connection always fails. The named pipe connection works.
> Originally PC 2 did not have MSDE installed and a TCP/IP ODBC connection
> could not be made to PC 1 either. Once MSDE was installed the TCP/IP ODBC
> connection worked.
> Any ideas what the problem is here?
> I know that by default MSDE is not set-up to allow access from a client
> across the network but when I installed MSDE I used these switch commands to
> allow it:
> cd c:\MSDERelA
> setup SAPWD="whatever" INSTANCENAME="PC1MSDE" TARGETDIR="C:\MSDE"
> SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0 /L*v C:/MSDELog.log
>
> At first I thought perhaps MSDE was listening on a different port to what it
> was reporting but the MSDE log that is generated when the service starts and
> svrnetcn.exe both indicate the same socket is being used. I have tried using
> a different port.
> I notice that when MSDE is installed, there is an extra option in the ODBC
> TCP/IP dialog window 'dynamically determine port' and wondered if this had
> anything to do with the problem? However, it doesn't seem to make a
> difference because PC 1 and PC 2 can connect to each other with or without
> this option enabled.
> I found this article http://support.microsoft.com/kb/306865 which explains
> how SQL Server 2000 may not be listening on TCP/IP sockets but the registry
> keys again indicate that the correct port is being used.
> FYI, I am using MSDE 2000. PC2 has been upgraded to MSDE2000 SP4 but that
> hasn't resolved the issue. All 3 PCs are W2K Pro so no firewall issues.
> I'm a bit stuck as to what else to try.
>