Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Rate This
  • Comments 35

This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting tips for this specific error message.

First, take a look at below MSDN forum link lists about this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1287189&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1334187&SiteID=17

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1292357&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

 

The various causes fall into five categories:

1 Incorrect connection string, such as using SqlExpress.

2 NP was not enabled on the SQL instance.

3 Remote connection was not enabled.

4 Server not started, or point to not a real server in your connection string.

5 Other reasons such as incorrect security context.

Let's go throught the detail one by one:

I.   Incorrect connection string, such as using SqlExpress.

Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

The typical error when dealing with Express includes:

a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

b. Np was disabld by default after installing SqlExpress.

c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

Please read the following blog for best practice of connecting to SqlExpress.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

 

II. NP was not enabled on the SQL instance.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

1) Go to SQL Server Configuration Manager, See Server has NP enabled.

2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

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

4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

 

III. Remote connection was not enabled. 

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

                 https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

a. "File and Printer Sharing" was opened in Firewall exception list.

b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

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

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

 

IV. Server not started, or point to not a real server in your connection string.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

 

 V. Other reasons such as incorrect security context.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

 

Summary, give checklist:

1. Is your target server started?

2. Is your target server listening on NP? Which Pipe?

3. Has your client enabled NP? Use the same pipe to connect as Server?

4. Are you making local connection? If so, what is the instance, default or remote?  

5. Did you put correct instance name in the connection string? Remember, Sqlexpress is a named instance.

6. Did you enable remote connection? Firewall? IPSec? "File and Printer Sharing" opened? Can access server?

7. Can you make basic connection by using <servername> or <servername>\<instancename>? Use sqlcmd or osql.

8. What is your repro step? What was your client APP doing during this error occuring? Which DB operation, detail?

 

MING LU

