Solved

SQL Snapshot replication fails:  Named Pipes Provider: Could not open a connection to SQL Server [2]

Posted on 2013-11-20
19
1,125 Views
Last Modified: 2013-11-26
We are trying to set up snapshot replication for a database in our main site to the same database in multiple remote sites across a WAN link.  No firewalls are in the mix between SQL servers just distance.

When we run a snapshot replication manually it works but when it tries to run against a schedule we get the following error and are unsure how to get this working according to a schedule.

Error: 14151, Severity: 18, State: 1.
Message
Replication-Replication Distribution Subsystem: agent PUBLISHERSERVERNAME-DATABASENAME-OEG System Replicatio-SUBSCRIBERSERVERNAME-13 failed. Named Pipes Provider: Could not open a connection to SQL Server [2].
0
Comment
Question by:NBF
  • 9
  • 9
19 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Just a question for clarification purposes.  When you say manually run snapshot replication, are you talking about using the replication sps or running the underlying sql agent jobs?
0
 

Author Comment

by:NBF
Comment Utility
When choosing start job at STEP on the sql agent job for the snapshot replication it works fine however when running the job as part of it's schedule it fails with the error above.  The behavior is consistent across all subscribers in our environment.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Is there any additional information in the SQL Error Logs ?
0
 

Author Comment

by:NBF
Comment Utility
Nothing other than these errors for each snapshot replication job at the time of the scheduled run.

Error: 14151, Severity: 18, State: 1.

Replication-Replication Distribution Subsystem: agent PUBLISHERSERVERNAME-DATABASENAME-OEG System Replicatio-SUBSCRIBERSERVERNAME-13 failed.

Named Pipes Provider: Could not open a connection to SQL Server [2].
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
0
 

Author Comment

by:NBF
Comment Utility
The only thing we haven't done in that article is set up aliases.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Can you connect to one of the subscribers from the publisher with SSMS and force the use of named pipes?
0
 

Author Comment

by:NBF
Comment Utility
How do you force the use of named pipes?

Also we don't have named pipes enabled on any of our sql servers by default.  Only TCP.  Is this required to be on?  Why would it work manually starting the agent job but not on schedule?
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
My only guess is they're connecting via different protocols for some reason.  When you have the connection screen up from SSMS, click Options >>, go to Connection Properties, and for network protocol, select named pipes.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:NBF
Comment Utility
I was not able to connect via named pipes using SSMS.  Is enabling named pipes on the server basically a requirement for snapshot replication?  Are there any negatives to turning this on?  I heard that this protocol is not efficient and will slow down queries, etc....
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
This is true.  I'm a little curious as to why it's using named pipes for authentication myself.  Take a look at this article: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/906da9b5-2482-468c-a424-ae099da2d96b/sql-server-agent-service-account-error?forum=sqlsetupandupgrade

Specifically the portion about changing the ordering of the protocols via Configuration Manager so that the Agent doesn't default to connecting via Named Pipes.  I am wondering if because you're coming through TCP/IP if it passes through the protocol when you execute the job...
0
 

Author Comment

by:NBF
Comment Utility
The order is already correct.  TCP first and Named pipes 2nd.  My only other thought is that we just need to enabled named pipes on the servers.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
Certainly give it a try and see what happens.  I'm going to keep digging to see if I can find a reason as to why it's connecting with named pipes.
0
 

Author Comment

by:NBF
Comment Utility
Thank you.  I would very much like to understand that as well since I am nervous to enable named pipes to cause performance issues with our applications.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
So I found a combination of things.  The feeling I seem to be getting is that when you impersonate the process account under which the distribution agent runs under, it forces the necessity of named pipes to establish the trusted connection required for passing the token.  Did you specify different accounts than the default (which would default to the SQL Server Agent's context, if I'm not mistaken) for the various agents to run under?  If not, I am thinking that may be a first step to try.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
can you clarify: what are your sql server version\edition used in the replication?
are all of them on the same network?
is your TCP\IP protocol enabled
on the both sql servers?

clarify "we run a snapshot replication manually"? from job?
what security did you use when set replication: sql agent account?

check this snapshot job owner


you  may need to install fresh sql service pack
0
 

Accepted Solution

by:
NBF earned 0 total points
Comment Utility
We recreated the publication and subscriptions and now everything is working properly last night.  I guess we call it a fluke.  We didn't think we set it up any differently.
0
 
LVL 10

Expert Comment

by:PadawanDBA
Comment Utility
As long as you got it working, that's the important part!
0
 

Author Closing Comment

by:NBF
Comment Utility
Unable to find issue, recreated and working now.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now