18 Nisan 2012

SQL Server Slow With IP , Fast With Name And sqlcmd is not connecting with IP

ile Yunus KALDIRIM

Bu problem sql sunucusuna (2005, 2008) ip ile bağlanmaya kalktığımızda çok yavaş bağlantı kurulduğu veya sunucu adıyla bağlandığında çok hızlı bağlanması durumu ile ilgilidir. Ayrıca sqlcmd ile sunucuya bağlanamama veya ip ile bağlanmaya çalışılırken alınan aşağıdaki hata mesajlarının çözümüdür.

 

Symptoms:

  • Sqlcmd: Error: Microsoft SQL Native Client : Unable to complete login process du e to delay in opening server connection.
  • An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Login timeout expired”.

    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Unable to complete login process due to delay in login response”.

    An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “TCP Provider: Timeout error [258].

  • Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
  • Database connection failed by using IP address
  • Error message when you log on to an instance of Microsoft SQL Server 2005 from SQL Server Management Studio: “Timeout expired. This timeout period elapsed prior to completion of the operation or the server is not responding”
  • Connection to SQL Server Database Using IP Address Is Unusually Slow
  • sqlcmd Utility – Could not open a connection…

Solution

A. Windows (kb300420) To work around this problem, either provide some reliable means for performing a reverse lookup of the SQL Server server’s IP address, use the SQL Server machine name instead of the IP address, or apply this hotfix.

To use the HOSTS file or LMHOSTS, put the TCP/IP address of the SQL Server server at the start of the line followed by a few spaces followed by the machine name of the SQL Server server. For example:
123.123.123.123 myservername
The HOSTS or LMHOSTS file belongs in the System32\Drivers\Etc directory for client computers running Microsoft Windows NT and Microsoft Windows 2000, or in the \Windows directory for clients running Microsoft Windows ME, Microsoft Windows 98, or Microsoft Windows 95.

B. Add PTR reverse lookup records to DNS(http://technet.microsoft.com/en-us/library/cc961414.aspx)  or add ip to sql server’s host file. (B solution is working ,tested:)

C. Please be carefull to write sqlcmd -S IP, or sqlcmd -S hostname (big -S important!!!If you write low s it will give below error)

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.