SharePoint 2010: User Profile Synchronization Service fails to start with a message "Failed to configure ILM" in ULS

What?

I have setup a new SharePoint 2010 non-production farm in a single server environment with Active Directory, SQL Server 2008 and SharePoint 2010 installed. (Not stand-alone installation)
The dreaded User Profile Synchronization Service fails to start as usual in a newly setup SharePoint 2010 farm.

The following error was found in the ULS logs:

UserProfileApplication.SynchronizeMIIS: Failed to configure ILM, will attempt during next rerun. Exception: System.Data.SqlClient.SqlException: Specified collection 'StringSchemaCollection' cannot be dropped because it is used by object...

Note that the above message shows up as an entry with High priority and not as an exception in ULS log.

Why?

There could be hell a lot of reasons why the User Profile Synchronization Service fails to start. First of all, it does not work in a single server stand alone installation with inbuilt database.

In my case, there were a couple of issues and I will try to explain how I fixed them in this post.


How?

User Profile Synchronization Service does not like fully qualified name for the database and therefore we got to be careful to not provide a fully qualified database name when running SharePoint 2010 Products Configuration Wizard for the first time.

For example DO NOT use DBSP01.DOMAINNAME.COM instead use DBSP01.

Add the User profile Synchronization user to Farm Administration Group.

Make sure the User profile Synchronization user in the Local Administrator Group.

Delegate the User profile Synchronization user to have Active directory access.

Melick has explained all the delegation process with nice screenshots here. Please follow the steps as he mentioned.

Execute the below command:

setspn –a NONE/NONE UserProfileSyncUserAccount

Restart SharePoint Timer Service

Run the below PowerShell script:

$sync_db = "Sync DB Name here (User Profile Service Application_SyncDB)"

$ups_service_app_name = "User Profile Service Application"

$syncdb=Get-SPDatabase | where {$_.Name -eq $sync_db}

$syncdb.Unprovision()

$syncdb.Status='Offline'

$ups = Get-SPServiceApplication  | where {$_.Displayname -eq $ups_service_app_name }

$ups.ResetSynchronizationMachine()

$ups.ResetSynchronizationDatabase()

$syncdb.Provision()

Restart SharePoint Timer Service

Change the 2 Forefont Identity Manager services to run using the appropriate account (if the UP sync still does not start) and start User Profile Synchronization Service again.

Depending on the CU that has been applied in your SharePoint server, you may have to recreate the User Profile Application.