SQL Server 2005 connectivity error messages

SQL Server 2005 connectivity error messages

One of the more visible changes we made in connectivity space for SQL Server 2005 was enhancing error messages reported to the user in case of connection failures.  I have seen several cases over the last several months, and in this post I would like to list some of them with the root cause that caused them.  The cause may not be the only one triggering a particular error message, and there may be other error messages of interest. 

This is a semi-random selection from real cases I investigated.  Likely I will post another batch some time in the future, and I would be happy to see replies with additional errors, particularly if the root cause is unclear. 

The examples are from various client stacks – ODBC or OLEDB from SQL Native Client or from managed SqlClient. 

The exact formatting of the messages will depend on the application you use.  Most of the examples below used OSQL, SQLCMD, or SQL Server Management Studio. 

  • Connecting to a server by the server’s name from SqlClient, the server name is aliased to TCP, the server is up and running but it does not listen on TCP (or is not running at all):

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. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

  • Local connection from SqlClient; server is not running:

(a) default instance:

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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

(b) named instance:

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. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

  • Remote connection from ODBC, Windows Firewall is turned on on the server machine but there is an exception for File and Printer Sharing:

 [SQL Native Client]Unable to complete login process due to delay in opening server connection

  • A successful TCP connection from SqlClient to the server was broken due to a TCP keep-alive heartbeat failure (typically indicating problems with the underlying network infrastructure):

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Possibly:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

  • OBDC connection attempt when server is not ready to process a new local connection, possibly due to overload: 

[SQL Native Client]Shared Memory Provider: Timeout error [258].
[SQL Native Client]Login timeout expired
[SQL Native Client]Unable to complete login process due to delay in prelogin response

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQL Server [121].
[SQL Native Client]Login timeout expired
[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.

  • There is a space after server name in the connection string:

osql -E -S "<serverName> ,1433"
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The server ERRORLOG/EventLog shows something similar to:

2005-08-11 12:46:04.29 Logon       Error: 17806, Severity: 20, State: 2.
2005-08-11 12:46:04.29 Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: <IP address>]
2005-08-11 12:46:04.29 Logon       Error: 18452, Severity: 14, State: 1.
2005-08-11 12:46:04.29 Logon       Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: <IP address>]

  • Remote OLEDB connection using TCP to a server that is blocked by Firewall

HResult 0x274C, Level 16, State 1
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

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..
Error: Microsoft SQL Native Client : Login timeout expired.

  • Remote OLEDB connection using TCP to a server that is either not running or does not have TCP/IP protocol enabled for incoming connections (but is not blocked by the Firewall on the server machine):

HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively refused it.

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..
Error: Microsoft SQL Native Client : Login timeout expired.

Peter Gvozdjak, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
  • i have the error of instance failure while i am connecting to the sql server 05.i need resolution for that.

  • Hello All,

    I got an similar error that you mentioned above:

    2010-07-06 11:16:58.90 Logon       Error: 17806, Severity: 20, State: 2.

    2010-07-06 11:16:58.90 Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 164.4.57.61]

    2010-07-06 11:16:58.90 Logon       Error: 18452, Severity: 14, State: 1.

    2010-07-06 11:16:58.90 Logon       Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 164.4.57.61]

    But I´m trying to find out the solution in the links provided but  this Dosent has the solution, kindly provide an assistance...

  • Arrived at work this morning with panic breaking loose and there was the following message within the event viewer on the sql server machine:

    Event Type: Error

    Event Source: MSSQL$SQLEXPRESS

    Event Category: (4)

    Event ID: 17827

    Date: 15/09/2010

    Time: 22:06:11

    User: N/A

    Computer: DATASERV1

    Description:

    There was a failure while attempting to encrypt a password. The connection has been closed. [CLIENT: <local machine>]

    For more information, see Help and Support Center at go.microsoft.com/.../events.asp.

    Data:

    0000: a3 45 00 00 14 00 00 00   £E......

    0008: 15 00 00 00 44 00 41 00   ....D.A.

    0010: 54 00 41 00 53 00 45 00   T.A.S.E.

    0018: 52 00 56 00 31 00 5c 00   R.V.1.\.

    0020: 53 00 51 00 4c 00 45 00   S.Q.L.E.

    0028: 58 00 50 00 52 00 45 00   X.P.R.E.

    0030: 53 00 53 00 00 00 00 00   S.S.....

    0038: 00 00                     ..      

    There was a failure while attempting to encrypt a password. The connection has been closed. [CLIENT: <local machine>]

    It started at a seemingly random time last night?  Its a SQLEXPRESS 2005 install.  A reboot sorted it but am trying to find out the cause?!?!

  • My solution for login errors:

    SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. AND Login failed for user ''. The user is not associated with a trusted SQL Server connection.

    1. Logoff all remote users from that server (i used "tsadmin" for that)

    2. Connect to SQL instance again

  • 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

  • Yes. It works!!!! Thanks so much, Cheng!!

    Christopher Cheng 21 Mar 2006 12:54 AM

    For those who gave up, try one more thing:

    Start "SQL Server Configuration Manager",

    In "SQL Server 2005 Network Configuration" > "Protocols for SQLEXPRESS", Click on the tab "IP Addresses", Change all TCP Port to 1433.

    Yes, I mean all, even "IPAll", then it will work!

  • I just stumbled upon your blog after reading your blog posts wanted to say thanks.i highly appreciate the blogger for doing this effort.

  • My sql server 2005 showing error no 40 during connectinge with server.

  • Suddenly my site was not responding and took long time when opened with a new browser,

    then I was nothing to do that I just check server even for any error information, there saw the error with a below detail,

    "OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01; Shared Memory Provider: No process is on the other end of the pipe."

    then I moved to SSMS and try to connect server for my verification (I have named instance, SSAS also in the same server) it was worked fine then I realized there were nothing to do with server. but I managed to restart all the server related services.

    even that I was not resolved the issue, I restarted IIS Admin Services and reopen the site  again

    Wondered, it was work for me.

  • when i am trying to install a 3d software sql is not installing its giving following code "Error code :  -2068578304(0)" .What would be the reason for this. Please some one help me to resolve this issue.

  • •Remote OLEDB connection using TCP to a server that is blocked by Firewall

    In this who is the culprit the server in which App is hosted or the one in which DB is hosted.?

Page 17 of 17 (251 items) «1314151617