Resolving Errors with Duplicate ConfigMgrEndpoint Certificates

  • Article

 

Overview

When setting up database replicas for management points in ConfigMgr 2012, it is possible that you could end up with a duplicate certificate in place when trying to configure SQL Broker. This article outlines that scenario and offers a way to resolve it.

Scenario

Recently while working with a customer to set up a database replica for one of their management points in ConfigMgr, we encountered the following situation:

 

  • We were working through the process of setting up the SQL Broker service, which is an essential element of configuring database replicas. Part of this process (explained in detail in this article) was to export and exchange ConfigMgrEndpoint certificates for each database server
  • On the primary database, we imported the certificate with a reference to the default instance of SQL on the database replica (an example of the stored procedure we used is shown below)

EXEC sp_BgbConfigSSBForRemoteService 'REPLICA', '4022', 'C:\Test\Cert.cer', 'S12-MP1.W2K12-LAB.LOCAL', 'CM_MP1'

  • After the stored procedure had completed, we remembered that the customer was using a named instance for their database. This means the stored procedure should have been written as follows:

EXEC sp_BgbConfigSSBForRemoteService 'REPLICA', '4022', 'C:\Test\Cert.cer', 'S12-MP1.W2K12-LAB.LOCAL', 'TestInstance\CM_MP1'

NOTE: The format is ‘<InstanceName>\<Database Name>’. In the example above, ‘TestInstance’ is just an example name I selected to illustrate the stored procedure with a named instance.

The problem came in when we re-ran the stored procedure. We received an error that said the following:

Msg 15232, Level 16, State 1, Line 17

A certificate with name 'ConfigMgrEndPointCert0x8eae7c6bf36b15ed2ea186928fcf6c4e' already exists or this certificate already has been added to the database.

Where is the ConfigMgrEndPointCert?

If you’ve worked very long with certificates in Windows, you’ll know that the default place to go when you need to find a certificate is the Certificates snap-in. We opened an MMC and added this snap-in, then proceeded to search through every container but we did not find the cert.

At this point, I thought perhaps the cert had an unexpected name, so I opened it (since I had previously exported it I knew where it was). To be clear, I was looking at the database replica certificate that I had imported to the primary database. To open the cert, simply right-click it and select ‘Open’ to see the information below:

clip_image002

I searched again, using the name highlighted above but I still could not find it. I also searched by Expiration Date but nothing matched up.

Finding the Certificate

Further research and collaboration with colleagues finally pointed me to the certificate. I learned by in order to see it, I needed to look inside of SQL Server Mgmt. Studio. To locate it, I did the following:

  • Opened SQL Server Mgmt. Studio and connected to the primary database server
  • In Object Explorer, I expanded the server and navigated to Server Objects\Endpoints\Service Broker. Within the Service Broker container, I could see the ConfigMgrEndpoint node was present

clip_image004

  • I right-clicked this node and selected ‘Script Endpoint as > CREATE To > New Query Editor Window’. This opened a query window with the following information visible:

clip_image005

But this is only part of the story. To find the actual certificate, I had to do the following:

  • Still in SQL Server Mgmt. Studio, I expanded Security\Logins (NOTE: Not the security tab within the database. Here, we are looking for the Security tab that is a peer of the Databases container)
  • Within Logins, I could see the ConfigMgrLogEndpointLogin account with the same hexadecimal code as was listed in the error message above, as the screenshot below shows:

clip_image007

I had found where the certificate was located. Now how to get it out of there?

Removing the Certificate

Deleting the certificate so I could run the correct stored procedure ended up being as simple as right-clicking the ConfigMgrEndpointLogin account and selecting ‘Delete’. Doing this removed the Login and I was able to run the stored procedure with the correct entries (in my case, <named instance>\<database name>) and the certificate imported without difficulty.

So What If I Need to Rebuild the ConfigMgrEndPoint Altogether

Hopefully this will never be necessary, but in order to be thorough I am outlining the process of deleting/re-importing the ConfigMgrEndpoint certificate as a part of recreating the ConfigMgrEndpoint object itself.

Before making any big changes to my SQL database, I always create a backup (something that should always be done before making any significant change in your database). In this case, however, it turns out that when I navigated to Server Objects\Endpoints\Service Broker\ConfigMgrEnpoint andselected ‘Script Endpoint as > CREATE To > New Query Editor Window’, I was exposing the SQL code necessary to recreate the ConfigMgrEndpoint node if needed.

With this in mind, it was straightforward to take the following steps:

  • I made sure the CREATE statement for ConfigMgrEndpoint was available in one of the Query windows. (NOTE: If the CREATE statement is not available, right-click the node again and select ‘Script Endpoint as > CREATE To > New Query Editor Window’)
  • Next, I right-clicked the ConfigMgrEndpoint node and selected ‘Delete’. This brought up the Delete Object wizard. I selected ‘OK’ to confirm the node’s deletion
  • At this point, I went into Security\Logins and deleted the ConfigMgrEndpointLogin login by right-clicking and selecting ‘Delete’ (I again got the Delete Object wizard and selected ‘OK’)

NOTE: At this point there is no ConfigMgrEndpoint node under Service Broker, so running the stored procedure to import the certificate from the database replica will fail.

  • I recreated the ConfigMgrEndpoint object by running the CREATE statement exposed in the query window. In my environment, this is the code (repeated here for convenience):

USE [master]

GO

/****** Object: Endpoint [ConfigMgrEndpoint] Script Date: 9/23/2014 9:27:40 AM ******/

CREATE ENDPOINT [ConfigMgrEndpoint]

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

  • Once I verified that the ConfigMgrEndpoint node was successfully recreated, I re-ran the stored procedure with the named instance specified as shown below:

EXEC sp_BgbConfigSSBForRemoteService 'REPLICA', '4022', 'C:\Test\Cert.cer', 'S12-MP1.W2K12-LAB.LOCAL', 'TestInstance\CM_MP1'

After taking each of these steps, the certificate imported again successfully.

Summary

While this issue is hopefully not something that is encountered frequently (or at all), it’s nice to know how to deal with it if it comes up. Hopefully this short guide provides that information.