Tuesday, April 10, 2012

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!

2 comments:

  1. How to Solve MS SQL Server Hostname Connectivity Issue? Contact to Microsoft SQL Server Support
    In some of the cases the MS SQL Server hostname connectivity issue occurs due to if string too long or string malformed. To tackle this problem chooses Cognegic’s Remote Infrastructure Management Support for Microsoft SQL Server or Online MS SQL Server Support. We are able to diagnose existing performance problem and also improved database performance and resource management. Our all technical experts are capable to configure, optimize and analyze storage engines.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  2. Bila membahas asyiknya permainan kartu online ini memang tiada batas. Namun ketahuilah kalau kamu akan selalu mendapatkan hal baru yang sebelumnya kamu tidak ketahui dari permainan judi online terpercaya. Dan dari sanalah istilah "konklusi" dimulai pada game (Baca Selengkapnya...)

    ReplyDelete