Holy cow. Finally got my User Profile Synchronisation Service started successfully!! I’ve been cracking my head for these few days, tried reading and reading again the MSDN UPS Configuration guides. It’s just not an easy task to get it setup properly. Apart from that, would like to thank Harbar for the great work to note down all the necessary requirement for UPS

Evidence

Just to summarize all the requirement in case i may forget.

1. The Service Account running the “Windows Service – User Profile Synchronization Service” MUST BE to be your SharePoint Farm Account (Check here on how to find your SharePoint Farm Account)
2. The Service Account (Which is also the Farm Account) has to be granted with Local Administrator rights in machine which runs the User Profile Synchronisation Service Instance. At least when provisioning the service. (in layman’s term, when clicking the “Start” action link from “Manage Services on Server” in Central Admin). Check here on how to grant Administrator right. Why? Reason being that the provisioning requires modification on Server’s Registry which required administrator right to do so. Starting the UPS without Admin rights will cause Unauthorized Access. You may try and check SharePoint Log Files =).
3. The Service Account (Which is also the Farm Account) has to be granted with Allow Log on Locally right. You can do this via group policy editor (GPEdit) in your Domain Controller machine. Check here on how to assign permission. Granting the access in step 2 is sufficient to get the service started. However, it is always not recommended to grant Farm Account with Administrators right. By revoking the Administrator right after provision, the Allow Log on Locally right will be gone too! Hence, it is advisable to grant explicitly the permission via GPEdit.

Note. Those are the requirement to get the User Profile Synchronisation Services STARTED. There needs additional set to setup the synchronisation connection.