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 6 and 7 and type the answer here:
  • Post
  • What is the value of this blog? You've posted the error messages - likely the messages we are seeing - but there are no suggested resolutions.
  • This is a quick list compiled from the early Beta experience with SQL Server 2005. To get it out quickly I tried to list at least the root cause. I plan to update it with resolutions and more error messages moving forward.

    If you encountered any specific message, can you let me know?

    I will be glad to work with you on resolution, an updating this blog accoprdingly.

    You may also want to check additional blogs posted by Ming Lu from our team, which contain resolution to many of the connectivity problems:

    http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

    http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

  • I've been getting the following message when trying to run Management Studio Express and also a similar error with Altiris Depoloyment Solutions for Clients 6.5 trying yo connect to SQL Server Express.

    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)

    My TCP/IP and Named Pipes are both enabled, I've reinstalled everything several times with no luck. I'm just a Tech, and still learning my way around databases so there might be something simple that I missed. :P My boss has only had a couple minutes to help me out and he couldn't come up with anything. Any info you could give would be greatly appreciated, and I will keep you updated as to my failures/success so you can help build your FAQ.
  • Error[provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it] indicates that your TCP/IP was not enabled or your sql server was not listening on the appropriate port.
    1) After installing SQL Express, by default, TCP/IP was disabled, please see http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx to enable remote connection for SQL Express.
    2) Please check errorlog to make sure server TCP was enabled.
    - Key words indicates server TCP/IP is enabled –

    Server is listening on ['any' <ipv4> <Port Number>] or

    [ <ipaddress> <ipv4> <Port Number>].

    First one is when enabling server “ListenonAllIPs”, and second one is when server listening on individual IP. To further verification, you can test whether sql server is listening on the exact port using "netstat -ano| findstr <PortNumber>".

    3)Try to connect to the exact port that server was listening on and see what happens.

    If you still face the same problem, please attach your errorlog info and your connection string(eg, you connect through "."/"(local)"/<hostname>, etc.. and whether you specified any protocol prefix(eg, lpc:/np:/tcp:).

    Thanks
    Ming.
  • I'm seem to be having an upgrade issue. I have upgraded my client tools to 2005 and am trying to connect to an existing SQL Server 2000 box. I have a named data source pointing to a port on my local machine which is then forwarded through an SSH connection. The 2000 client tools had no trouble connecting; I also have no trouble connecting via a Perl script, but I can't connect using Management Studio. I get the first error you have listed above.

    Any ideas what the problem might be? Thanks
  • Hi, Bob

    As Peter mentioned in the blog, your sql server might not listen on TCP port properly under this situation.Please follow below steps to troubleshoot:
    Go to Server Network Utility, if you enabled TCP on the server,you can see TCP/IP is in the "Enabled Protocols" and click properties of it, get which port configured,then

    1)netstat –ano |findstr <PortNum>

    2)go to server ERRORLOG see whether server is listening on TCP protocol,you should see "SQL Server is listening on TCP" in the log.

    3)or use c:\Program files\Microsoft SQL Server\90\tools\binn\osql /S<Instance>,<PortNum>, see what happens.

    4)It sound that you connect to SQL Server using alias, so, please go to SQL Server Client Network Utility,choose "Alias" tab, check whether you configured the same port that server was listening on in the TCP Alias properties.

    If you still have further question, please attach the log file and the connection string( how do you make connection).

    Thanks!
    Ming.
  • This may be plain silly, but I was struggling with this for 30 minutes because I used a small -s instead of a large -S when specifying the server. You get this connectivity error message instead of a command syntax message and believe your network connectivity is the issue.
  • if you try "osql /?" or "sqlcmd /?", you will see:

    -S:server
    -s:colseparator

    Hence, if you make connection like:
    "OSQL /s<machinename> /E" it equals to "OSQL /S /E" without specify server and by default it make connection to local default instance.

    Thanks!
    Ming.
  • Hello guys,

    I just downloaded and installed the VWD 2005 Express Edition, i followed the tutorial on the msdn until i got this exact error:

    An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be cause 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 Sever)

    Sorry but i'm in .net i am a asp developer for years now but i use msaccess so no experience in SQL Server.

    Thanks,
    Junifer.
  • If you need to access the SQL Server back-end from a remote machine you need to enable remote connections.  See this for a reference:

    http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

    Thank you,
    Peter
  • I am getting the following error while i try to open a SqlConnection(using  C#) if the SQl Server is paused.

    Source : .Net SqlClient Data Provider
    Message : A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The system cannot open the file.)

    The error number for this err is 4. (SqlException.Number)

    However if the open this connection again then i get the following error

    Source : .Net SqlClient Data Provider
    Message : SQL Server has been paused. No new connections will be allowed.

    Login failed for user '(null)'.

    So this is correct and the error number for this is 17142.

    So my question is why does i get the error number 4 in the first place and the subsequent runs gives the correct 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) (Microsoft SQL Server, Error: 2)

    to correct this error in SQLEXPRESS on servername use [servername]\sqlexpress, this should work
  • Please give me a solution for the following error, which occured when i am trying to connect  sql server2005 from remotely.

    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: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)
  • Please give me a soluten for the following error when trying to open the migrated DTS-Packages from SQL Server 2000 on the MSDB-Node/Refresh:


    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

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

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

    Login timeout expired
    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.
    Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)

    ------------------------------

    Login timeout expired
    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.
    Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

  • hi, i get this error when i run my project. i am using a remote server. it is running. but i am getting the error when i am adding a webpart to the page. otherwise it is running fine. do clear my bug. thanks.

    gokul.
Page 1 of 17 (251 items) 12345»