Connecting to SQL Server 2000 on a remote machine
1) You need to install the Client Network Utilities located on the SQL Server 2000 CD.

2) Open the SQL Server Client Network Utility located at Start | All Programs | Microsoft SQL Server | Client Network Utility. The shortcut points to C:\WINDOWS\system32\cliconfg.exe so you can just type cliconfg from your command prompt. It should look like this.


3) Once this is done, Click on the Alias tab.

4) Click on the Add button on the right hand side. Fill in a Server Alias, Check the TCP/IP from the Network Libraries on the right hand side. In the connection parameters, fill in the server name (IP address). After you are done, it should look like this.



5) Click OK. Then Click Apply in the Client Network Utility window.


6) To make sure that the connection works, Open the SQL Query Analyzer. It should look like this.


7) Fill in the information and click ok. It should open a new window called Query -xxx.xxx.xxx.xxx.master.loginname - Untitled 1

If you get the following error :-

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

Or an error that has "ConnectionOpen (Connect())." in it then your SQL Server 2000 is not working properly. Make sure

1) you have the latest Service Pack installed. The problem I ran into was that Windows 2003 Server does not accept connections over IP for SQL Server 2000 and you need to install SQL Server 2000 Service Pack 3a. For more information look at this KB article at http://support.microsoft.com/default.aspx?scid=kb;en-us;884012

2) The account your using to connect is legal. Don't use the remote machines admin account or any windows account. I personally had a bunch of issues with this. Create a separate account for your database on the remote server and use that account.


Good luck to you all on this.
- posted by Sushant Bhatia @ 5:38 AM