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 1 and type the answer here:
  • Post
  • Changing the OSQL command switch from -S to -s -S fixed this issue for me.  Not sure why, but with -S server\instance we got the can't find instance error message but with -s it worked.

    Thanks to SqlRick who pointed this out early in the thread.
  • hi ming,
    I am getting the error pasted below when I connect to SQL express with SQL server authentication.
    Can u plz help?
    TITLE: Connect to Server
    ------------------------------

    Cannot connect to PS0654\SQLEXPRESS.

    ------------------------------
    ADDITIONAL INFORMATION:

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
  • Hi Ming,
    This is further to my previous query,I get the following error when i try to login with sa.

    Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

    Thanks
  • Kamate,
      "Cannot connect to PS0654\SQLEXPRESS. .."
    Mostlikely, this is because you are connect to the SQLExpress remotely and the remote protocol is not enable for sqlexpress. Please use SQL Server Surfuce Configuration Tool to enable remote protocol.

      "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)"

    What is you connection string, you can either use SQL login with UID and Password or use windows integrated authentication, not both. Make sure that SQLExpress authentication mode is "mix" and you have correct SA password is you decide to use SQL login.


  • Strange might it sound that, I have this issue with the SQL server connectivity from the .net application. The application seems to connect fine fromthe staging system. but throws up an exception from the development

    Error number  : 18452
    Error : User not associated with the SQL Server trusted connnection..

    Checked for the authentication its configured to mixed as mentioned in the microsoft website.  Is there any other option i am missing here for i am getting this error????

  • "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)"

    I have the same problem -- how does one specify "mix" for SQLExpress authentication mode? I already allow both remote and local connections  via the Surface Area Configuration -- and "Using both TCP/IP and Named Pipes" is set.

  • In SQL Server Management Studio, when I tried to connect to the Analysis Services database located on a remote server, it gives me the following error:
    A connection cannot be made.  Endure that the server is running.
    (Microsoft.AnalysisServices.AdomdClient)

    Additional Information:
    -- Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
     -- An existing connection was forcibly closed by the remote host (System)

    What should I do?  

    Thanks.
  • Hi, masaki

       The error indicated that
    1) you might have no TCP protocol enabled on remote server, double check SQL configuration manager, see whether remote server was successfully started.

    2) See server error log, check which tcp port server is listening on? Try telnet <remoteserver> <port>, and double check whether the port was added to firewall exception list.

    3) Try osql /S<remoteservername>,<port> /E, see whether it works? If so, open trace file see what operation cause server reset connection, and see eventlog and server errorlog to get clue.

    Good Luck!
    Ming.
  • Hi, marsaki

       I read some discussion about AS issue, so, here I collect helpful troubleshooting tips from those:

    1)Is this AS 2K or AS 2005?
    2)Have you enabled AS to accept remote connections (SAC utility - 2005)?
    3)How do you make connection? You connected through DNS name?
    4)Did you try ping your AS server or ip?
    5)Can you double check the following registry key?

    A. Locate the following key in the registry:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa


    B. Does this exist?

    Value name: DisableLoopbackCheck

    Data type: REG_DWORD

    Radix: Decimal

    Value: 1


    Good Luck!
    Ming.
  • OLE DB Provider returns error “General network error (80004005)” – Communication link failure when Job scheduler runs long taking DTS package via TSQL command. DTS package makes UPDATE against big table. This error can issue any time during long taking UPDATE. We have
    Win2k3 sp1 64-bit for IA, SQL 2005 EE 64-bit for IA.
  • Hi, Sergey

       Could you try enlarging query timeout?
  • Thank's a lot for you answer!
    We increased "general timeout" and "connect timeout. SQL server still drops local connection. We can see in server error log next record:
    Date 21.08.2006 12:39:20
    Log SQL Server (Current - 21.08.2006 12:51:00)

    Source spid60

    Message
    A fatal error occurred while reading the input stream from the network. The session will be terminated.
  • Hi, Sergey

       This is because your client send invalid request and got rejected by server. Could you describe more spcifically what you did?

    1) what is your client provider?
    2) what is your server, sql 2k5 or 2k?
    3) what is your client request?

    You can turn on sql trace to see which client operation caused this failure?

    Also, from error log, you should be able to see the error state, can you provide it?

    Thanks!
  • Hi i am new member of this fourm.I am also geting the following problem,Kindly help me to solve the problem

    Problem:

    I am developing one web application with vb.net, we have uploaded our pages in online with the sqlserver version 7. But some pages only showing the following error while view the page.But the online sql server also showing the same version.That we tested. Kindly help to solve the problem and aslo let me know how to post new topic.

    Error:

    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)

  • Hi,

     Please see the following blog to troubleshoot error 40:

     http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

    If you still face problem, please go through our questions w/ connectivity issue:

    http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

    Thanks!
    Ming.

Page 5 of 17 (251 items) «34567»