Named Pipes Provider: Could not open a connection to SQL Server [5].

Question

Named Pipes Provider: Could not open a connection to SQL Server [5].

Ram:)

SSCrazy

Points: 2246
More actions
July 13, 2017 at 1:21 am

#322627
Hi,
We have configured Transactional Replication between SERVER A and SERVER B and Server C is Configured as distributor. When I check the SnapShot agent status, It is showing as "Named Pipes Provider: Could not open a connection to SQL Server [5]. "
- SQL browser is running as all servers
- Remote Desktop is enabled.
- Ping is happening between the servers
- TCP/IP and NamedPipes have been enabled in Network configuration protocols
- Shared Memory(1), TCP/IP(2), NamedPipes(3) are in the given order
I am struck here in debugging the issue. When I checked https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service,
it understood that NamedPipes will use SQL Browser's default port 1434. I thought to check whether this port is added in firewall exception. But before that I feel to post the issue here to get right solution and check list.
Please help.
🙂

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Did you see the error in Replication Monitor? Was the snapshot generated? From the agents tab, select and right click the snapshot agent and then select Properties. That should open of the Job Properties window for whatever the job is called. Then go to jobs in SSMS and check the history for that job. See if you can't find additional information on the error.

Was the snapshot ever generated? It would be in the repl share. Right click the publication, select properties, go to the Snapshot page. The share is listed on the the middle of that page. In that properties window, go to Agent security on the bottom left and check the snapshot security settings. Check the account being used to connect to the publisher.

You did do a good job checking things - there are just a lot with replication. One thing you missed is the linked server. The linked server named Repl_distributor is used by publishers to connect to distributors. The distributor also uses it to verify roles and replication stored procedures. Query sys.servers and make sure that linked server has remote login and RPC enabled. It should be marked is_system and is_publisher.

And check the account used by the linked server- the default is distributor_admin. When you are on the properties page of the publication, go down to the Publication Access List and make sure the distributor_admin as well as all the accounts you just checked earlier (from Agent Security page) are listed.
In Replication Monitor, on the agents tab, right click on the snapshot agent and select Agent Profile. If you click on the ellipsis/three dots, it will bring up the agent properties. Try increasing the login timeout.

Sue

Ram:) SSCrazy Points: 2246 More actions · Answer 2

Sue_H - Thursday, July 13, 2017 4:04 PM
Did you see the error in Replication Monitor? Was the snapshot generated? From the agents tab, select and right click the snapshot agent and then select Properties. That should open of the Job Properties window for whatever the job is called. Then go to jobs in SSMS and check the history for that job. See if you can't find additional information on the error.

Here is the log When I see the Job history
2017-07-14 08:22:55.01 [0%] The replication agent had encountered an exception.
2017-07-14 08:22:55.01 Source: Replication
2017-07-14 08:22:55.01 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2017-07-14 08:22:55.01 Exception Message: Named Pipes Provider: Could not open a connection to SQL Server [5].

Sue_H - Thursday, July 13, 2017 4:04 PM
Was the snapshot ever generated? It would be in the repl share. Right click the publication, select properties, go to the Snapshot page. The share is listed on the the middle of that page. In that properties window, go to Agent security on the bottom left and check the snapshot security settings. Check the account being used to connect to the publisher.

I Can see the replication files has been generated in Repl share folder.

Sue_H - Thursday, July 13, 2017 4:04 PM

You did do a good job checking things - there are just a lot with replication. One thing you missed is the linked server. The linked server named Repl_distributor is used by publishers to connect to distributors. The distributor also uses it to verify roles and replication stored procedures. Query sys.servers and make sure that linked server has remote login and RPC enabled. It should be marked is_system and is_publisher.

I can see the below data when I query sys.servers @publisher server

name	is_remote_login_enabled	is_rpc_out_enabled	is_system	is_publisher	is_distributor
repl_distributor	1	1	1	0	1

Sue_H - Thursday, July 13, 2017 4:04 PM

And check the account used by the linked server- the default is distributor_admin. When you are on the properties page of the publication, go down to the Publication Access List and make sure the distributor_admin as well as all the accounts you just checked earlier (from Agent Security page) are listed.

I check the Linked server properties and it is using distributor_admin. But when I see Logins I cannot see any login name with that. But Login name is available at distributor server and Subscription server.

Sue_H - Thursday, July 13, 2017 4:04 PM
In Replication Monitor, on the agents tab, right click on the snapshot agent and select Agent Profile. If you click on the ellipsis/three dots, it will bring up the agent properties. Try increasing the login timeout.

Please suggest me on this.

🙂

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Try increasing the Login Timeout property from the default 15 seconds to 30 seconds or 60 seconds.

Sue