Month: June 2021

Problem

Suppose that you setup a new Transactional Replication topology in Microsoft SQL Server, the scripts execute successfully and/or the wizard shows no errors. Despite this, when you try to run the log reader, snapshot, distribution or other agent for the first time it starts up successfully but then stops. When you check the job history or the replication monitor you might see an error like this:

2021-06-23 18:31:45.110 Status: 0, code: 20015, text: ‘Named Pipes Provider: Could not open a connection to SQL Server [2]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Query timeout expired, Failed Command: ‘.

2021-06-23 18:31:45.110 Named Pipes Provider: Could not open a connection to SQL Server [2]. A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Query timeout expired, Failed Command:

2021-06-23 18:32:45.233 Microsoft SQL Server Log Reader Agent 13.0.5888.11

This error is normally caused by either a connection timeout or improper permissions on the agent account.

Troubleshooting

The first thing you should do is confirm that the agent is using the account you think it is using. In this case I am checking the Log Reader Agent. You can verify the account that is in use via SSMS:

For Log Reader Agent and Snapshot Agent accounts:

Object Explorer -> Replication -> Local Publications -> Right Click [your_publication] -> Properties -> Agent Security -> Log Reader Agent

For Distribution Agent and Merge Agent accounts:

Object Explorer -> Replication -> Local Publications -> Expand [your_publication] -> Right Click [your_subscription] -> Properties -> Expand Security

Next, you should make sure that you can successfully establish a connection bidirectionally between your Publisher <-> Distributor, and Distributor<-> Subscriber.

An easy way to do this is with the UDL Test.

NOTE: Remember that for replication to work properly you need to have SQL Server ports (1433 by default for the default instance) open for both incoming and outgoing connections on your Publisher, Distributor and Subscriber(s).

In special circumstances with an unreliable network or if you’re using Replication in conjunction with Always On AG, it may be necessary to increase the server connection timeout and/or the linked servers remote login timeout which you can do with the following TSQL scripts:

Connection Timeout:

/*This has  to be done for each linked server. Best to use 'remote login timeout' */

EXEC master.dbo.sp_serveroption 
    @server=N'repl_distributor', 
    @optname=N'connect timeout', 
    @optvalue=N'60' 

Remote Login Timeout:

/*This setting applies to all linked servers */
sp_configure 'remote login timeout (s)', 60 
go 
reconfigure 
go 

If you’re troubleshooting a distribution agent or a merge agent make sure that the agent account is added to the Publication Access List (PAL) which you can check via SSMS:

Object Explorer -> Replication -> Local Publications -> Right Click [your_publication] -> Properties -> Publication Access List

Lastly, we want to check the account type and make sure that the proper permissions are mapped. Below are the steps to check the account for the Log Reader Agent. For the Distribution Agent or Merge Agent you will follow a similar procedure except user mappings will be on the Subscriber instead of the Publisher. The steps for the distribution database still apply.

• If Log Reader Agent is Domain Account:
  • Connect to Publisher:
    • Check: Object Explorer -> Security -> Logins
      • If already DOES exists: Right click login -> properties -> User Mappings -> [your publication database] -> Make sure “Map” is checked and that db_owner is checked under “Database role membership”.
      • If does NOT exist: Right click Logins -> New Login…
        • Type: Windows Authentication
        • Login Name: chose a domain account that has access to both the publisher and distributor server
        • User Mapping: [your publication database] -> Make sure “Map” is checked and that db_owner is checked under “Database role membership”.
  • Connect to Distributor:
    • Repeat all steps above for the [distribution] database

• If Log Reader Agent is Local Windows Account (non-domain):
  • Make sure that you can successfully sign into Windows on both the Publisher server and the Distributor Server with each account AND that they both have the same password. (If passwords are different, passthrough authentication will not work).
  • Connect to Publisher:
    • Check: Object Explorer -> Security -> Logins
      • If already DOES exists: Right click login -> properties -> User Mappings -> [your publication database] -> Make sure “Map” is checked and that db_owner is checked under “Database role membership”.
      • If does NOT exist: Right click Logins -> New Login…
        • Type: Windows Authentication
        • Login Name: chose a local account that on both the publisher and distributor server (and has the same password)
        • User Mapping: [your publication database] -> Make sure “Map” is checked and that db_owner is checked under “Database role membership”.
  • Connect to Distributor:
    • Repeat all steps above for the [distribution] database

For more information about replication security and required permissions, see the official Microsoft docs page: Replication Agent Security Model