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

This was originally posted in the SSIS Forum, but a member of the IS team suggested it be moved here.  "Most recently I got this error (Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) so does this mean that SQL Server is still trying to use named pipes even if I only have TCP/IP enabled in SQL Server Configuration Manager "

I'm having the same issue, and here's our scenario:

1. Installed SQL Server 2005 Developer Edition on a machine with WinXP SP2 and enabled remote connections over TCP/IP
2. Installed SQL Server 2005 Standard Edition on a machine with Win2003 SP1 (remote connections over TCP/IP enabled by default)
3. Attempted to 'Copy Database' from Developer Edition TO Standard Edition using 'Detach and Attach' method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
4. Attempted to 'Copy Database' from Developer Edition TO Standard Edition using 'SQL Management Objects' method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
5. Reinstalled Standard Edition on the Win2003 SP1 machine
6. Checked all the settings on both machines several times, restarted services etc., and read every post I could find referencing the error.
7. Same error
8. Lost my last hair

Thanks in advance,

Steve

The error is reported by client library. While your server is listeing on remote TCP, client will still try TCP and NP connection in order. So the error client behavior is expected. From what you have described, I believe that even though you enabled the remote TCP connection on the XPSP2 machine, you didn't make the TCP listening port an exception of XPSP2 personal firewall. You should follow steps below to resolve this issue.

Nan,

Before I get on with this post - thank you for your reply.

The XPSP2 machine actually had the firewall disabled, and I verified that it could accept connections on the the associated ports with the Shields Up utility.  I tested this on both machines.

I can connect to the server from the XPSP2 machine, Import / Export, manage, view logs, and do everything EXCEPT get the Copy Database package to run.  SQL Server Agent always fails on the last step with the following error:

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date:  1/10/2006
Time:  1:34:15 PM
User:  NT AUTHORITY\SYSTEM
Computer: 401SERVER
Description:
  Event Name: OnError
 Message: Failed to connect to server BETHESDA.
StackTrace:    at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->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)
StackTrace:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at 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)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
 Operator: NT AUTHORITY\SYSTEM
 Source Name: BETHESDA_401SERVER_Transfer Objects Task
 Source ID: {86F355AD-3B74-4D7B-8D2D-C743C790A269}
 Execution ID: {91B7C32C-C439-4EDB-8A0F-9F8BF207BC06}
 Start Time: 1/10/2006 1:34:15 PM
 End Time: 1/10/2006 1:34:15 PM
 Data Code: 0

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Have you (or anyone else) successfully executed a Copy Databse task

Steve

From the error message, your scenario is broken on connection stage and I would like to know if you have tried basic connectivity tests between the two mahcines you are working on. One simple way to verifty connectivity is to use command line tools, such as osql.exe. For example, osql -E -Stcp:servername\instancename. If it connects cross-machine successfully, please also verify that your connection string in your scenario is correct.

Nan,

I'm probably going to thank you for your help every time I post - so again, thanks for your help.  I've tried the basic connectivity tests (below is the command line output) and they were successful.  I have had no connectivity issues using SQL Studio or otherwise, unless it involves using the 'Copy Database' task and SQL Server Agent.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Steve>sqlcmd -S 401Server -i C:\TestQuery.sql
Changed database context to 'Test'.
Location_ID Location_Name
----------- --------------------------------------------------
0010-10     ADAMSVILLE
0011-10     ALLENTOWN
0011-11     EASTON
0011-12     BETHLEHEM
0011-13     EASTON RENTAL MART

(5 rows affected)

C:\Documents and Settings\Steve>osql -E Stcp:401Server
1> exit

C:\Documents and Settings\Steve>

Has anyone been able to successfully execute a 'Copy Database' from one SQL Server 2005 to another

Steve

Thanks for the reply Matt.. I've been off-site working on a different project and will post again as soon as I'm able to implement your suggestion.

Steve

Hi,

I am able to remotely connect to SQL Server 2005 using the Management Studio and also via application running on the client machine.

I am attempting a setup project for dynamically creating database for which I need to connection to the sql server during install. It is at this stage that I get the error - Default settings do not allow remote connections. Whereas on my sql server remote connections using both named pipes as well as tcp / ip are enabled...

PLZ HELP!!

Also -

Windows XP with SP2, Windows Firewall is probably blocking your SQL connection:

http://support.microsoft.com/default.aspx scid=kb;en-us;839269