SQL connectivity issues troubleshooting Steps

As a DBA you may have seen\trying to fix connectivity issues of SQL. So, I thought to put some points here on resolving the connectivity issues to SQL Server, based on my experience I have given below basic steps to follow Below are the points I use to check when you failed to connect SQL Server :

  1. SQL Server configuration Issue
  2. Firewall Issue
  3. Network Issue
  4. Logon Issue
  5. Configuration Issue in Application

SQL Server configuration Issue :
–> Check SQL Server is running on the Machine and is listening on protocols.
–> You can check and enable protocols in SQL Server Configuration Manager.
–> SQL Server to connect remotely Named Pipes and TCP protocols are reQuired, make sure these are enabled
–> In SQL Server Errorlog we can check if server is listening on protocols or not.
–> Also make sure SQL Server Browser is running on the Machine is SQL Instance is a Named Instance.

Firewall Issue:
–> SQL connectivity reQuest can be blocked by Firewall between Client and Server.
–> To fix this we can DISABLE Firewall but it is Temperory Fix as in real time we can disable it Permanentely.
–> The Permanent fix for this is adding SQL Server and SQL Browser in Firewall Exception.
–> While adding the exception for SQL Server DB Engine provide the Port Number in which SQL is listening.
–> While adding exception for SQL Browser provide the UDP port 1434.

Network Issue :
–> The first thing to check for SQL connectivity is Ping response from your SOurce Server to Target Server.
–> You can also do Telnet ‘SQL ServerName’,PortNumber and UDL testing from client machine to SQL Server box.
–> check nslooup, if DNS entry is wrong that may cause the SQL connectivity issue.

logon Issue to SQL Server:
–> This is different from above all cases..This needs to troubleshoot based on the error message which we get from Application team.
–> As it is Login Connectivity issue to SQL, we have to study case by case.
–> If the Login reQuest is coming to SQL box and getting failed then the errors can be recorded in SQL ErrorLogs and event viewer of SQL box.
–> We can go through the logs and fix the issue.

Configuration Issue in Application :
–> If the configuration part in application is wrong then that also can lead to SQL connectivity Issue.
–> Check the connection String Parameters Provided in Application Config File.
–> Also check which Login Account Application is using to connect SQL
–> If it is SQL account check the SQL instance properties which Authentication Mode is Enabled.
–> And check what level of Permissions that Account has.