Saturday, October 20, 2007

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 :-


Blogger Chandran said...

My dear MVP
I have a connectivity problem with an SQL Express installation. I have made a Visual Studio 2005 application using an SQL Database. I have configured the surface area to allow remote connections. The Server Browser is running and the firewall has been turned off. The programme has a login form that connects to the database. That seems to be working properly but the next part of the programme starts with a Janus GridEx that is bound to a table in the same database. That is where the connection fails throwing an error that suggests that the default properties of the SQL Express Server does not allow remote connections. I hope I have made myself clear. Can you think of a solution?

Yours sincerely


7:14 PM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home