There is a problem with SQL Server SP2 that prevents its operation using TCP/IP. Port 1433 (or the configured port) is not open. If you check the application event log, you will find this nice message:
You are running a version of Microsoft SQL Server 2000 or Microsoft SQL Server 2000 Desktop Engine (also called MSDE) that has known security vulnerabilities when used in conjunction with this version of Windows. To reduce your computer's vulnerability to certain virus attacks, the TCP/IP and UDP network ports of Microsoft SQL Server 2000, MSDE, or both have been disabled. To enable these ports, you must install a patch, or the most recent service pack for Microsoft SQL Server 2000 or MSDE from http://www.microsoft.com/sql/downloads/default.asp
More information from this issue can be found in KB841375.