SharePoint 2010 User Profile Sync Job has been running for a week with no end in sight?

Problem:

As I was working with one of my customers this week we encountered a seemingly strange issue! Their user profile synchronization has been running for over a week to no end in sight. So I covered the basics to ensure the User Profile Synchronization Service and Service application are configured properly and according to the configuration guide titled Configure profile synchronization (SharePoint Server 2010) and it was.!?!?! But still the Synch job in question was still running; no errors in the logs both System and ULS.

 At this point I thought to myself it could be a performance issue so I went down the path of Performance Monitor and SQL Profiler still no solution in sight; so I decided to comb the Application Logs for anything small and big that maybe related and bingo there I see the following events:

Source: MsiInstaller
Event ID: 1004
Level: Warning
Keywords: Classic
User: NETWORK SERVICE
Description:
Detection of product '{90140000-104C-0000-1000-1000000FF1CE}', feature 'PeopleILM', component '{1C12B6E6-898C-4D58-9774-AAAFBDFE273C}' failed. The resource %programfiles%\Microsoft Office Servers\14.0\SQL\DatabaseSettings.SQL' does not exist.

Source: MsiInstaller
Event ID: 1001
Level: Warning
Keywords: Classic
User: NETWORK SERVICE

Detection of product '{90140000-104C-0000-1000-1000000FF1CE}', feature 'PeopleILM' failed during request for component '{1681AE41-ADA8-4B70-BC11-98A5A4EDD046}'

Source: MsiInstaller
Event ID: 1015
Level: Warning
Keywords: Classic
User: NETWORK SERVICE

Failed to connect to server. Error: 0x80070005

 

At this point I decided to chase the databasesettings.sql file only to successfully locate it within the same path indicated in event ID 1004, hmmmm now I am thinking I am running into an undocumented issue.

Not so fast; doing a little research I found the following

 Cause:

The Network Service account does not have rights to the %programfiles%\Microsoft Office Servers\14.0 folder.

 Problem Resolution:  

1)       Grant the Network Service account read access to the %programfiles%\Microsoft Office Servers\14.0 folder

2)       Start a full User Profile Synchronization and voila :)

3)      Almost 5 hours later job is done successfully!!

4)       For More details review the full KB article https://support.microsoft.com/kb/2473430/de