"No process is on the other end of pipe" caused by bad certificate

"No process is on the other end of pipe" caused by bad certificate

  • Comments 18

In this blog, I am focusing on one specific error message which I think a little confusing for you to troubleshoot the connectivity issue against SQL Server 2005.

To be clear, please make sure you saw the following exact same symptom before you apply the resolution that this blog provide since the same error message could also caused by other reasons which is out of scope of this blog.

1) OS on the server could be WinXP/Win 2K/ Win 2K3/ Vista.

2) SQL Server 2005

3) Server was started and from Server ERRORLOG, you can see "The certificate was successfully loaded for encryption."

4) Make local or remote connection over SNAC(SQL 2005 Client) or SqlClient(ADO.NET 2.0) provider, you see any of  following error message depends on what protocols enabled on your sql server:

Shared Memory Provider: No process is on the other end of the pipe.


Named Pipes Provider: No process is on the other end of the pipe.


TCP Provider: An existing connection was forcibly closed by the remote host.

5) The problem is consistently not intermitently whenever you make a fresh connection.

6) If you try connection over MDAC( SQLOLEDB.1 provider or {SQL Server} driver) you would get consistent and more descriptable error message like:

[DBMSLPCN]SSL Security error
[DBMSLPCN]ConnectionOpen (SECDoClientHandshake()).

The cause behind would be various such as SQL Server loads the bad cert due to different OS behavior such as between XP/2K and 2K3, later on connection fail since certificate validation does not pass on the server based on validation flag provided by the client.

The resolution here is:

1) If you are not trying encryption, first, see whether you explicitly specify a cert? if so, then delete it; otherwise, go to local machine or user certificate personal store, delete all certificates, and restart sql server, doulbe check Server ERRORLOG, see " A self-signed certificate was successfully loaded for encryption.", then retry connection.

2) If you must use encrypted connection, you need a good certificate for SQL Server 2005. Please see blog: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx 

to understand what kind of certificate is qualified for SQL 2k5. Also, you can go to SQL Server Configuration Manager, right click properties for the sql instance, and choose *Certificate* tab, choose any certificate in the drop down list which has already been validated by SQL 2k5, then restarted the sql instance and retry connection.

The error you may hit more frequently when you did not choose a cert and SQL Server load the bad cert from certificate store. There are various bad cert that cause the problem, see kB:http://support.microsoft.com/kb/919710/en-us for one example.

 

MING LU

SQL Server Protocols

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

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • Thank you. We had the same problem and your doc helped us a lot.

  • Thanks for this solution.

    This problem had me scratching my head for a while as it initially appeared that SQL Server 2005 (all three instances on my machine) had spontaneously failed for no apparent reason.  There were no error messages in the logs, and it was impossible to establish any connection to the server by any means from any account.

    Other points of interest:

    1. I had used the IIS Resource Kit to create a self-signed SSL certificate for IIS, and this had caused the problem. Someone should report this to the IIS and SQL Server teams, as this needs to be fixed.

    2. The self-signed certificate was loaded by SQL Server 2005 even though no certificates whatsoever were specified anywhere in the SQL Server configuration dialogs.

    3. The self-signed certificate was not displayed anywhere in Certificate Manager and could not be removed by obvious means (admittedly, with a good system backup on hand, I did not bother to look very far to resolve this).  The solution for me was to restore the most recent system backup image.

  • Thanks for this solution it very helpfull

  • When using SSIS I noticed that this error message will also popup when an lookup object is trying to retrieve too much data and runs out of cache to place it in.

    Aparently the object will shut down and the error is that the pipeline is broken since the lookup object shuts down.

    (Shared Memory Provider: No process is on the other end of the pipe.) is the error message you'll recieve.

    Setting the enable caching feature on and limit the memory useage to xx mb's will olikely solve this problem. In stead of running out of cache, the object will divert queries to the relational database when the information is not cached in that particular 100MB.

  • If you are using SQL Server 2005, you must also change the surface area settings.

    Configure surface area settings in SQL Server 2005

    1.  Click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

    2.  In the SQL Server 2005 Surface Area Configuration dialog box, click Surface Area Configuration for Services and Connections.

    3.  In the tree view, expand the node for your instance of SQL Server, expand the Database Engine node, and then click Remote Connections.

    4.  Select Local and Remote Connections, select Using both TCP/IP and named pipes, and then click OK.

  • I had this error message too, but it was completely unrelated to certificates.

    I had an sp which accessed a view which accessed a linked server. It turned out the user account being used didn't have the necessary rights on the linked server.

    This was esp confusing because both servers were configured to only use TCP/IP.

    Just a good old misleading error message.

  • You may also run across this error message if you try to login to SQL Server 2005 with a disaled SQL Login.

  • Thanks for this post. I had a selfssl installed and I wasnt explicitly using it to connect to still was giving me this error. I disabled all purposes of the certificate and it seems to have worked!! Thanks a bunch.

    -Nicky

  • Some tricky i have here, somehow i have to place my computer name instead of local or localhost into connection string:)

  • PingBack from http://ryangaraygay.com/blog/post/2008/03/SSL-and-SQL-Server-2005.aspx

  • PingBack from http://moses.freemusicradioonline.info/noprocessisontheotherendofthepipe.html

  • I spent a few hours investigating these error after installing SQL Server Express on a new laptop with Vista Buisness.

    It turns out (in my case) the error was due to me forgetting to allow SQL Server Authentification, not related to a bad certificate as described here (although the symptoms were the same).

  • The same error message also appears if you use sqlcmd.exe with a local username and SQL-Server authentication is not enabled.

  • Hi,

    I have a computer that has Win-2003 and SQL Server 2005 Installed.

    I checked my client and server both are giving the response to each other using ping their address.

    I created a string as:

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

    Public conn As New ADODB.Connection

    Public rs As New ADODB.Recordset

    Sub main()

    Set conn = New ADODB.Connection

    conn.ConnectionString = "Provider=SQLNCLI;Server=PUNSEZ178073d;Database=test;Uid=sa;Pwd=Infosys123;"

    conn.Open

    conn.close

    end sub

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

    Above string is working fine at Server machine but when i am running the same script on client machine i am getting error "Provider not found".

    Please help

    Rashid khan

  • Rashid, have you installed the "Microsoft SQL Server Native Client" on the client machine, which includes the SQLNCLI OLEDB provider? It can be downloaded alone or as part of the SQL Server 2005 feature pack: http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en

    Also, please post questions like this on the SQL Server Data Access forums, rather than the blogs: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/threads

    Hope this helps.

Page 1 of 2 (18 items) 12