I ran into a problem while starting the User Profile Synchronization service. It wouldn’t start…
I found that a bit odd, because for several months my SharePoint 2010 deployments are done using PowerShell and every time the User profile Synchronization service started nicely. The first thing I did was opening the ULS viewer and sorted to Category – User Profiles. The ULS viewer than presented me this error:
ILM Configuration: Error ‘ERR_CONFIG_DB’
In and effort in solving the problem I read (again) Spence Harbars blogs: Stuck on Starting & Rational Guide to implement SharePoint 2010 User Profile Synchronization. The error I got is mentioned by Spence and is caused due to insufficient privileges of the SharePoint Farm Account on the Profile Sync DB.
So I went to my SQL Management Studio and looked at the privileges of the SharePoint Farm Account on the Profile Sync DB. I was a bit surprised to see that the SharePoint Farm Account had the correct privileges.
So if I had the correct privileges and my PowerShell script had never failed before, why now?
After searching for quite some time I found the solution to my problem!
As a best practice we created a SQL Alias, so nothing new about that. It turns out that MIIS, when configuring the Profile Sync DB, problems has when naming the SQL Alias as the instance name.
So when you got SQL server\Sharepoint and an alias as Sharepoint, the User Profile Synchronization Service would not start. When I used SQL server\instance name, the service started within 200 seconds.