An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
After a thorough search using Google, I found the MS SQL Express, Evaluation and Developer editions only allow to local connection, be default.
By setting the steps (from MSDN, using "SQL Server Surface Area Configuration") to allow it to make remote connection from remote PC, it works fines for some of the PCs only, using connection string likes:
Data Source=[pc name]\SQLEXPRESS; database=[database name]; uid=[user name]; pwd=[user password]
, when there's only MS SQL Express 2005 resides on PC.
But those PCs which have both MS SQL 2000 & MS SQL Express 2005 and the default instance is MS SQL 2000, it still hits the error message when I try to connect to MS SQL Express 2005:
SQL Server does not exist or access denied.
1) Go to: All Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager
2) Then under: SQL Server 2005 Network Configuration>Protocols for SQLEXPRESS
3) Double-click the "TCP/IP", under the "IP Addresses", find the "TCP Dynamic Ports".
4) Use the port number (I think port no. 1433 is used by default MS SQL instance) as part of the connection string likes:
5) Success!
Data Source=[pc name]\SQLEXPRESS, [port number] ; database=[database name =]; uid=[user name]; pwd=[user password]
5) Success!