This is an exhaustive list of troubleshooting techniques to use when you cannot connect to the SQL Server Database Engine. These steps are not in the order of the most likely problems which you probably already tried. These steps
are in order of the most basic problems to more complex problems. These steps assume that you are connecting to SQL Server from another computer by using the TCP/IP protocol, which is the most common situation. These steps are written for SQL Server 2008 R2
with a client running Windows 7, however the steps generally apply to other versions of SQL Server and other operating systems with only slight modifications.
These instructions are particularly useful when troubleshooting the "Connect to Server" error, which can be Error Number: 11001 (or 53), Severity: 20, State: 0
This error usually means that the SQL Server computer can't be found or that the TCP port number is either not known, or is not the correct port number, or is blocked by a firewall.
Note: IP address 127.0.0.1 is probably listed. It is called the loopback adapter address and can only be connected to from processes on the same computer. It can be useful for troubleshooting, but you can’t use it to connect from another
In many installations of SQL Server, connecting to the Database Engine from another computer is not enabled unless an administrator uses Configuration Manager to enable it. To enable connections from another computer:
Connecting to SQL Server by using TCP/IP requires that Windows can establish the connection.
Before troubleshooting a connection problem from another computer, first test your ability to connect from a client application on the computer that is running SQL Server. This procedure uses SQL Server Management
Studio. Management Studio might not have been installed when you installed the Database Engine. You can install Management Studio from the SQL Server CD by running setup and selecting the Management Tools option. If you are running SQL Server Express, you
can download the free SQL Server Management Studio Express from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b.
(If Management Studio is not available you can test the connection using the sqlcmd.exe utility which is installed with the Database Engine.)
Logon to the computer where SQL Server is installed, using a login that has permission to access SQL Server. (SQL Server 2008 installation requires at least one login to be specified as a SQL Server Administrator. If you do not
know an administrator, see Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.)
On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.
In the Connect to Server dialog box, in the Server type box, select Database Engine. In the Authentication box, select Windows Authentication.
In the Server name box, type one of the following:
The computer name
The computer name\instance name
Note: When connecting to a SQL Server from a client application on the same computer, the shared memory protocol is used. Shared memory is a type of local named pipe, so sometimes errors regarding pipes are encountered.
If you receive an error at this point, you will have to resolve it before proceeding. There are many possible things that could be a problem. Your login might not be authorized to connect. Your default database might be missing.
Note: Some error messages passed to the client intentionally do not give enough information to troubleshoot the problem. This is a security feature to avoid providing an attacker with information about SQL Server.
To view the complete information about the error, look in the SQL Server error log. The details are provided there. If you are receiving error 18456 "Login failed for user", Books Online topic http://msdn.microsoft.com/en-us/library/cc645917.aspx contains
additional information about error codes. And Aaron Bertrand's blog has a very extensive list of error codes athttp://www2.sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx.
If you can connect using shared memory, test connecting using TCP. You can force a TCP connection by specifying tcp: before the name. For example:
tcp: The computer name
tcp: The computer name/instance name
If you can connect with shared memory but not TCP, then you must fix the TCP problem. The most likely issue is that TCP is not enabled. To enable TCP, See the Enable Protocols steps above.
If your goal is to connect with an account other than an administrator account, once you can connect as an administrator, try the connection again using the Windows Authentication login or the SQL Server Authentication login that
the client application will be using.
Beginning with Windows XP Service Pack 2, the Windows firewall is turned on and will block connections from another computer. To connect using TCP/IP from another computer, on the SQL Server computer you must configure the firewall
to allow connections to the TCP port used by the Database Engine. If you are connecting to a named instance or a port other than TCP port 1433, you must also open the UDP port 1434 for the SQL Server Browser service. For step by step instruction on opening
a port in the Windows firewall, see How to: Configure a Windows Firewall for Database Engine Access.
Once you can connect using TCP on the same computer, it's time to try connecting from the client computer. You could theoretically use any client application, but to avoid additional complexity, install the SQL Server Management
tools on the client and make the attempt using SQL Server Management Studio.
1. On the client computer, using SQL Server Management Studio, attempt to connect using the IP Address and the TCP port number in the format IP address comma port number. For example, 192.168.1.101,1433 If this
doesn't work, then you probably have one of the following problems:
2. Once you can connect using the IP address and port number, attempt to connect using the IP address without a port number. For a default instance, just use the IP address. For a named instance, use the IP address and the instance
name in the format IP address backslash instance name, for example 192.168.1.101\PAYROLL If this doesn't work, then you probably have one of the following problems:
Both of these problems are related to the SQL Server Browser service, which provides the port number to the client. The solutions are:
3. Once you can connect using the IP address (or IP address and instance name), attempt to connect using the computer name (or computer name and instance name). Put tcp: in front of the computer name to force
a TCP/IP connection. For example, for a default instance use something like tcp:ACCNT27 For a named instance usesomething like tcp:ACCNT27\PAYROLL If you could connect using the IP address but not using the computer name,
then you have a name resolution problem. Go back to the section Testing TCP/IP Connectivity, section 4.
4. Once you can connect using the computer name forcing TCP, attempt connecting using the computer name but not forcing TCP. For example, for a default instance use just the computer name such as ACCNT27 For a
named instance use the computer name and instance name like ACCNT27\PAYROLL If you could connect using whileforcing TCP, but not without forcing TCP, then the client is probably using another protocol (such as named pipes).
a. On the client computer, using SQL Server Configuration Manager, in the left-pane expand SQL Native Client 10.0 Configuration, and then select Client Protocols.
b. On the right-pane, Make sure TCP/IP is enabled. If TCP/IP is disabled, right-click TCP/IP and then click Enable.
c. Make sure that the protocol order for TCP/IP is a smaller number that the named pipes or VIA protocols. Generally you should leave Shared Memory as order 1 and TCP/IP as order 2. Shared memory is only used when the client and
SQL Server are running on the same computer. All enabled protocols are tried in order until one succeeds, except that shared memory is skipped when the connection is not to the same computer.
Another important place to find an extensive amount of SQL Server General & Database Engine related articles is the TechNet Wiki itself. The best entry point is SQL
Server General & Database Engine Resources on the TechNet Wiki