Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1403
  • Last Modified:

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

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
NBF
Asked:
NBF
  • 9
  • 9
1 Solution
 
PadawanDBAOperational DBACommented:
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
 
NBFAuthor Commented:
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
 
PadawanDBAOperational DBACommented:
Is there any additional information in the SQL Error Logs ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NBFAuthor Commented:
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
 
NBFAuthor Commented:
The only thing we haven't done in that article is set up aliases.
0
 
PadawanDBAOperational DBACommented:
Can you connect to one of the subscribers from the publisher with SSMS and force the use of named pipes?
0
 
NBFAuthor Commented:
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
 
PadawanDBAOperational DBACommented:
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
 
NBFAuthor Commented:
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
 
PadawanDBAOperational DBACommented:
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
 
NBFAuthor Commented:
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
 
PadawanDBAOperational DBACommented:
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
 
NBFAuthor Commented:
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
 
PadawanDBAOperational DBACommented:
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
 
Eugene ZCommented:
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
 
NBFAuthor Commented:
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
 
PadawanDBAOperational DBACommented:
As long as you got it working, that's the important part!
0
 
NBFAuthor Commented:
Unable to find issue, recreated and working now.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now