SCCM: Workaround for CB1610 replica MP issue (sp_BgbConfigSSBForRemoteService)

By | January 9, 2017

Before you read this, please understand Microsoft does NOT supported this kind of direct SQL edit, so please do not use this “workaround” in production.
This is also a knowing issue, hotfix from Microsoft will come later.

UPDATED: KB4010155 update rollup fix this issue.
https://support.microsoft.com/en-us/help/4010155/update-rollup-for-system-center-configuration-manager-current-branch-v

 

 

I have been testing replica MP based on https://technet.microsoft.com/en-us/library/hh846234.aspx and https://sccmentor.com/2015/05/19/installing-a-replica-mp-in-configmgr-2012/ many months ago, using CB 1606. Everything works just fine.

In this weekend I was trying to do upgrade from 1606 to 1610, I noticed configure SQL Service Broker import certificate no longer works, later thanks for Paul Winstanley pointed out that I don’t need to repeat those Service Broker steps again since I have done it earlier. So seems everything is working fine.

But… well, I like answers, want to know why the import certificate in service broker step failed, so I rebuild a whole new test lab for CB 1610. When I tried to import the certificate that is in use on the database replica server to site database server using:

 

it did not work! So the problem was not related 1606 to 1610 upgrade.  Error message:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ‘ConfigMgrEndpointLoginREP’.
ERROR: Failed to setup SSB for remote service REP

I google that error message for 3 days, didn’t find anything… Reinstall everything again using different SQL version, didn’t help. So I decided install CB 1606 and see what is different, and it worked in CB1606. I did more diggings….

And I found sp_BgbConfigSSBForRemoteService query is not the same between CB1606 and CB1610 (also TP version)

You will find sp_BgbConfigSSBForRemoteService under “Programmability”

So this is how I fix my test environment, but I don’t suggest you do that in production.

1. Find sp_BgbConfigSSBForRemoteService, Create to a “New Query Editor Window”

2. Copy the SQL query to notepad

3. Find sp_BgbConfigSSBForRemoteServic and replace them as sp_BgbConfigSSBForRemoteServiceTEST.

4. find this (maybe in line 22):

replace with:

5.  find this

replace with:

6. Create a new query, copy the modified query to the new query, and then execute it. I mean all the lines, not only the modified lines. 🙂

7. Refresh, then you should able to see it like this.

8. Now try again import certificate by running this query again:

in my case it is:

9. It should be now executed successfully.

10. You should able to see a certificate is imported, name “ConfigMgrEndPointCertxxxxxxxxxxxxxxx”

11. Also you should able to see a new login account is created name “ConfigMgrEndpointLoginxxxxxxxx”, the ending numbers are sama as in the certificate name.

 

12.  Delete this sp_BgbConfigSSBForRemoteServiceTEST what you just created by using “DROP To” and new query, then execute.

Remember: THIS  IS PROVIDED “AS IS“, and NOT supported.

2 thoughts on “SCCM: Workaround for CB1610 replica MP issue (sp_BgbConfigSSBForRemoteService)

Leave a Reply

Your email address will not be published. Required fields are marked *