The process could not connect to distributor

Troubleshooter: Find errors with SQL Server transactional replication

Troubleshooting replication errors can be frustrating without a basic understanding of how transactional replication works. The first step in creating a publication is having the Snapshot Agent create the snapshot and save it to the snapshot folder. Next, the Distribution Agent applies the snapshot to the subscriber.

This process creates the publication and puts it in the synchronizing state. Synchronization works in three phases:

1. Transactions occur on objects that are replicated, and are marked «for replication» in the transaction log.
2. The Log Reader Agent scans through the transaction log and looks for transactions that are marked «for replication.» These transactions are then saved to the distribution database.
3. The Distribution Agent scans through the distribution database by using the reader thread. Then, by using the writer thread, this agent connects to the subscriber to apply those changes to the subscriber.

Errors can occur in any step of this process. Finding those errors can be the most challenging aspect of troubleshooting synchronization issues. Thankfully, the use of Replication Monitor makes this process easy.

• The purpose of this troubleshooting guide is to teach troubleshooting methodology. It’s designed not to solve your specific error, but to provide general guidance in finding errors with replication. Some specific examples are provided, but the resolution to them can vary depending on the environment.
• The errors that this guide provides as examples are based on the Configuring transactional replication tutorial.

Troubleshooting methodology

Questions to ask

1. Where in the synchronization process is replication failing?
2. Which agent is experiencing an error?
3. When was the last time replication worked successfully? Has anything changed since then?

Steps to take

1. Use Replication Monitor to identify at which point replication is encountering the error (which agent?):
   • If errors are occurring in the Publisher to Distributor section, the issue is with the Log Reader Agent.
   • If errors are occurring in the Distributor to Subscriber section, the issue is with the Distribution Agent.
2. Look through that agent’s job history in Job Activity Monitor to identify details of the error. If the job history is not showing enough details, you can enable verbose logging on that specific agent.
3. Try to determine a solution for the error.

Find errors with the Snapshot Agent

The Snapshot Agent generates the snapshot and writes it to the specified snapshot folder.

1. View the status of your Snapshot Agent: a. In Object Explorer, expand the Local Publication node under Replication. b. Right-click your publication AdvWorksProductTrans >View Snapshot Agent Status.
2. If an error is reported in the Snapshot Agent status, you can find more details in the Snapshot Agent job history: a. Expand SQL Server Agent in Object Explorer and open Job Activity Monitor. b. Sort by Category and identify the Snapshot Agent by the category REPL-Snapshot. c. Right-click the Snapshot Agent and then select View History.
3. In the Snapshot Agent history, select the relevant log entry. This is usually a line or two before the entry that’s reporting the error. (A red X indicates errors.) Review the message text in the box below the logs:

The replication agent had encountered an exception. Exception Message: Access to path '\\node1\repldata. ' is denied. 

If your Windows permissions are not configured correctly for your snapshot folder, you’ll see an «access is denied» error for the Snapshot Agent. You’ll need to verify permissions to the folder where your snapshot is stored, and make sure that the account used to run the Snapshot Agent has permissions to access the share.

Find errors with the Log Reader Agent

The Log Reader Agent connects to your publisher database and scans the transaction log for any transactions that are marked «for replication.» It then adds those transactions to the distribution database.

1. Connect to the publisher in SQL Server Management Studio. Expand the server node, right-click the Replication folder, and then select Launch Replication Monitor: Replication Monitor opens:
2. The red X indicates that the publication is not synchronizing. Expand My Publishers on the left side, and then expand the relevant publisher server.
3. Select the AdvWorksProductTrans publication on the left, and then look for the red X on one of the tabs to identify where the issue is. In this case, the red X is on the Agents tab, so one of the agents is encountering an error:
4. Select the Agents tab to identify which agent is encountering the error:
5. This view shows you two agents, the Snapshot Agent and the Log Reader Agent. The one that’s encountering an error has the red X. In this case, it’s the Log Reader Agent. Double-click the line that’s reporting the error to open the agent history for the Log Reader Agent. This history provides more information about the error:

Status: 0, code: 20011, text: 'The process could not execute 'sp_replcmds' on 'NODE1\SQL2016'.'. The process could not execute 'sp_replcmds' on 'NODE1\SQL2016'. Status: 0, code: 15517, text: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Status: 0, code: 22037, text: 'The process could not execute 'sp_replcmds' on 'NODE1\SQL2016'.'. 

