Solved

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

Posted on 2013-11-20
19
1,202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
19 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39663372
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
ID: 39663381
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
ID: 39663409
Is there any additional information in the SQL Error Logs ?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:NBF
ID: 39663415
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
ID: 39663424
0
 

Author Comment

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

Expert Comment

by:PadawanDBA
ID: 39663483
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
ID: 39663496
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
ID: 39663523
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
 

Author Comment

by:NBF
ID: 39663572
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
ID: 39663608
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
ID: 39663740
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
ID: 39663785
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
ID: 39663789
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
ID: 39664187
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 43

Expert Comment

by:Eugene Z
ID: 39664501
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
ID: 39666027
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
ID: 39666104
As long as you got it working, that's the important part!
0
 

Author Closing Comment

by:NBF
ID: 39677034
Unable to find issue, recreated and working now.
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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