SCCM: Workaround for CB1610 replica MP issue (sp_BgbConfigSSBForRemoteService)

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:

EXEC sp_BgbConfigSSBForRemoteService 'REPLICA', '<SQL Service Broker Port>', '<Certificate File Path>', '<Replica SQL Server FQDN>', '<Replica Database Name>'

 

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):

CREATE PROCEDURE [dbo].[sp_BgbConfigSSBForRemoteServiceTEST] (@DestSiteCode NVARCHAR(3),

replace with:

CREATE PROCEDURE [dbo].[sp_BgbConfigSSBForRemoteServiceTEST] (@DestSiteCode NVARCHAR(128),

5.  find this

CREATE USER ' + QUOTENAME(@sUser) + N' FOR LOGIN ' + QUOTENAME(@sLogin,'''') + N'

replace with:

CREATE USER ' + QUOTENAME(@sUser) + N' FOR LOGIN ' + QUOTENAME(@sLogin) + N'

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:

EXEC sp_BgbConfigSSBForRemoteServiceTEST 'REPLICA', '<SQL Service Broker Port>', '<Certificate File Path>', '<Replica SQL Server FQDN>', '<Replica Database Name>'

in my case it is:

EXEC sp_BgbConfigSSBForRemoteServiceTEST 'REPLICA', '4022', 'c:\sql.cer', 'CMRMP01.z-it.com', 'ZIT_REP'

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.