Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Part III – Connection Fail when SqlClient connects to Sql Server 2005

When you connect to SQL Server 2005 either using "SQL Server Managment Studio" or any application compiled with .NET Framework 2.0, you are using SqlClient provider(Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.)


Error Message 1:

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)

Troubleshoot:
1) Make sure your sql service is running, use either "net start" or "sc query <InstanceName>" or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.

2) You might explicitly use "np:"prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:

Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]

Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

3) You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.

4) You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.

5) You might explictly specify "lpc:" prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

Error Message 2:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: 0 - No process is on the other end of the pipe.)

1) You might explicitly use "np:"prefix which ask for connect through named pipe and specify FQDN/LoopbackIP/IPAddress as server name in the connection string.
2) You might use FQDN/IPAddress/LoopbackIP to connect to the server.

To resolve 1) and 2), you can specify <machinename> instead of FQDN/IPADress/LoopbackIP.

Error Message 3:

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)

Troubleshoot:
1) You might explicitly use "lpc:"prefix which ask for connect through shared memory. Either server instance was not started successfully or shared memory was not enabled on the server.To resolve this, you remove "lpc:"prefix in your connection string as long as Server is listening on other protocols or enable shared memory and restart server.

2) You explicitly use "lpc:"prefix and connect to a local named instance through form [./(local)/localhost/<machinename>]\<InstanceName>, but Sqlbrowser service was not started. To resolve this, you need to enable sqlbrowser.

3) You might connect through "lpc:" which not includes any server name, to fix this, add correct server name in your connection string.

Error Message 4:

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.)

Troubleshoot:
1) You might explicitly use "tcp:"prefix which ask for connect through TCP/IP, however either server was not listening on TCP/IP, to resolve this, either remove "tcp:"prefix in your connection string or enable tcp protocol.

2) You might not connect through the exact port that server is listening on, to verify this, go to SQL Configuration Managner
choose "Protocols for <InstanceName>" and click properties for TCP/IP, see which port is configured for server listening and then try connect through the port, like in connection string "tcp:<machinename>,<portnumber>".

3) The instance that you want to connect through TCP was not started, check server ERRORLOG and restart server.

Error Message 5:

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)

1) You might have named pipe or tcp enabled and connect to a named instance, but SQL Browser service was not started or enabled. To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

Error Message 6:

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: 41 - Cannot open a Shared Memory connection to a remote SQL server)

1) You might explicitly use "lpc:"prefix and connect to a named instance but specify FQDN/LoopbackIP/IP as <servername>, eg, your connection string looks like "lpc:<FQDN>\<InstnaceName>" or "lpc:127.0.0.1\<InstanceName>"..

2) You might explicitly use "lpc:"prefix and give the wrong server name in your connectionstring, eg: "lpc:xx" <xx> is not the hostname of your machine.

Error Message 7:

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)

1) You might explicitly use "np:" prefix in your connection string and try to connec to a named instance, but named pipe was not enabled on the remote server, to resolve this, enable the remote named pipe and restart instance or remove "np:"prefix if remote server is listening on TCP/IP.

2) You might explicitly use "tcp:" prefix in your connection string and try to connec to a named instance, but TCP/IP was not enabled on the remote server, to resolve this, enable the remote TCP/IP and restart instance or remove "tcp:"prefix if remote server is listening on Named Pipe.

Error Message 8:

An error has occurred while establishing a connection to the server. When connectiong 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, errror:0-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.)

1) This is because connection blocked by Windows firewall. To resolve this, take follow steps:

a. Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\AuthorizedApplications\List
b. Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433).
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\GloballyOpenPorts\List.For more detailed operation, see http://support.microsoft.com/default.aspx?scid=kb;en-us;287932

Summary:

1) In any case, SqlClient should be able to connect to SQL Instance through any of the protocols(Shared Memory/Named Pipe/TCP) as long as SQL Instance was started successfully.

2) And if you speculate any protocol in connection string ("lpc:"/"np:"/"tcp:"), the error message would display "<Protocol> Provider, error <Num1> -....<Num2>." <Protocol> stands for "Shared Memory" or "Named Pipes" or "TCP"; If you do not speculate
any protocol, the error message indicates that connection fails when connecting through specific <Protocol>.