SQL Server Protocols
 
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • Hi,

    I am trying to connect SQL2005 from my local machine.

    I am getting following 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

    I have checked tcp/ip setting, browser service, firewall on target as well as my local machine. I have sql2005 client with sp1, windows xp with sp2.

    I am able to connect, register few different sql2005 servers.

    I have uninstall an reinsall sql2005.

    Can you please help me?

  • Did you find an answer for your problem?

    I have the same problem. I was able to use it. I had to reinstall express, the only difference is I put a check mark for user instance.

    I get this error:

    (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider

  • im really confused because ive tried all the steps ive come accross on forumns and i still am not able to run the sql server 2005 express service.

    I am posting my error log for this program. Hope someone can help.

    Here is the log

    2007-05-29 01:19:20.77 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

    Oct 14 2005 00:33:37

    Copyright (c) 1988-2005 Microsoft Corporation

    Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    2007-05-29 01:19:20.79 Server      (c) 2005 Microsoft Corporation.

    2007-05-29 01:19:20.79 Server      All rights reserved.

    2007-05-29 01:19:20.80 Server      Server process ID is 5860.

    2007-05-29 01:19:20.80 Server      Logging SQL Server messages in file 'G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

    2007-05-29 01:19:20.85 Server      This instance of SQL Server last reported using a process ID of 5704 at 5/29/2007 1:18:29 AM (local) 5/28/2007 8:18:29 PM (UTC). This is an informational message only; no user action is required.

    2007-05-29 01:19:20.85 Server      Registry startup parameters:

    2007-05-29 01:19:20.87 Server       -d G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

    2007-05-29 01:19:20.87 Server       -e G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

    2007-05-29 01:19:20.87 Server       -l G:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    2007-05-29 01:19:21.34 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    2007-05-29 01:19:21.34 Server      Detected 1 CPUs. This is an informational message; no user action is required.

    2007-05-29 01:19:29.96 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.

    2007-05-29 01:19:51.45 Server      Database Mirroring Transport is disabled in the endpoint configuration.

    2007-05-29 01:19:54.76 spid5s      Starting up database 'master'.

    2007-05-29 01:19:59.72 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

    2007-05-29 01:20:07.72 spid5s      SQL Trace ID 1 was started by login "sa".

    2007-05-29 01:20:08.52 spid5s      Starting up database 'mssqlsystemresource'.

    2007-05-29 01:20:16.19 spid8s      Starting up database 'model'.

    2007-05-29 01:20:16.25 spid5s      Server name is 'BOY\SQLEXPRESS'. This is an informational message only. No user action is required.

    2007-05-29 01:20:16.39 spid5s      Starting up database 'msdb'.

    2007-05-29 01:20:19.85 spid8s      Clearing tempdb database.

    2007-05-29 01:20:27.24 spid8s      Starting up database 'tempdb'.

    2007-05-29 01:20:31.82 spid5s      Recovery is complete. This is an informational message only. No user action is required.

    2007-05-29 01:20:32.36 spid11s     The Service Broker protocol transport is disabled or not configured.

    2007-05-29 01:20:32.44 spid11s     The Database Mirroring protocol transport is disabled or not configured.

    2007-05-29 01:20:33.22 Server      A self-generated certificate was successfully loaded for encryption.

    2007-05-29 01:20:34.94 spid11s     Service Broker manager has started.

    2007-05-29 01:20:35.29 Server      Server is listening on [ 'any' <ipv6> 1026].

    2007-05-29 01:20:35.52 Server      Server is listening on [ 'any' <ipv4> 1026].

    2007-05-29 01:20:36.21 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].

    2007-05-29 01:20:36.21 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].

    2007-05-29 01:20:37.03 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

    2007-05-29 01:20:37.39 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

    2007-05-29 01:20:37.40 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.

    2007-05-29 01:20:40.97 spid11s     Service Broker manager has shut down.

    2007-05-29 01:20:42.69 spid5s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.

    2007-05-29 01:20:42.69 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    2007-05-29 01:20:43.23 Server      The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.

  • I have been working on to reslove this for the last 4 days. Still no clues. I had tried all the possibilities...strange !!!

    Any one who has the solution, pls post it.

    TIA,

    - Anand.

  • Looks like you are trying to force a remote connection on Named Pipes and your named pipe provider is not enabled.

    Please try to follow the troubleshooting ideas for "enabling remote connections" at the very beginning of this blog. Namely,

    III. Remote connection was not enabled.

    Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

                    https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

    If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

    a. "File and Printer Sharing" was opened in Firewall exception list.

    b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

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

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

    Your best bet is the following suggestion. Try it first before trying everything else in the posts:

    Enable remote named pipe: All programs | Microsoft SQL Server 2005| Configuration Tools | SQL Server Surface Area Configuration | Configuration for Services and Connections | Remote Connections, choose enable Named Pipe.

    Looking at the errorlog just before your post, it has TCP enabled, but NOT named pipes. When you perform the steps described in the above posts, you should expect to find something like that in your errorlog:

    <date> <time> Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\... ].

    If you did enable Named Pipe and do see message like this in your errorlog (NOT "local connection provider", but "named pipe provider") and you still see the error message above, please let us know. Please provide as much information as you can about your client program, your connection string, your OS on both client and server side etc.

    Thank you

  • Hi guys, I have a problem with error: 40.

    I made a Web page, and it works perfect on my local machine, everything is OK, until I uloaded page to the server, it reports this 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)

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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)

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

    Stack Trace:

    [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)]

      System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091

      System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

      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) +820

      System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628

      System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170

      System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359

      System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28

      System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424

      System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66

      System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496

      System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82

      System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105

      System.Data.SqlClient.SqlConnection.Open() +111

      System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121

      System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137

      System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83

      System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770

      System.Web.UI.WebControls.Repeater.GetData() +50

      System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232

      System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53

      System.Web.UI.WebControls.Repeater.DataBind() +72

      System.Web.UI.WebControls.Repeater.EnsureDataBound() +55

      System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12

      System.Web.UI.Control.PreRenderRecursiveInternal() +77

      System.Web.UI.Control.PreRenderRecursiveInternal() +161

      System.Web.UI.Control.PreRenderRecursiveInternal() +161

      System.Web.UI.Control.PreRenderRecursiveInternal() +161

      System.Web.UI.Control.PreRenderRecursiveInternal() +161

      System.Web.UI.Control.PreRenderRecursiveInternal() +161

      System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

    I dont't Know what to do. Please HELP!

  • Hi, zdena

       Please check out the following blog: http://blogs.msdn.com/sql_protocols/archive/2007/05/16/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error-xxx.aspx

    Good Luck!

    Ming.

  • clear all the log from the log events frm service manager and try to enable the service

  • thx for ur tips......i got the solutions

  • In the end... What is the solution to this problem?

  • I found the solution. The SQL port - 1433 -  needs to be included as part of Data Source on the connection string:

    ...Data Source=mysqlserver\instance1,1433;...

    That did it for me.

  • This error kept me busy all morning and part of the afternoon: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be cause ...

  • If you did everything above and it still doesn't work, check that Windows Firewall is ON, it fixed my problem when I turned it on. ;)

  • On corporate network, if using corporate domain login, do not use network service as built-in account.

    After changing the setting to local system, it works. The change is under: SQL Server Configuration Manager --> SQL Server --> Log on as: Built-in account  --> Local System

  • try starting service SQL Server (MSSQLSERVER)

Page 1 of 3 (35 items) 123