SQL Server 2008 R2 General Troubleshooting Tips

 

 

This segment provides strategies for solving some communal connectivity issues. You can achieve the following steps as a first line of protection when your connections fail:

  1. Check whether the server is configured.
  2. Confirm that the SQL Browser service is started.
  3. Regulate whether clients are stipulating the correct port (for using fixed ports with named instances) in the server alias or connection string.
  4. Check whether the client's network protocols are allowed and configured to properly handshake with those of the server. They should use SSCM on both sides.
  5. Confirm you have permission to connect on the server's endpoints.
  6. While using encryption, ensure the server and client certificates match.
  7. Ensure that your firewalls are arranged to permit the required network traffic.
  8. Check to see whether your users have permission to log in to the server and access the identified database.
  9. Ensure that your clients' choices of workers support the SQL Server 2008 features they are trying to use.
  10. Ensure the provider, driver, DSN, server alias, or other connection mechanism is still valid and hasn't been altered or removed from the system.
  11. Network administrators are no longer added to the SQL Server sysadmin role by default. If the user trying to connect is a network administrator, he or she must be granted explicit permission with SQL Server 2008.



Connection Testing Tools

It's always supportive to have a few tools on your belt for testing client connectivity. SSCM is a tool that is typically easily available, and you may use its Connect to Server dialog to select a protocol to test .You can also use SQLCMD with the -S parameter to connect to a specific server. This is the syntax:

SQLCMD -Sprotocol_prefix\ServerName,PortNumber –E

In this syntax, protocol_prefix takes one of the subsequent values:

In the subsequent example, -E indicates the use of a trusted connection:

SQLCMD -Step:.\SQL08,1435 –E

When all else fails, you can use telnet to test the openness of a port on the firewall.

Here's an example:

telnet IP Address Port Number