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 5 and 1 and type the answer here:
  • Post
  • If the error message contains the string "SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified" it is most likely caused by one of the two following causes:

    (1) The SQL Browser process is not running on the server box.  

    and/or

    (2) The SQL Browser's UDP port 1434 is blocked by firewall.  You can grant exception either to UDP port 1434 or to SQL Browser process.  
  • Can someone please help me with this error.

    ===================================

    Cannot connect to CNF52307XV.

    ===================================

    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.) (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10061&LinkId=20476

    ------------------------------
    Error Number: 10061
    Severity: 20
    State: 0


    ------------------------------
    Program Location:

      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
      at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
      at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
      at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
      at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
      at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
      at System.Data.SqlClient.SqlConnection.Open()
      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

  • Shahid,

    This error suggests that (a) your client specifies to use TCP but (b) your server is not listening on the TCP protocol but (or at least not on its default port).  

    Regarding (a): the TCP protocol is most likely specified in the connection string or in an alias with the name of the target server.  Is your intent to use TCP?  

    If yes, we get to part (b).  Most likely you need to enable the TCP protocol on the server, and retsart the server.  Instructions on how to do this for SQL Express are in http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx.  The steps are the same for other SQL Server editions, just "Click on the Protocols for MSSQLSERVER" instead of "SQLEXPRESS" if your server is installed as a default instance (or for the appropriate instance name if it is a named instance).  

    Peter
  • Hi
    When i install Sqlserver Express it doesn't ask about Default or Instance Setup. After installing it connect as ServerNema\SQLEXPRESS
    I want to change connection name to just ServerName. What should i do?
    Thank you for helping me.
  • Hi, Saeid

     By default, SqlExpress was installed as a named instance and with TCP and Named Pipe are disabled.

     You can not change a named instance to a default instance, if you really want to use only servername in the connection string, you can spcify the tcp port like "Server=<machinename>,<port>" after you enabled Tcp; but if you only want to use shared memory or named pipe, we suggest you keep using "Server=<machinename>\sqlexpress".

    Here is useful info for SqlExpress

    http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
  • And if you really want to access it through Server=<machinename> you can create an alias through SQL Server Configuration Manager with the name of the machine connecting to your SQL Express.  

    Thanks,
    Peter
  • Has anyone tried accessing a SQL 2005 Enterprise from a local machine with Symantec Client Firewall (v 7.1.0) installed?  When trying to update diagrams locally, I get the following message with the firewall enabled:

    "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.) (.Net SqlClient Data Provider)"

    * * *

    Of course, when disabled, I am able to update diagrams.

    Does anyone know of a workaround to update diagrams locally with the firewall enabled?  Or, is that just a pipedream?

    Thanks.

  • Try specifying the server by its loopback IP address, 127.0.0.1 in the IPv4 case.  And let us know the result.  

    Thanks,

    Peter

  • Alternatively, you can use a protocol other than TCP (Shared Memory and/or Named Pipes) if you have it enabled on your SQL Server.  By default SqlClient first tries to use Shared Memory on a local machine.  
  • 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 am getting a very troublesome error using SQLExpresss 2005.  I had a developer write an application utilizing SQLExpress 2005, and I can connect to the server, view all the data (customers, orders, setup tables, etc.), I can connect to the database via Access (ODBC), everything works fine until I try and print an order.  Then it comes up and says "An error has occured while establishing a connections to the server.  When connecting to SQL Server 2005, this failure may be cuased 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).  I have enabled remote connections, both TCP/IP and named pipes, made sure that both loopback and actual machines IP address are not only active, but enabled, and like I said previously, am able to view & update data through application, but can seem to print.  Not sure what is going on.  ANY help would be greatly appreciated.  Thanks in advance.
  • You need to contact the developer who wrote the Access application.  The report you are trying to print is referencing an old linked table most likely.  Open the report in design view and select View|Properties and look at Record Source property, this might give you a clue.  But in general the report could be referencing an old linked table just about anywhere so you have to dig through the report in design view until you find it.
  • I also have this error message:

    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)

    ...And I disabled de firewall, added SQLSERVER to Firewall exceptions, executed aspnet_regsql.exe to add the instance and nothing... configuring in options of visual studio environment to setup the instance of SQL Server 2005... I have this backgrouds: I have installed SQL Server 2000 and Visual Studio .NET 2003 and I have not SQLEXPRESS Edition but SQL Server 2005

    What should I do??

    Thsnks a lot...
  • If you are trying to remote connection to Sql Server 2005, you need to enable your TCP/IP Protocol in your SQL Server Configuration Manager.

    SQL Server Configuration Manager
    -> SQl Server 2005 Network Configuration
      -> Protocols for <instance>

    After enabling the TCP/IP, you need to restart the SQL Server for the changes to be in affect.
  • When my PC connecting the SQL Server 2005,
    the Error occurred as follows:
    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 setting 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:53)

    But others pc could connect it!
    The TCP/IP is enabled state and Named Piped is enabled state
Page 2 of 17 (251 items) 12345»