The process could not connect to Subscriber , transaction replication

Question

Error messages: The process could not connect to Subscriber 'DESKTOP-2D64Q3G\MSSQLSERVER02'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Login failed for user 'NT Service\SQLAgent$MSSQLSERVER01'. (Source: MSSQLServer, Error number: 18456) Get help: http://help/18456

Answer 1

Only dumping an error message is not a good way if you want assistance. You should always include some context of what you are trying to do and a short description of your configuration. Now we can only guess.

And it seems that you are trying to set up replication between two instances on your laptop and that you have set up replication to use the SQL Server Agent account. (Which I seem to recall that the wizard says is not recommended.)

It seems that when you have installed SQL Server, you have gone with the default which is to have one local Managed Service Account for each instance. This is also good practice, because normally you want to keep the instances separated. So the best would be to use an account that has access to both the distributor and the subscriber.

Depending on what you want to test, you could resolve the problem by simply granting the service account for Agent on MSSQLSERVER01 access to the second instance:

CREATE LOGIN "NT Service\SQLAgent$MSSQLSERVER01" FROM WINDOWS
go
USE SubscriberDB
go
CREATE USER "NT Service\SQLAgent$MSSQLSERVER01" 

Then you will also need to grant this account the required permissions, but I don't know these on the top of my head.

It is important to understand that if you would do this for real at work, you would not use this solution, but rather you would make sure that you have the proper accounts.

Answer 2

Hi @Marly Magdy2 ,

Thank you for your reaching out and welcome to Microsoft Q&A!

I agree with Erland, this error indicates that the Distribution Agent could not connect to the subscriber, because the login failed for user DESKTOP-2D64Q3G\MSSQLSERVER02.

Please refer to this thread which provided the steps in detail to resolve the issue.

In addition, you could connect to the subscriber and open the current SQL Server error log under the Management node in Object Explorer. If the issue cannot be resolved, could you please post the detailed error log here to help us narrow down the issue? Thank you for your understanding!

Feel free to share your issues here if you have any concerns.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications