We’re really excited about SharePoint 2013 but recently ran into a problem during an upgrade from SharePoint 2010. More specifically while attempting to import the MIIS encryption key for the User Profile Synchronisation Service, we received the following error:
Now the “Login failed for user” portion would indicate we forgot to grant our SP Admin account db_owner rights. However, the permissions have been double checked and are correct, so why can’t it open the database? Error code 80131904 from the SQL .NET assembly loosely means “SQL server does not exist or access is denied” so we need to ensure the connection elements are correct.
Open the local machine Registry hive and navigate to SystemCurrentControlSetServicesFIMSynchronizationServiceParameters and check the value of the DBName key. In this example, we noticed the database name was incorrect so updated it (after noting down the original value of course!) and confirmed the Server and SQLInstance keys are also correct:
When attempting the import again, we received a new error:
Notice the same error code? We’ve seen this before when attempting to restore a UPSA database from a backup that has become corrupt. So let’s apply some SharePoint 2010 logic and experience and examine this mms_server_configuration table in the database:
Oops! It looks like the database wasn’t provisioned correctly after all. After restoring the database from a known good backup, the MIIS key import was successful:
