UPS stuck on Starting (Event ID 234 / 22)

Installing SharePoint 2013, EASY…. or is it….. So after installing SP and then configuring the UPS, I found that the User Profile Sync was stuck on Starting.

Now of course being me, I dove headfirst as to why could this be happening… All the correct accounts have the correct access…hmmm.

So I decided to whais and trace what was happening.

Observation:

The Event Log has below entries:

Event ID:      234
Description:
ILM Certificate could not be created: Cert step 2 could not be created: C:\Program Files\Microsoft Office Servers\14.0\Tools\MakeCert.exe -pe -sr LocalMachine -ss My -a sha1 -n CN=”ForefrontIdentityManager” -sky exchange -pe -in “ForefrontIdentityManager” -ir localmachine -is root
Event ID:      234
Description:
ILM Certificate could not be created: netsh http error:netsh http add urlacl url=http://+:5725/ user=Domain\spfarm sddl=D:(A;;GA;;;S-1-5-21-2972807998-902629894-2323022004-1104)
Event ID:      22
Description:
The Forefront Identity Manager Service cannot connect to the SQL Database Server. The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.
Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

What can be causing the issue:

The Certificate store already has the Forefront Identity Manager certificates.  Each time you perform a SharePoint backup including the user profile service application, right after finishing the backup job, the events are logged and another ForeFront certificate is added into the certificate store.

What is the reason behind it:

While provisioning the ForeFront Identity Manager, a self signed certificate is created for the Computer Account -> Trusted People certificate store used by the web service on port 5725.  At the time of SPBackup, the timerjob reprovisions the same steps as while creating the UPS initially. Step 1 creates the certificate and step 2 will issue the trust which fails because a signed certificate already exists.

The Solution:

Deleting all the FIM certificates in certmgr.msc

1. On Server that hosts the user profile service application, go to “Start” and type “mmc.exe”
2. Click “Add/Remove Snap-In”
3. Click “Certificates”
4. Click “Add”
   
   
5. Select Service account  
   
    
6. If you are on SharePoint Server that is running User profile service, choose “Local Computer”, else click “Another computer” and connect to it:
   
   
7. Select the service accounts to connect to “Service account” Forefront identity manager service and repeat the steps for the Forefront Identity manager synchronization service as well
8. Redo steps 2 and 3 and but choosing “Computer Account” instead
9. Redo steps 2 and 3 and but choosing “My User Account” instead
10. Click OK.
    
    
11. Delete all the FIM Certificates.
12. Start the UPS. It should work this time.

Ref: https://www.linkedin.com/pulse/ups-stuck-starting-event-id-234-22-juan-de-roock