Why I can not connect sql server ???

       Recently, I installed sql server 2012 on windows 7 laptop, today when I wanted to login sql server with TCP/IP, I got error.

1. Enable TCP protocol
I tried to login sql server by SSMS with Server Name "MachineName\InstanceName",  and select the Network protocol "TCP/IP"

but got error message below

My first thought was "TCP/IP" protocol had not been enabled. I opened SSCM, yes, it is not enabled.

I enabled the TCP protocol, restart the sql server

2. Add port in connection string
I tried to connect sql server again, but got the same error. Oh, you know what, I was connecting to the named instance,  I need to checked if SQL Server Browser service is enabled, all named instance will register its port in SQL Server Browser service when it startup. when client use MachineName\InstanceName to connect sql server, if there is no port specified,  client will talk with SQL Server Browser first to get the named instance port, then connect to that named instance by this port. if SQL Server Browser is disabled, you need to specify the port in connection string, so that client know how to talk with the named instance. for default instance, you don't need to spedify port if 1433 is default port.

If you use dynamic port, you can get the port info from errorlog(sql server will write the port to errorlog when it startup), or get it from SSCM

My SQL Browser service is disabled by default, So I use Server Name "MachineName\InstanceName, Port" in SSMS

3. Check Network
before I connected the server again, I try to verify the network setting was correct.
      a) Check errorlog to ensure the SQL Server is listening to the port
        Yes, I found it in the errorlog
2012-04-09 22:24:16.59 spid13s     Server is listening on [ 'any' <ipv4> 50909].

      b)netstat -a
          Yes, I can find the port opened
      c) Telnet MachineName Port
          Yes, I can telnet port 50909

looks like very thing was good! I connected to sql server again with Server Name "MachineName\InstanceName, Port"

Done! I connected to the sql server with TCP successfully!

4. sqlcmd connect issue
SSMS is fine now,  I tried sqlcmd with TCP , I run the command below:
sqlcmd -S TCP:MachineName\InsteanceName,port -E

but got error message:
Unable to complete login process due to delay in opening server connection".

so weird, SSMS was ok, why sqlcmd was failed, based on the error message, looked like it was a login timeout issue, I ping my laptop machine name, it took a long time to return......so I modified my sqlcmd command with l parameter

-l : Specifies the number of seconds before a sqlcmd login to the OLE DB provider times out when you try to connect to a server.The default time-out for login to sqlcmd is eight seconds.

8 second is too small, I need to change it with a bigger value 60

sqlcmd -S TCP:MachineName\InsteanceName,port -E -l60

ok, after a long waiting, I logined the sql server with sqlcmd.

5. Other troubleshooting method.
The above steps are only some general troubleshooting ways for the sql server connection issue. sometimes you need to test connection with more ways
       a) try to use different login authentication way
       b) try to login server with different protocol: TCP, NP, Shared Memory
       c) try to login sql server from different location: locally or remotely
       d) try to login sql server with different tools: SSMS, sqlcmd, ODBC client tools, OLEDB client tools
       e) if use windows authentication, check if use NTLM or Kerberos http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

       f) use different server name: hostname, 127.0.0.1, localhost, ipaddress, ".",
       g) some useful tools, like ping, telnet, netstat -a, hosts file
      
for some special cases, you need narrow down the problem with the ways I mentioned upper, then work with windows or network engineer to troubleshooting it.

normally, it is not difficult to solve connection issue , but if it can not be solved within a short time, it will be very tough issue!