FIMMA no-start-bad-ma-configuration

I've been dealing with a new issue the past few days that I thought would be good to share with the community.

Over the past few years I have seen a variety of different issues related to FIM, however, this was the first time I have run into this specific error. When running a full import on the FIMService MA, the import would get to "completing obsoletion" then fail. Certainly frustrating to say the least since it would always fail at the end of a full import, a delta import would always run successfully though. After trying some of the standard things (deleting the connector space, deleting the MA and recreating) I found that the issue persisted and I couldn't seem to resolve it.

A very telling item in this case, was that the DB's (FIMService and FIMSync) had been moved from a virtual server to a physical host. Additionally, they had also been patched to the latest hotfix and I'm not sure what order that happened in since it was like that when I got here.  Anyone who has spent quality time with FIM knows that randomly upgrading and moving things around can be a recipe for disaster! As I dug in a little deeper, I found the stack trace below on the sync server which lead me to look into the DB role permissions.

 

As it turns out, during the DB moves execute and alter permissions had been removed from the roles which was the root cause of my errors.  After mirroring the permissions from a known good copy of the FIMService DB I was finally able to complete full imports.

 Planning is a step that cannot be overlooked or hurried through when dealing with FIM databases.

 __________________________________________________________________________________________________________________________________

Stack Trace:

___________________________________________________________________________________________________________________________________

 

Net SqlClient Data Provider: System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'AssertTransactionalContext',
database 'FIMService', schema 'fim'.

The SELECT permission was denied on the object 'RequestLog', database 'FIMService', schema 'fim'.

The SELECT permission was denied on the object 'RequestLog', database 'FIMService', schema 'fim'.

The SELECT permission was denied on the object 'RequestLog', database 'FIMService', schema 'fim'.

Cannot find the object "#truncateRequestLogBuffer" because it does not exist or you do not have permissions.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,
TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at Microsoft.ResourceManagement.Data.Sync.TruncateRequestLog(Int64 requestLogTruncationKey