-- set the owner of the database to 'sa' or a specific user account, without the brackets. EXECUTE sp_changedbowner '' -- example for sa: exec sp_changedbowner 'sa' -- example for user account: exec sp_changedbowner 'sqlrepro\administrator' 

You might need to restart the Log Reader Agent: a. Expand the SQL Server Agent node in Object Explorer and open Job Activity Monitor. b. Sort by Category and identify the Log Reader Agent by the REPL-LogReader category. c. Right-click the Log Reader Agent job and select Start Job at Step.

Select the AdvWorksProductTrans publication, select the Agents tab, and double-click the Log Reader Agent to open the agent history. You should now see that the Log Reader Agent is running and either is replicating commands or has «no replicated transactions»:

Find errors with the Distribution Agent

The Distribution Agent finds data in the distribution database and then applies it to the subscriber.

1. Connect to the publisher in SQL Server Management Studio. Expand the server node, right-click the Replication folder, and then select Launch Replication Monitor.
2. In Replication Monitor, select the AdvWorksProductTrans publication, and select the All Subscriptions tab. Right-click the subscription and select View Details:
3. The Distributor to Subscriber History dialog box opens and clarifies what error the agent is encountering:

Error messages: Agent 'NODE1\SQL2016-AdventureWorks2022-AdvWorksProductTrans-NODE2\SQL2016-7' is retrying after an error. 89 retries attempted. See agent job history in the Jobs folder for more details. 

The error indicates that the Distribution Agent is retrying. To find more information, check the job history for the Distribution Agent: a. Expand SQL Server Agent in Object Explorer >Job Activity Monitor. b. Sort the jobs by Category. c. Identify the Distribution Agent by the category REPL-Distribution. Right-click the agent and select View History.

Select one of the error entries and view the error text at the bottom of the window:

Message: Unable to start execution of step 2 (reason: Error authenticating proxy NODE1\repl_distribution, system error: The user name or password is incorrect.) 

This error indicates that the password that the Distribution Agent used is incorrect. To resolve it: a. Expand the Replication node in Object Explorer. b. Right-click the subscription >Properties. c. Select the ellipsis (. ) next to Agent Process Account and modify the password.

Check Replication Monitor again, by right-clicking Replication in Object Explorer. A red X under All Subscriptions indicates that the Distribution Agent is still encountering an error. Open the Distribution to Subscriber history by right-clicking the subscription in Replication Monitor >View Details. Here, the error is now different:

Connecting to Subscriber 'NODE2\SQL2016' Agent message code 20084. The process could not connect to Subscriber 'NODE2\SQL2016'. Number: 18456 Message: Login failed for user 'NODE2\repl_distribution'. 

This error indicates that the Distribution Agent could not connect to the subscriber, because the login failed for user NODE2\repl_distribution. To investigate further, connect to the subscriber and open the current SQL Server error log under the Management node in Object Explorer: If you’re seeing this error, the login is missing on the subscriber. To resolve this error, see Permissions for replication.

After the login error is resolved, check Replication Monitor again. If all issues have been addressed, you should see a green arrow next to Publication Name and a status of Running under All Subscriptions. Right-click the subscription to open the Distributor To Subscriber history once more to verify success. If this is the first time you’re running the Distribution Agent, you’ll see that the snapshot has been bulk copied to the subscriber:

Enable verbose logging on any agent

You can use verbose logging to see more detailed information about errors occurring with any agent in the replication topology. The steps are the same for each agent. Just make sure that you’re selecting the correct agent in Job Activity Monitor.

The agents can be on either the publisher or the subscriber, based on whether it’s a pull or push subscription. If the agent isn’t available on the server you’re investigating, check the other server.

1. Decide where you want the verbose logging to be saved, and ensure that the folder exists. This example uses c:\temp.
2. Expand the SQL Server Agent node in Object Explorer and open Job Activity Monitor.
3. Sort by Category and identify the agent of interest. This example uses the Log Reader Agent. Right-click the agent of interest >Properties.
4. Select the Steps page, and then highlight the Run agent step. Select Edit.
5. In the Command box, start a new line, enter the following text, and select OK:

-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 3 

You can modify the location and verbosity level according to your preference.

• There’s a formatting issue where the dash became a hyphen.
• The location doesn’t exist on disk, or the account that’s running the agent lacks permission to write to the specified location.
• There’s a space missing between the last parameter and the -Output parameter.
• Different agents support different levels of verbosity. If you enable verbose logging but your agent fails to start, try decreasing the specified verbosity level by 1.

Related content

• Transactional replication overview
• Replication tutorials
• ReplTalk blog

Get help

• Ideas for SQL: Have suggestions for improving SQL Server?
• Microsoft Q & A (SQL Server)
• DBA Stack Exchange (tag sql-server): Ask SQL Server questions
• Stack Overflow (tag sql-server): Answers to SQL development questions
• Reddit: General discussion about SQL Server
• Microsoft SQL Server License Terms and Information
• Support options for business users
• Contact Microsoft
• Additional SQL Server help and feedback

Contribute to SQL documentation

Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.

SQL Replication ‘The process could not connect to Distributor’ between vps and laptop

I have 1 SQL server 2017 in London (VPS) and I connect with remote on port 1413 with ‘sa’ user , that work perfect. I config Distributor and Publisher successfully as shown as on server B : And create subscriber on server A (my Laptop) as shown as bellow: And subscriber created and started successfully but after 30 seconds show error ‘The process could not connect to Distributor’ as shown as : Additional information:’ (from User sa) refused because the job is already running from a request by User sa. Changed database context to ‘PUB4′. (.Net SqlClient Data Provider)’

• sql-server
• replication
• transactional-replication

asked Sep 26, 2020 at 12:56
545 7 7 silver badges 30 30 bronze badges

Replicating over a WAN, with a VPN connection, to a laptop? Replication is designed with high availability in mind, I doubt your laptop is going to be permanently switched on, nor always connected the the VPN, so why are you setting up replication here?

Sep 26, 2020 at 12:59
server A is my laptop and always on, server B is VPS , I don’t have VPN connection.
Sep 26, 2020 at 13:07

Side question, why are you so against updates? SQL Server 14.0.1000.169 is the RTM version of SQL Server 2017. We’re on CU 22 now. You’ve been ignoring updates for around 2 years.

Sep 26, 2020 at 13:07

«I don’t have VPN connection» So you have an unpatched RTM server open to the world, and you’re connected with an sa account?! I’d start looking at security before you start worrying about this. But a Laptop, connected to via the Internet, is not a good candidate as a replication destination or source. A laptop on it’s own isn’t a good candidate as a permentent host, it’s not a «server», let alone for this set up.

Sep 26, 2020 at 13:09

There have also been multiple security patches on SQL Server 2017 (Build List) so, in my opinion, you are really playing with fire here. The build you have is actually almost 3 years old (not the 2 I said)!

The process could not connect to distributor

Category:AGENT
Source: Merge Process
Number: 20084
Message: The process could not connect to Distributor ‘xxxxxxxxxx’.

Environment:
Replication between 2 servers over the internet

Actions on Publisher:
Publisher creates a user account
User account is listed as system admin
User account has public and owner permissions on database
Script database and logins
Create Publication
(Successful)

Actions on Subscriber:
From the Subscriber
Register the Publisher (with sql account as described below)

Build schema on subscriber with script

Create the same user account with same permissions as done on Publisher
User account is listed as system admin
User account has public and owner permissions on database

Create Pull Subscription
(successful)
*schedule — 5 minutes

Review Job History
Login failed for user ‘xxxxxx’.

Review Event Viewer
SubSystem Message — Job ‘xxxxx- 0’ (0x8ED71BBBDB36FB4491250C03332ED387),
step 1 — The process could not connect to Distributor ‘xxxx’.
Login failed for user ‘xxxxxxx’.

Test login account:
From the Subscriber
Register the Publisher

On the subscriber, at the start menu
Start | Programs | Microsoft SQL Server | Query Analyzer
Select Publisher
Select SQL Server Authentication
Enter login account

2007-06-02 15:37:01 UTC

Paul, if you read this!

I just finished reading your article
«SQL Server Replication Across Domains and the Internet»

My position is similar except for the following 2 itemes:
I am not allowed an FTP site on either the publisher/distributor or
subscriber.
Both publisher/Distributor and subscriber are limited to Windows only
authentication.

Sounds like I need to setup pass through authentication but I do not know
how to do this. Could you provide details?

Post by mj
Category:AGENT
Source: Merge Process
Number: 20084
Message: The process could not connect to Distributor ‘xxxxxxxxxx’.
Replication between 2 servers over the internet
Publisher creates a user account
User account is listed as system admin
User account has public and owner permissions on database
Script database and logins
Create Publication
(Successful)
From the Subscriber
Register the Publisher (with sql account as described below)
Build schema on subscriber with script
Create the same user account with same permissions as done on Publisher
User account is listed as system admin
User account has public and owner permissions on database
Create Pull Subscription
(successful)
*schedule — 5 minutes
Review Job History
Login failed for user ‘xxxxxx’.
Review Event Viewer
SubSystem Message — Job ‘xxxxx- 0’ (0x8ED71BBBDB36FB4491250C03332ED387),
step 1 — The process could not connect to Distributor ‘xxxx’.
Login failed for user ‘xxxxxxx’.
From the Subscriber
Register the Publisher
On the subscriber, at the start menu
Start | Programs | Microsoft SQL Server | Query Analyzer
Select Publisher
Select SQL Server Authentication
Enter login account
Successful connection
Help!
MJS

Paul Ibison
2007-06-02 18:16:56 UTC

I’ve not set this up using windows authentication, but you’ll need the same
account and password set up on each machine and the agent to use this
account. For non-FTP processing, you can use a nosync initialization.
HTH,
Paul Ibison

2007-06-03 17:20:01 UTC

Review:
Problem: Cannot connect to Distributor. Login Failed…..

Scenario:
1. Replication across the internet between non-trusted servers
2. Windows 2003 setup as described in NSA guideline
3. SQL Server 2000 setup as described in NSA guideline
4. SQL Server Agent login Account in each domain data server is unique to
the domain.
5. SQL Server authentication is Windows only
6. FTP is not allowed on data servers
7. Schema, database and logins are scripted on the publisher
8. Publisher/distributor have been successfully configured
9. Publication has been successfully configured
10. Schema, database and logins are created from script on subscriber
11. Subscriber successfully created Pull subscription of published publication

Hillary Cotter’s words on Creating Pass-through Accounts
Creating Pass-Through Accounts
“Log onto the publisher,
Select Start | Programs | Administrative Tools | Computer Management | Local
Users and Groups.
Right click on the Users folder and add a user
Deselect the User must change password at next login
Check password never expires
Select Create User
Return to Local Users and Groups
Select Groups
Add the new account to the Administrators group”

Addendum
Remove the “Everyone” group from the SQL Server installation drive or
partition.
Give the SQL Server 2000 service account, System and Administrators Full
Control over the installation drive or partition.

Thanks for answering on a saturday
MJ

Post by Paul Ibison
I’ve not set this up using windows authentication, but you’ll need the same
account and password set up on each machine and the agent to use this
account. For non-FTP processing, you can use a nosync initialization.
HTH,
Paul Ibison

Paul Ibison
2007-06-03 19:32:45 UTC

OK — did you follow Hilary’s guidelines on each server ie add the identical
account to the subscriber and publisher/distributor, and is the sql server
agent (subscriber agent for pull subscription) operating as that account?
Cheers,
Paul Ibison

2007-06-04 11:24:02 UTC

review:
replication between non-trusted domains over the internet.

Accounts with the same name and password were created on both machines.
Security Permissions were assigned each account
sql server agent login on server 1 IS NOT the same as sql server agent login
on server 2.
Security does not allow me to change the accounts.

Post by mj
Paul;
Problem: Cannot connect to Distributor. Login Failed…..
1. Replication across the internet between non-trusted servers
2. Windows 2003 setup as described in NSA guideline
3. SQL Server 2000 setup as described in NSA guideline
4. SQL Server Agent login Account in each domain data server is unique to
the domain.
5. SQL Server authentication is Windows only
6. FTP is not allowed on data servers
7. Schema, database and logins are scripted on the publisher
8. Publisher/distributor have been successfully configured
9. Publication has been successfully configured
10. Schema, database and logins are created from script on subscriber
11. Subscriber successfully created Pull subscription of published publication
Hillary Cotter’s words on Creating Pass-through Accounts
Creating Pass-Through Accounts
“Log onto the publisher,
Select Start | Programs | Administrative Tools | Computer Management | Local
Users and Groups.
Right click on the Users folder and add a user
Deselect the User must change password at next login
Check password never expires
Select Create User
Return to Local Users and Groups
Select Groups
Add the new account to the Administrators group”
Addendum
Remove the “Everyone” group from the SQL Server installation drive or
partition.
Give the SQL Server 2000 service account, System and Administrators Full
Control over the installation drive or partition.
Thanks for answering on a saturday
MJ

Post by Paul Ibison
I’ve not set this up using windows authentication, but you’ll need the same
account and password set up on each machine and the agent to use this
account. For non-FTP processing, you can use a nosync initialization.
HTH,
Paul Ibison

The process could not connect to distributor

Hi,
I’m getting the «The process could not connect to Distributor» error when
trying to do a pull subscription. Here is my setup. Machine1 set up as
publisher for a snapshot replication of entire database. Machine2 set up as
the distributor. Machine2 also set up with a pull subscription to
publication on Machine1 using itself as the distributor. The publication
was set up and initialized ok. When the subscription pulls I get this
error. Looking in the history I see 4 run agent steps with the following
messages:
Connecting to subscriber Machine2
Connecting to distributor Machine2
Login failed for user ‘sa’.
The process could not connect to Distributor Machine2. The step failed.

The only place I am using ‘sa’ (to my knowledge) is in the publisher
properties for Machine1 and the password is correct for it. All other
connections to Machine2 are using the «Impersonate SQL Server Agent Account»
which is the local administrator on Machine2.

Any ideas where this failed login is coming from. By the errors in the job
history it seems like the problem is where Machine2 is connecting to itself
to connect to the distributor but that doesn’t make sense. Thanks in
advance for any help.

Greg Yvkoff [MSFT]
2003-08-06 17:50:43 UTC

is -DistributorSecurityMode set to 1 for the distribution agent?
—
This posting is provided «AS IS» with no warranties, and confers no rights.

Post by Wayne Antinore
Hi,
I’m getting the «The process could not connect to Distributor» error when
trying to do a pull subscription. Here is my setup. Machine1 set up as
publisher for a snapshot replication of entire database. Machine2 set up as
the distributor. Machine2 also set up with a pull subscription to
publication on Machine1 using itself as the distributor. The publication
was set up and initialized ok. When the subscription pulls I get this
error. Looking in the history I see 4 run agent steps with the following
Connecting to subscriber Machine2
Connecting to distributor Machine2
Login failed for user ‘sa’.
The process could not connect to Distributor Machine2. The step failed.
The only place I am using ‘sa’ (to my knowledge) is in the publisher
properties for Machine1 and the password is correct for it. All other
connections to Machine2 are using the «Impersonate SQL Server Agent Account»
which is the local administrator on Machine2.
Any ideas where this failed login is coming from. By the errors in the job
history it seems like the problem is where Machine2 is connecting to itself
to connect to the distributor but that doesn’t make sense. Thanks in
advance for any help.
Wayne

Wayne Antinore
2003-08-07 14:27:01 UTC

Hi Greg,
Thanks for getting back to me. How do I check this? When I look at the
profiles for the agent in EM I don’t see that as one of the listed ones.
Thanks,
Wayne

Post by Greg Yvkoff [MSFT]
is -DistributorSecurityMode set to 1 for the distribution agent?
—
This posting is provided «AS IS» with no warranties, and confers no rights.
thanks.
greg

Post by Wayne Antinore
Hi,
I’m getting the «The process could not connect to Distributor» error when
trying to do a pull subscription. Here is my setup. Machine1 set up as
publisher for a snapshot replication of entire database. Machine2 set

Post by Wayne Antinore
the distributor. Machine2 also set up with a pull subscription to
publication on Machine1 using itself as the distributor. The publication
was set up and initialized ok. When the subscription pulls I get this
error. Looking in the history I see 4 run agent steps with the following
Connecting to subscriber Machine2
Connecting to distributor Machine2
Login failed for user ‘sa’.
The process could not connect to Distributor Machine2. The step failed.
The only place I am using ‘sa’ (to my knowledge) is in the publisher
properties for Machine1 and the password is correct for it. All other
connections to Machine2 are using the «Impersonate SQL Server Agent

Post by Wayne Antinore
which is the local administrator on Machine2.
Any ideas where this failed login is coming from. By the errors in the

Post by Wayne Antinore
history it seems like the problem is where Machine2 is connecting to

Post by Wayne Antinore
to connect to the distributor but that doesn’t make sense. Thanks in
advance for any help.
Wayne