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 4 and 5 and type the answer here:
  • Post
  • Hi All


    I have been having the same error I have narrowed it down to being the SQL Server Express 2005 not switching on properly.

    These are my issues, everytime i go to the sql server configuration manager and switch on the sql server (sqlexpress) it say

    "The request failed or the server did not respond in timely fashion. Consult the error or other application error log"

    I get a different error when I try and start the SQL server (sqlexpress) in the Sql server 2005 surface interface configuration tool the error is:

    "An error occurred while performing this operation"

    I then get this error when I try and start SQL SERVER (SQLEXPRESS) in the services tool

    "The SQL SERVER (SQLEXPRESS) on your local computer started and then stopped. Some service stop automaticly if they have no work to do, for example, the performance log."

    Now when I try and connected to the sql server Management studio express I get this error

    "An error has occured while establishing a connection to the sql server. When connecting to SQL sever 2005, this failure may be caused by the fact that the default setting sql server does not allow remote connections. (provider, name pipes provider, error: 40 - Could not open a connection to sql server) (microsoft sql server Error: 2)

    Which is the same error I get when I try an use one of my websites which interacts with an sql server 2005 express database.

    So what have I tryed so far to fix this problem, well I have enable TCP/IP and Name pipes in the sql sever configuration manager under SQL server 2005 network configuration / protcols for sql server express

    I have also set up the sql server browser to automatic which is working fine.

    I have setup sql server (slqexpress) to manual and that still switch off like above. There seems to be lots of people out there with this issue.

    WHAT I AM TRYING TO FIND IS A WAY OF STARTING SQL SERVER USING THE COMMAND PROMPT?????

    I need to sort this problem out ASAP.

    Does anyone know how to do this??
  • Hi Ming,

    I think Sunu is referring to my post on 6/21/2006 immediately above his. Let me know if you need any more information.  I have found several forums posting similar errors however have not found a solution to it yet :( Let me know if you would like any additional information ...also i would like to remove the comment that removing netbios helps...it seems like maybe i just had a lucky streak cause the errors are persisting.  It seems so random. It doesn't coincide with backups or dbcc commands that we're running and the machine isn't being stressed at all....

    plz help
  • In response to thomas' post
    NET START MSSQLSERVER
    NET START SQLSERVERAGENT
    also note that if you start SQLSERVERAGENT that it will automatically attempt to start the dependant service MSSQLSERVER

    Also i see the improper switching to be a result of the errors not a cause... I have found that sometimes when i get this error my cluster service will be unable to communicate with SQLServer (even though it's on the same machine) and restart the sql service if it can't connect in a reasonable amount of time.


  • Hi, Thomas

    1)
       By default SQL Express was installed as a named instance, you can find useful info by following the blog:
       http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    2)From command line, you can use

    net start MSSQL$SQLEXPRESS, since the instance name is fixed for sql express.

    Or you can go to services.msc, find MSSQL$SQLEXPRESS, do start/stop/restart..

    Good Luck!
    Ming.
  • Hi, Nam

       From your description, this looks like not a cluster issue. The error you saw probably is due to server close the connection, could you check server errorlog or system event log see whether there is any useful info to provide clue?

    Also, we need more detail info about your scenario to identify the problem, could you help follow the below guidline and post your answer to the question?

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

    Thanks!
    Ming.
  • Hi All

    I fixed the problem I unistalled sql server 2005 express and reinstalled everything is working fine now. Thanks for the advice. Keep up the good work

    regards.
  • Thanks ming I checked the system Logs and there are a couple of errors that happen around the same time but they don't always happen...

    Application popup: Windows - Virtual Memory Minimum Too Low : Your system is low on virtual memory. Windows is increasing the size of your virtual memory paging file. During this process, memory requests for some applications may be denied. For more information, see Help.

    The browser service has failed to retrieve the backup list too many times on transport \Device\NetBT_Tcpip_{2367FB22-08A8-453D-9814-778CBC0FFB4D}. The backup browser is stopping.

    In light of the paging file i have expanded it to the recommended size on from the OS which is quite large.. 48898MB....we have 32Gigs of ram on this server. Let me know if that's overkill.  I read somewhere that recommended is about equivilant to how much ram you have.

    Hope this works...just in case :D

    [1] Client side:
      1. What is the connection string in you app or DSN?
    jdbc:inetpool:inetdae7a://serverName?user=User&password=*******&appname=AppProcess&database=DBName
      2. If client fails to connect, what is the client error messages? (please specify)
    Typically only fails to connect during the minute or two when the errors are happening.
    Also if the errors cause a sql server reboot then it will get an error message stating such.

    com.nprise.dao.DaoException: com.inet.tds.s: java.net.ConnectExceptionConnection refused

    com.nprise.dao.DaoException: com.inet.tds.s: Msg 6005, Level 14, State 2, Line 1, Sqlstate 01000
    [DIDATABASE1]SHUTDOWN is in progress.

      3. Is the client remote or local to the SQL server machine? Remote
      4. Can you ping your server? YES
      5. Can you telnet to your SQL Server? YES
      6. What is your client database provider?  merlia java database driver Or/And, what is your client application? SQL Management Studio, Query Analyzer
      7. Is your client computer in the same domain as the Server computer? Same Domain
      8. What protocol the client enabled? TCPIP
      9. Do you have aliases configured that match the server name portion of your connection string? YES



    [2] Server side:



      1. What is the MS SQL version? SQL Server 2005 x64
      2. What is the SKU of MS SQL? Standard
      3. What is the SQL Server Protocol enabled? TCPIP, NamedPipes
      4. Does the server start successfully? YES
      5. If SQL Server is a named instance, is the SQL browser enabled? YES
      6. What is the account that the SQL Server is running under? Domain Administrator
      7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? NA
      8. Do you make firewall exception for SQL Browser UDP port 1434? NA



    [3] Platform:

      1. What is the OS version? Windows 2003 x64
      2. Do you have third party antivirus, anti-spareware software installed? NO



  • Hi,
    I'm getting error msg:(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) while using webparts in my web app.I guess this has something to do with the point that connection string while connecting with aspnetdb in case of web parts uses windows authentication.
    I'm also not able to connect to any other db if i use windows authentication in my connection string.
    Can u plz suggest me any solution for it?

    Thanks
    Regards,
    Ritu
  • Hi, Ritu

       Can you provide the connection string? Please following the below blog and search the error message to see the troubleshoot tips.

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

    Good Luck!
    Ming.
  • Hi, Nam

       Thanks for your detail info.
       So,

       First, it is remote connection, how do you telnet? telnet <server> <sqlport>? If your sql instance is a named instance, you should go to server errorlog to check server TCP/IP was enabld and listening on which port;  if your sql instance is default instance, you just try port 1433. Did you try to telnet the correct port? Plus, the <server> must be the hostname of your remote server.

     Secondly, if your sql instance is named instance, did you specify the instance name in your connection string? Normally, you should specify "<remoteserver>\<instancename>"

     Thirdly, you mentioned you specify the alias, how does it looks like? did you make connection by using alias or use "<servername>\<insatncename>"? Please double check whether you spcify the correct sql port number in your alias, that might cause client connect to a tcp port that sql instance was not listening on.

    Fourthly, to make remote connection and your remote server has firewall enabled, you need add sql port and browser udp port to the exception list, so, either you add sqlservr.exe and sqlbrowser.exe to exception list or edit the port directly to the exception list. After this done, see whether you can make connection.

    Finally, for a quick workaround is by specifying the port number in your connection string, namely, "<servername>\<instancename>,<sqlport>", on your server " netstat -ano |findstr <sqlport> " to see server was listening on the port.

    Let me know if you have further question.

    Good Luck!!
    Ming.

     
  • Asaspal. Memrano tu es besta. Amigo.
  • Hey Ming,

    default port and default instance also i can telenet into the machine. Not using an alias just the virtual server name that we set up with the cluster.  Also no firewall everything is connecting from in the same domain.  

    The problem isn't that we can't connect, we can connect 99% of the time. It's just that about once a day we lose connection for about 1 minute and get the errors listed above.

    Also since i expanded the page file the problem still persists. :(
  • Hi

    I fixed my error 40 by turning off user instances and re editing the connection string in my C# project.

    Hope this helps.
  • Hi, Nam

       One way to identify the 1% connection lose is to open SQL trace file, and see when you got repro, what operation are your client app doing and what was going on on the server...

    Thanks!
    Ming.
  • Your article is quite right, thanks.
Page 4 of 17 (251 items) «23456»