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 2 and 5 and type the answer here:
  • Post
  • 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)
  • Jinn,

    Error code 53 often indicates that the server is not reachable from the client machine.  If you run the following two commands on the client machine, what do you get (I assume the server is installed as a default intsnace):

    telnet <serverMachineName> 1433

    ping <serverMachineName>
  • Srinivas,

    Are you able to retrieve the Number member of the SqlError objact in the SqlErrorCollection contained in the SqlException thrown?  
  • Does anybody know how to fix the following:

    Unable to complete login process due to delay in
    prelogin response.

    This seems to happen every 1-2 hrs and we have to restart the SQL service, then it is fine.

    We usually use tcp/ip for our connections.

    When the server starts refusing connection on tcp/ip, we can still connect using namedpipes or the dedicated admin port (tcp 1434).

    A few sites mention the erro but no-one can suggest a fix or even further routes for investigation.

    Thanks in advance
  • "An error has occurred while establishing 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: HTTP Provider, error: 0 - )"

    This occurs randomly, but always under heavy _client_ load. I suppose client first tries some other "provider", but fails, and HTTP provider isn't configured.
  • I am getting the following error when trying to run an ASP.NET 2.0 application using a connection to SQL Server 2000,

    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)

    I don't understand why the application is trying to connect to SQL 2005 server when it is not specified.

    Thanks.
  • 1) Were you trying to make local or remote connection?
    2) What does your connection string looks like, please try removing "lpc:" prefix if you specified.
    3) Run *cliconfg* in the command line, check off box "enable shared memory protocol".
    4) Double check whether your SQL 2000 instance started successfully, and listen on shared memory / named pipe/ tcp from server errorlog.

    If you still face problem, please provide your connection string and server errorlog.

    Good Luck!
    Ming.

  • Additional note, this error message tends to confuse alot of customers with the SQL 2005 part.   The error message can occur when you attempt to connect to ANY version of SQL Server, it does not mean that the driver is trying to connect to some SQL 2005 server.
  • If you are connection to a local sql server using SqlClient try using Server Name as '.' this worked for me.

    But i am not able to connect to the remote server running sql server 2000. I also tried modifying the HOSTS file still not able to work out. Please drop a mail on my website if someone finds something. thanks.
  • I had problems connecting to my SQL Server edition that came with Visual Web Developed 2005 Express Edition version. The error was that a remote connection could not established error: 40. I solved it by opening SQL Server Configuration Manager explan SQL Server 2005 Network Configuration clicked on Protocols for SQLEXPRESS to find that Shared Memory was enabled but TCP/IP wasnt and once I enabled it, the connection was established.
    The server is running in the same machine as the development environment.
    One additional thing to note is that you should give <<server or machine name>>\SQLEXPRESS as your data source while trying to add a connection
  • Hi Ming,

    I get the error:
    [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.

    followed by several these other errors
    [sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available.
    [sqsrvres] printODBCError: sqlstate = 08S01; native error = 40; message = [Microsoft][SQL Native Client]Communication link failure
    [sqsrvres] OnlineThread: QP is not online.
    [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed

    and it looked to me like it is potentially related to :  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)

    that you mentioned from above i was wondering what type of network problems could be affecting my cluster?  I have disabled Netbios/wins and only have Named Pipes and TCPip on the server...also when i shut down the passive node on my cluster the errors will still pop up... I assume the issue has something to do with the heartbeat on the active machine but thus far have been unable to determine the cause of the error.  Also to note...when i enable netbios/wins the problems happen more frequently  from once a day to multiple times an hour.  Using server 2003 x64 sp1 and sql 2005 sp1 x64

    Thanks
  • I want immediate answer on this. Plz help
  • Hi Ming,
    Me too trying to connect to SQlServer 2000
    But am getting the same error.Could u plz tell how to solve it! I tried checking off "enable shared memory protocol".But it didnt worked out

    Thanks in advance
  • Hi,Anita

      Did you get error: "TCP provider: An existing connection was forcibly closed by the remote host"? Did you make remote connection or local connection. To solve this: you need check several things:

    Server side:

    1)Go to services.msc, see whether you sql 2000 was started successfully.
    2) Go to server error log, see whether TCP was enabled and server is listening on <ip>,<port>
    3) netstat -ano | findstr "<port>" double check server was listening on the exact port
    4) If you were making remote connection, double check <port> is in the firewall exception list.

    Client Side:
    5) Try telnet <serverip> <port> see whether it succeeds.
    6) Try change your connection string if you have "tcp:" prefix, remove the prefix,see what happens.
    7) Try change your connection string to " Server = <machinename>[\<Instance>],<serverport>/ or Data Source = <machinename>[\<Instance>],<serverport>"

    If it is local connection, <machinename> is local hostname, if it is remote connection, <machinename> is remot host name; <port> is the portnumber that server was listening on; <Instance> is the instance name of your sql server, your sql 2000 is default instance or named instance? If default instance, the port should be 1433, if named instance, see the errorlog to check.

    If you have further question:
    1) Please give me what error you saw?
    2) Your connection string?
    3) Client/Server OS?
    4) server errorlog?
    5) local/remote connection?

    Good Luck!
    Ming.
       
  • Hi, Sonu

       Sorry, I did not see the question you posted? Could you give more specific info?

    Thanks!
    Ming.
Page 3 of 17 (251 items) 12345»