One of the most common issue which many users face is that they cannot connect to the SQL Server instance or Database. You will receive a message similar to below while you try to connect to the SQL instance

Typically Users complain that ‘I can’t connect to instance X’. Below is one common error received during the connectivity failure. Other errors include Login Failed for user, Login Timeout expired

Error:
Cannot connect to server-name.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)


This error or connectivity failure to SQL Server instance can occur due to various reasons. Below are some troubleshooting steps to identify and fix the issue.

1. Make sure the SQL Server is UP and Running.

• RDP to the server, on the Start menu, point to All Programs, point to Microsoft SQL Server 2005/2008 R2/2012, point to Configuration Tools, and then click SQL Server Configuration Manager.
• Using Configuration Manager, in the left pane select SQL Server Services. In the right-pane confirm that the instance of the Database Engine is present and running.
• Confirm that the instance is running, by looking for the green arrow. If the instance has a red square, meaning it is stopped, ensure there is no maintenance on the server being performed.
• Check cluster manager for cluster servers to check if the SQL instance is up or not.
• The instance named MSSQLSERVER is a default (unnamed) instance. There can only be one default instance. Other (named) instances will have their names listed between the parentheses.
• Make a note of the name of the instance that you are trying to connect to.

2. If SQL Server is not stopped,

• If you are attempting to connect to a named instance, ensure that you specified the instance name correctly, which should look like server_name\instance_name or virtual_sql_network_name\instance_name, in case of connecting to cluster instance. To connect to default instance you just specify server_name or virtual_sql_network_name in case of cluster.
• Make sure the SQL Server Browser service is running Or
• While connecting, use the server name/IP address, along with the port number. Ex: server_name,1433. To find the port number on which SQL Server instance is listening on – Open SQL Server Configuration Manager, expand Services, and then click SQL Server instance – In the details pane, right-click the name of the instance, and then click Properties – Click the Advanced tab, and Verify Startup Parameters. -e has the fully qualified path for the error log file. Open the errorlog and look for message Server is listening on [ ‘any’ 1433]
• If above are fine, then make sure that the server is not hanging. To confirm, you can do a successful connection from inside (RDP) the server and from another machine. The SQL Server needs to have remote connections enabled for the latter.
• Make sure Firewall Allows connections between server where we are attempting the connection from to SQL Server over port SQL Server is listening on.

3. If the server is NOT hanging then, we need to narrow down why the user is not being able to connect.

• Does the user have a valid username/pass for this instance? Is this username NOT locked out?
• Does the user’s machine have connectivity to the SQL instance? A good way to see this is by setting up a Profiler session, this way you can see if the login attempts are even reaching the box.
• Is user is trying to explicitly access a database, does NOT have access to?
• Is the SQL Server going through a very high volume moment? Very high CPU or network overload might cause the login attempts to fail, this problem can be seen when the users tries multiple times and some of them fail and some succeed. In this case, check the cause of high volume
• If you are receiving error related to Login failed for user ‘‘then, check SQL errorlog and get the state of the error and see table below to know the cause of the login failed.
  Error: 18456, Severity: 14, State: 8

  ERROR STATE	ERROR DESCRIPTION
  2 and 5	Invalid userid
  6	Attempt to use a Windows login name with SQL Authentication
  7	Login disabled and password mismatch
  8	Password mismatch
  9	Invalid password
  11 and 12	Valid login but server access failure
  13	SQL Server service paused
  18	Change password required

4. If the connection only fails from application or from user’s computer, but works inside the server, then try the UDL test.

There can be various other reasons for the connectivity failures, if you came across any other issue, please leave a comment and I would update the blog, so that others can benefit.

Happy Reading,
SQLServerF1 team