3) In the error message format for SqlClient, please notice two different error number. <Num1> stands for internal error thrown out by SQL Protocols, <Num2> is the OS error(eg: 233 - No process is on the other end of pipe). When you see <Num1>=0, that means the connection fails due to OS error not caused by SQL Protocols, under this situation, you can use "net helpmsg" to check specific OS info.

Finally, if you were developing .NET framework application and came across above issues in your client app, the best way is first try SQL Server Management Studio to connect to SQL Server using the exact same connection string in your app, and watch the error message, normally, there is additional error info at the end of error string, eg ( Microsoft SQL Server, Error:87) which gives you clue(net helpmsg 87) that problem inside your connection string.

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

 

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • Hi, Migue

       The two quick workaround here is:

       1) Enable Named Pipe, and connect through "np:machine.domain.com/
    np:x.y.z.t"

       2) Use SQL Authentication.

       To identify the root cause, I have question for you:

       a. Did your machine join domain before?
       b. Is your machine connected to a network?
       c. Can you also try "tcp:127.0.0.1,1433", see what happens?

    Thanks!
    Ming.
  • Hi, Ming

    thanks for your quick answer.

    Answering your questions:

      a. Did your machine join domain before?

         No, from the beginning it is in a workgroup and it has never joined a domain

      b. Is your machine connected to a network?

         Yes. It's in a network with connectivity to the other computers and servers (DHCP, DNS, ...).

      c. Can you also try "tcp:127.0.0.1,1433", see what happens?

         It works well. When I noticed the problem I checked the connectivity and everything seems correct (telnet x.y.z.t 1433 also works)

    And ... do you mean that Database mirroring works with named pipes? I had understood that the protocol was necessary tcp.
  • Hi, Migue

       1)Thanks a lot for your response. Your scenario seems very interesting, it sounds like you try "tcp:127.0.0.1,1433" works, only "tcp:<FQDN>,1433" and "tcp:<IP>,1433" not work, right? We saw such issue on WINXP and WIN2K due to OS design, but not on WIN2K3.
    So, sounds for you the current workaround is either use "tcp:127.0.0.1,1433" or SQL Authentication.

      2)Yes. In DB-Mirroring scenario, you are required using TCP.

    Basically, I will do some investigation and send you reply about the possible cause.

    Thanks!
    Ming.
  • OLE DB provider "SQLNCLI" for linked server "LinkerServerName" returned message "Communication link failure".
    Msg 233, Level 16, State 1, Line 0
    Named Pipes Provider: No process is on the other end of the pipe.
    OLE DB provider "SQLNCLI" for linked server "LinkerServerName" returned message "Invalid connection string attribute".
  • Hi, suyog

       The error indicated that either you might have not name pipe protocol enabled on your server or your connection string is not correct.

       To resolve the issue, could you
    1) provide your connection string? how do you make connection? Is your server a default or named instance? did you add the instance name to your connection string? Did you make local or remote connection?

    2) Can you go to SQL Server Configuration Manager to find your sql instance, and enable Named Pipe and then restart sever, and retry?

    Good Luck!
    Ming.
  • Hi,

    I get following the errors, these are rare and random. The application is written C++ uses MS ODBC on SQL 7 with Windows NT4:

    SQLError Info
    SqlState 01000fNativeError 233
    Error Msg [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionTransact (TransactNamedPipe()).
    SqlState 08S01f NativeError 0
    Error Msg [Microsoft][ODBC SQL Server Driver]Communication link failure

    Thanks.
  • Hi,Faisal

      We need more info to identify your problem, could you provide:

      1) What is your connection string?, namely how do you make connection? Connect through server name or ipaddress?
      2) Were you making local or remote connection? If remote, do you have firewall on your server? Did you have "file and printer sharing" added in the exception list? Or can you try whether you can access a share folder of your remote server?
      3) Can you check your server errorlog to see whether there is any error info, the error indicates that server might close connection or you can open sql trace file to see which client operation cause server closing the connection.

    Good Luck!
    Ming.

     
     
  • Hello Ming,
    I'm unable to run my webapp (written in NET 2.0 and C#) from home. Could you please help?
    Thank you.

    The error is:
    [SqlException (0x80131904): 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)]

    Here is my connection setup:
    <appSettings>
       <add key="ConnectionStringXML" value="Server=sof2;Provider=SQLOLEDB;Database=myDB;UID=myUID;PWD=myPWD" />
       <add key="ConnectionString" value="Server=sof2; Database=myDB;UID=myUID;PWD=myPWD" " />
    </appSettings>


  • Hi, Van
     Can you answer following question:
     
     1) Were you making local or remote connection?
     2) Is your sql server a default or named instance?
     3) Do you have sqlbrowser on?

    Here is checklist of troubleshooting tips:

    1) If remote connection, double sheck whether you have firewall on your server, if so, please add "File and Printer Sharing" to exception list; plus add sqlservr.exe to exception list; and add sqlbrowser.exe to exception list.

    2) If you tried to connect to a named instance, change your connection string to set "<servername>\<instancename>" as the value of *Server* field.

    3) If you were connecting to a named instance and it is remote connection, on your remote server, please do "net start sqlbrowser".

    Also, see the section "Error Message 1:" in this blog, there are some other potential cause and resolution described.

    Good Luck!
    Ming.
  • Hello Ming -

    Thanks for the great t-shooting list.
    Despite this, I'm still having trouble getting a new linked server to work.
    Server Mfr is 2005 sp1 and is the 'local' server.
    Server Krypton is 2000 sp3 and is the target server I need to link to.
    I have the Windows login on both systems and I'm in the sysadmin group on both servers.

    After linking the servers, I ran this:
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'KRYPTON', @useself = 'true'

    When I try running this query:
    SELECT * FROM OPENQUERY(KRYPTON, 'SELECT * FROM CRICUST WHERE CustName = ''Acme''')

    I get these errors:
    OLE DB provider "SQLNCLI" for linked server "KRYPTON" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0
    TCP Provider: An existing connection was forcibly closed by the remote host.

    Msg 18452, Level 14, State 1, Line 0
    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    Can you see what I'm missing?
    Thanks!
    dj
  • DJ,
     I believe your case is single hop in that the client is collocated with the middle server.
     Are you be able to login as the windows login on the local server and make successful direct SQL connection to the target linked server?
     If you can, could you describe more about your setting?
     (1) The version of Windows OS.
     (2) The version of SQL.
     (3) The user account is local account or domain account?
     (4) The SQL service running account is local account or a domain account?

  • Very many thanks for a good work. Nice and useful. Like it!
  • Hi,

    I'm getting the same error as suyog (233 Named Pipes Provider: No process is on the other end of the pipe). I'm sure that named pipes is enabled, however.

    I get this error when trying to log in with the SQL Management Studio.

    Thanks for any help you can provide.
  • Hi, kihh

       Assume you have named pipe enabled on your server:

       1) Can you check the server errorlog the pipe name that server is listening on, try your connectiong string by specifying the pipe name, eg: "osql /Snp:\\<machinename>\pipe\sql\query" or "osql /Snp:\\<machinename>\pipe\mssql$<instancename>\sql\query" see whether you can connect?

     2)Are you making remote connection? if so, could you check whether you turn on "Files and printer sharing" on your remote server firewall?

     3)Can you check following blog, whether it is a potential cause?

    http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

     4)The error you saw is intermitentely or consistently?

     5)Is your sql server a default or named instance? If named instance, please check your connection string, whether you specify the correct server pipe?

    Finally, when you were saying you have named pipe enabled, is it client side or server side, if you do not have named pipe enabled on server side, the cause might be

    a. go to Sql server configuration manager,-> client protocols -> enabled protocol, put tcp on the top of np.

    b. or turn off shared memory on your server side and restart sql server.

    Good Luck!
    Ming.
  • Hi,

    Currently this is local.

    Most advice I've read deals with point 5b. However, I can't figure out to sort them--I'm only using SQL Express if that has an effect--it seems like they just sort by name or status. Right clicking should have a move up or down option, but I can't find it.

    It appears to be caused whenever more than one connection exists, though Max connections is set to 0. I could be wrong though. However, now, after a lot of trouble shooting, it seems to have subsided a bit. Sorry for the vague answer, but I don't know.

    Is this something that will go away when I move to a host and SQL Server Standard?
Page 5 of 15 (217 items) «34567»