Getting error creating replication subscriber on SQL 2017 with SQL 2022 Distributor and the Subscriber is Azure Managed Instance. Error on sp_MSadd_distribution_agent Line Number 406

Question

Problem: 

All my replication that connected from my SQL 2017 Publishers that use a SQL 2022 Distributor to send replicated data to our Azure Managed Instance started erroring when I created subscribers to the Managed Instances using my SQL 2022 Distributor as a Publisher.     That publication required no port number to connect to the managed instances.  Previously all publications coming from the SQL 2017 Publishers required or were connecting with the default port.   Now they can’t connect with either.  No new ones, no dropped and recreated ones.

 

The publications that send data to on-prem subscribers and use the same distributor work fine.  It’s only those publications that go from the SQL 2017 publishers using the SQL 2022 distributor that are erroring. 

 

The new publication on the SQL 2022 distributor with subscribers that to the 2 managed instances (both prod and test) works fine also.

Does anyone know how to fix this? I think it has to do with a SQL 2022 bug which may be related to this SQL 2019 bug. But I'm not sure how to fix this one.

https://support.microsoft.com/en-us/topic/kb4563348-after-upgrading-sql-server-2017-distributor-to-sql-server-2019-transactional-push-replication-fails-when-subscriber-is-using-non-default-port-d16cdcdd-575c-65a2-78a3-6baffa547d1f#Symptoms

I have a ticket open with our Azure support plan since the connections to the Managed Instance are the only ones effected in this case. However I have met with 3 sets of analysts and still haven't gotten an answer. It's kind of a cross between on-prem and Azure functionality.

Answer 1

Hi Julie Hollander •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting error creating replication subscriber on SQL 2017 with SQL 2022 Distributor and the Subscriber is Azure Managed Instance. Error on sp_MSadd_distribution_agent Line Number 406.

Firstly, thanks for the detailed verbatim of the issue and we are sorry about the inconvenience.

The problem appears to need investigation into all parameters involved in the scenario.

Since you already have a support case opened with team, I would recommend you to follow-up with the Engineer assigned there to assist you.

Let us know if you have any queries.

Thanks