Cannot open database User Profile Service Application_SyncDB ... The login failed

Some of my servers were mysteriously no longer synchronizing the user profiles after some time. Upon further investigation this appeared to happen after a server reboot. Here is an error found in the Application Log with a source of Forefront Identity Manager:


.Net SqlClient Data Provider: System.Data.SqlClient.SqlException: Cannot open database "User Profile Service Application_SyncDB_35eb65afa42c4f8e9ece6a33e9348849" requested by the login. The login failed.
Login failed for user '[my user]'.
   at Microsoft.ResourceManagement.Data.Exception.DataAccessExceptionManager.ThrowException(SqlException innerException)
   at Microsoft.ResourceManagement.Data.DatabaseConnection.Open(SqlConnection connection)
   at Microsoft.ResourceManagement.Data.DatabaseConnection.Open(DataStore store)
   at Microsoft.ResourceManagement.Data.TransactionAndConnectionScope..ctor(Boolean createTransaction, IsolationLevel isolationLevel, DataStore dataStore)
   at Microsoft.ResourceManagement.Data.TransactionAndConnectionScope..ctor(Boolean createTransaction)
   at Microsoft.ResourceManagement.Data.DataAccess.RegisterService(String hostName)
   at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.RegisterService(String hostName)
   at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.Initialize()
   at Microsoft.ResourceManagement.WebServices.ResourceManagementServiceHostFactory.CreateServiceHost(String constructorString, Uri[] baseAddresses)
   at Microsoft.ResourceManagement.WindowsHostService.OnStart(String[] args)

A few seconds later in the log

Starting up database 'User Profile Service Application_SyncDB_35eb65afa42c4f8e9ece6a33e9348849'.

So, it looks like the Forefront Identity Manager Service is starting up before SQL has started the necessary databases. This appears to cause it to crash. Now that I figured out the problem, I do a search to figure out how to delay the start of the FIM Service and find this article: Troubleshoot User Profile Synchronization Service start issues (SharePoint Server 2010). Scrolling down to the bottom, there is a section titled "User Profile Synchronization service cannot start after a server restart" This suggests using the Services Manager to change the startup of the FIM services to "Delayed Start". (Note that this contradicts the Important note at the top of the article stating "Starting the FIM services manually or changing the FIM service configuration is not supported.").

SharePoint KB983497, KB2281364, FIMSynchronizationService, User Profile Service Application synchronization issues

So, I needed to install hotfix KB983497 on my SharePoint 2010 server today because I needed a particular bug fixed. This was pretty straightforward. However, it breaks various things with user profile. For example, if you go to Manage User Profiles, you get a "File Not Found". You will also see something like this in the ULS log:

Exception: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.ResourceManagement, Version=4.0.2450.9, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.ResourceManagement, Version=4.0.2450.9, Culture=neutral, PublicKeyToken=31bf3856ad364e35' at Microsoft.Office.Server.UserProfiles.UserProfileConfigManager.InitializeIlmClient(String ILMMachineName, Int32 FIMWebClientTimeOut) at Microsoft.Office.Server.UserProfiles.UserProfileConfigManager..ctor(UserProfileApplicationProxy userProfileApplicationProxy, Guid partitionID) at Microsoft.SharePoint.Portal.WebControls.UserProfileServiceStatisticsWebPartBase.Loa...

The good thing is that the KB clearly states that you also need to install hotfix KB2281364 to fix this problem. This was also quite straightforward.

So, I go into the User Profile Service Application to create a synchronization connection, but I get this:

originalMaConfiguration.Create or UpdateResource failed at step Create Connection --- Microsoft.ResourceManagement.WebServices.Faults.ServiceFaultException: Unable to process Create message at Microsoft.ResourceManagement.WebServices.Client.ResourceTemplate.CreateResource() at Microsoft.ResourceManagement.WebServices.ResourceManager.CreateResource() at Microsoft.Office.Server.UserProfiles.DirectoryServiceConnection.UpdateInternal()

and I noticed that the Forefront Identity Management Service no longer starts. There are a bunch events similar to this one:

Microsoft.ResourceManagement.Workflow.Hosting.WorkflowManagerException: Forefront Identity Management Service does not support workflows of type 'Microsoft.ResourceManagement.Workflow.Activities.SequentialWorkflow, Microsoft.ResourceManagement, Version=4.0.2450.9, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.

So, apparently, this one does not affect the profile synchronization feature, so you can ignore it. More about this at KB2432041

But also, it complains about a bunch of things including the following in the Application Log:

The Forefront Identity Manager Service cannot connect to the SQL Database Server.

The SQL Server could not be contacted. The connection failure may be due to a network failure, firewall configuration error, or other connection issue. Additionally, the SQL Server connection information could be configured incorrectly.

Verify that the SQL Server is reachable from the Forefront Identity Manager Service computer. Ensure that SQL Server is running, that the network connection is active, and that the firewall is configured properly. Last, verify the connection information has been configured properly. This configuration is stored in the Windows Registry.

To resolve this, I followed the suggestion at FIMSynchronizationService and Sharepoint 2010 August update which goes like this:

1. Go to Central Administration->System Settings->Manage Services on Server
2. Stop the User Profile Service
3. Stop the User Profile Synchronization Service
4. Start the User Profile Service
5. Start the User Profile Synchronization Service and wait for it to finish starting

This will cause the 2 FIM Services to be started.

However, now I get the following:

UserProfileServiceUserStatisticsWebPart:LoadControl failed, Exception: System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) at Microsoft.Office.Server.UserProfiles.UserProfileConfigManager.InitializeIlmClient(String ILMMachineName, Int32 FIMWebClientTimeOut) at Microsoft.Office.Server.UserProfiles.UserProfileConfigManager..ctor(UserProfileApplicationProxy userProfileApplicationProxy, Guid partitionID) at Microsoft.SharePoint.Portal.WebControls.UserProfileServiceStatisticsWebPartBase.LoadControl(Object sender, EventArgs e)

To fix this, I found a post on UserProfileServiceUserStatisticsWebPart:LoadControl failed. To resolve, just do the following:

1. Verify that the Forefront Identity Management Synchronization Service and the Forefront Identity Manager Service are running. This should have been started by the steps above, but you can verify it anyway by going to Start->Administrative Tools->Services
2. Run iisreset, since Central Administration and User Profile Synchronization Service are provisioned on the same server. (I'm not sure why, though)
3. Now go into User Profile Service Application and continue as normal.

At some point, I should probably read how to Configure profile synchronization (SharePoint Server 2010) and maybe Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization and Configuring User Profiles in SharePoint Server 2010, but no time now - must get some sleep.