FAQ: SQL Server Connectivity Troubleshooting

Connectivity issues are very common in SQL Server 2005 since there are few settings to be done at server level and OS level to enable the server for remote connection. There are many documents available from Microsoft Protocol team gives step by step troubleshooting process. I always refer those Bolgs whenever I see such issues in forums. Then I thought to have a consolidated list where all the links are available

(a) Check whether the server is configured to accept remote connections. Use SAC to enable server for remote connection
.
(b) Ensure that the SQL Browser service is started (SQL Browser equivalent in 2000 is SQL Server Resolution Protocol (SSRP) )

(c) Determine whether clients are specifying the correct port (for using fixed ports with named instances) in the server alias or connection string.

(d) Check whether the client’s network protocols are enabled and configured to correctly handshake with those of the server.

(e) Be sure you have permission to connect on the server’s endpoints.

(f) When using encryption, be sure the server and client certificates match (that is, check their Common Name (CN) and any other relevant attributes) and are installed and configured correctly on both sides.

(g) Make certain that your firewalls are configured to permit the required network traffic.

(h) Check to see whether your users have permission to log in to the server and access the specified database.

(i) Make sure the provider, driver, DSN, server alias, or other connection mechanism is still valid and hasn’t been altered or removed from the system.

Refer following KBs and Blogs for more info :-
http://support.microsoft.com/kb/914277
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx