SharePoint 2010: User Profile Synchronization Service will not start when using Named Instance in SQL Server 2008

While deploying a SharePoint 2010 farm recently in which I implemented database mirroring in SQL Server 2008 using two servers with a Named Instance respectively for each server (SQL1\PRINCIPAL and SQL2\MIRROR), I could not get the User Profile Synchronization Service to start. It would remain in a status of “Starting” for about 10 minutes before eventually showing a status of “Stopped”.

A quick glance on the ULS logs and I discovered the following errors:

ERROR ILMPostSetupConfiguration: ILM Configuration: Validating installation of SQL Service FAILED

and

ILM Configuration: Error 'ERR_CONFIG_DB'

and

UserProfileApplication.SynchronizeMIIS: Failed to configure MIIS post database, will attempt during next rerun. Exception: System.Configuration.ConfigurationErrorsException: ERR_CONFIG_DB at Microsoft.Office.Server.UserProfiles.Synchronization.ILMPostSetupConfiguration.ValidateConfigurationResult(UInt32 result) at Microsoft.Office.Server.UserProfiles.Synchronization.ILMPostSetupConfiguration.ConfigureMiisStage2() at Microsoft.Office.Server.Administration.UserProfileApplication.SetupSynchronizationService(ProfileSynchronizationServiceInstance profileSyncInstance). 1125abef-5af4-4214-93c5-4d74e8d103fb

After reading through dozens of blog posts, especially this one, I determined that the Forefront Identity Manager Synchronization Service (FIMSynchronizationService) was trying to connect to the Default Instance of SQL1, instead of the correct Named Instance of SQL1\PRINCIPAL. Not sure if this is default behavior out of the box, but sure seems to be that way.

The Workaround – configure a SQL alias on the application server. Here’s how:

On the SharePoint application server that you want to start the User Profile Synchronization Service on, perform the following:

1) Open the SQL Server Client Network Utility.





On the General tab, ensure you enable the Named Pipes and TCP/IP protocols.

2) On the Alias tab, select to Add a new alias.



Ensure TCP/IP is selected as the Network library protocol. For the Server alias, type the name of the SQL server. For me, it was the principal server in the database mirror – SQL1. In the Connection parameters, for the Server name, type the entire SQL Instance Name. For me, it was SQL1\PRINCIPAL. Select OK.

3) Start the User Profile Synchronization Service again.

Walla! It starts correctly now!



Hope this helps.