?
Solved

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

Posted on 2013-11-20
19
Medium Priority
?
1,315 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

718 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