Solved

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

Posted on 2013-11-20
19
1,188 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
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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

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 42

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 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