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.
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),
CREATE PROCEDURE [dbo].[sp_BgbConfigSSBForRemoteServiceTEST] (@DestSiteCode NVARCHAR(128),
5. find this
CREATE USER ' + QUOTENAME(@sUser) + N' FOR LOGIN ' + QUOTENAME(@sLogin,'''') + N'
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.