Umair Khan's blog

Sharing my experience with System Center Configuration Manager and Microsoft.

ConfigMgr 2012: DRS and SQL service broker certificate issues

ConfigMgr 2012: DRS and SQL service broker certificate issues

  • Comments 6
  • Likes

Hi Folks,

Today's post is about the much talked about feature on ConfigMgr 2012- Data Replication Service (DRS). The replication mechanism internally uses SQL Service broker and I am discussing the three common issues with SSB that can stop the replication.

 

Issue 1: In the SQL error log we see the following:

'Connection handshake failed. Error 15581 occurred while initializing the private key corresponding to the certificate. The SQL Server errorlog and the Windows event log may contain entries related to this error. State 88.'

In the sys.transmission_queue we see this in the transmission_status column:

Service Broker login attempt failed with error: 'Connection handshake failed. An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'

Approach 1:

The history was the SQL server was installed with the system account and then later changed to a domain user account.

The problem with doing the above is that when Configuration Manager is installed it creates some internal certificates which are dependent on the master key. When the account being used to run the database server changes the new account is no longer able to 'unlock' the master key and consequently can not read the internal certificates which then cause communication between sites to fail.

In order to address this we dropped and regenerated the master key in the SQL database - this effort was hampered because the user account being used to run the SQL Service appeared not to have sufficient rights to generate a new master key. To combat this we temporarily put the user account into the local administrators group after which a new master key could be generated. We then regenerated the primary site server's certificate using the spCreateandBackupSQLCert stored procedure in the ConfigMgr database. Having successfully regenerated the SSB certificates we copied them to the CAS (parent) and Secondary (child) site and the SQL errorlogs no longer showed SQL Service Broker login failures.

Following this we left the sites to recover but overnight it appeared that some init messages from the Primary had become lost in transit - possibly cleared from the queue as a part of the spCreateandBackupSQLCert stored proc. We set the current rows with status < 3 in RCM_DRSInitilizationTracking to status = 7 to kick off the replication initialization process. This executed very quickly and replication appeared to be working as expected.

Approach 2:

To resolve this issue, give full control permissions to the account that the SQL Service is running under to the ProgramData\Microsoft\Crypto\RSA\MachineKeys directory.

 

Issue 2:

Service Broker login attempt failed with error: 'Connection handshake failed. The login 'ConfigMgrEndpointLoginCAS' does not have CONNECT permission on the endpoint. State 84.

In many cases the Endpoint itself would be missing we can create the same and give it the connect permissions:


CREATE ENDPOINT [ConfigMgrEndpoint] 
AUTHORIZATION 
[domain\account]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = 
ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED
, 
MESSAGE_FORWARD_SIZE = 5
, AUTHENTICATION = CERTIFICATE 
[ConfigMgrEndpointCert]
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

SQL > Security > logins > checked ConfigMGRENDPOINTLOGINCAS > properites > Securables and there were no permissions for the Connect for the user, Gave Grant for Connect.

 

Issue 3:

Service Broker login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: Certificate not found. State 89.'.  [CLIENT: 10.172.20.133]

This happens when the public key cert of the other SSB endpoint login for the ConfigMgr somehow goes missing on the other SSB endpoint login.

So suppose if I have the hierarchy is as follows-

PRI

    |

  SEC

   When I run the SpDiagDRS on the PRI site, I see that it cannot find the cert for the SEC site.

Then I export the cert from SEC by using the query:

 

 
Use master
Backup Certificate ConfigMgrEndpointCert TO 
FILE='C:\SEC.CER'

 

 

 

Then copy the SEC.CER file onto the primary site C:\SEC.cer.

After this we can connect to the Primary site DB and run the below query.

 
Use CM_PRI
Exec dbo.spCreateSSBLogin @EndPointLogin='ConfigMgrEndpointLoginSEC', 
@DestSiteCode='SEC', @DestSiteCertFile='C:\SEC.cer', 
@EndpointName='ConfigMgrEndpoint'

 

Hope it helps !

Umair Khan

Support Escalation Engineer | ConfigMgr Microsoft

  • thanks a lot for sharing

  • Amazing work :) Waiting for more DRS blogs from you :)

  • very very helpful !!! :)

  • hi... after " Use CM_PRI Exec dbo.spCreateSSBLogin @EndPointLogin='ConfigMgrEndpointLoginSEC', @DestSiteCode='SEC', @DestSiteCertFile='C:\SEC.cer', @EndpointName='ConfigMgrEndpoint'" I have error in SQL " The activated proc '[dbo].[spRCMActivation]' running on queue 'CM_RU2.dbo.ConfigMgrRCMQueue' output the following: 'ERROR 50000, Level 16, State 1, Procedure spRethrowError, Line 42, Message: ERROR 50000, Level 16, State 1, Procedure spRethrowError, Line 42, Message: ERROR 50000, Level 16, State 1, Procedure spGetSSBDialogHandle, Line 58, Message: Route is not defined for target site with service name ConfigMgrRCM_SiteTV2.' "

  • I see that you have used the SEC I the command and assume that it is the sitecode. From the error it seems that the route to the site is missing. You can view the same in the SQL management studio under your DB in the Service broker tab. Compare the number of routes from a working site and replicate/create the same route with the desired sitecode.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment