FIM Bug for multi-valued strings that need approval

June 28, 2011

I think I found a bug in FIM Version 4.0.3576.2 take a look:

It appears that when you have a multi-valued string attribute when you add more than 1 value at a time and you need approval to create the object or to update the attribute, the request will fail. In the event log you will see an error (UnwillingToPerformException … CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object).

clip_image001

clip_image002

clip_image003

Log Name: Forefront Identity Manager

Source: Microsoft.ResourceManagement

Date: 6/27/2011 6:33:52 PM

Event ID: 3

Task Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: fimserver

Description:

Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other —> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 16, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 16, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 1505, Level 16, State 1, Procedure ReEvaluateRequestOutputString, Line 53, Message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#reevaluateRequestOutputStringRemovalCandidate______________________________________________________________________0000000175F1’ and the index name ‘IX_ReEvaluateRequestRequestOutputStringRemovalCandidate_ObjectKey_ObjectTypeKey_AttributeKey_ValueString’. The duplicate key value is (23564, 32698, 32655, 0).

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

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

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

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

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

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

at Microsoft.ResourceManagement.Data.DataAccess.ProcessRequest(RequestType request)

-– End of inner exception stack trace —

Event Xml:

3

2

0

0x80000000000000

9448

Forefront Identity Manager

fimserver

Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other —> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 16, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 16, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 1505, Level 16, State 1, Procedure ReEvaluateRequestOutputString, Line 53, Message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#reevaluateRequestOutputStringRemovalCandidate______________________________________________________________________0000000175F1’ and the index name ‘IX_ReEvaluateRequestRequestOutputStringRemovalCandidate_ObjectKey_ObjectTypeKey_AttributeKey_ValueString’. The duplicate key value is (23564, 32698, 32655, 0).

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

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

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

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

at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

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

at Microsoft.ResourceManagement.Data.DataAccess.ProcessRequest(RequestType request)

-– End of inner exception stack trace —

clip_image004

So I looked up the stored procedure mentioned in the Error Message.

We can see that this stored procedure can get called from a lot of places and so can be an issue in many spots.

The problem is found in the text of the ReEvaluateRequestOutputString stored procedure excerpted here below with my comments added inside /* */:

SELECT DISTINCT

[requestOriginal].[ObjectKey] AS N’ObjectKey',

[requestOriginal].[ObjectTypeKey] AS N’ObjectTypeKey',

[requestOriginal].[AttributeKey] AS N’AttributeKey',

[requestOriginal].[ValueString] AS N’ValueString',

[requestOriginal].[Deleted] AS N’Deleted'

INTO #reevaluateRequestOutputStringRemovalCandidate

FROM [fim].[RequestOutputString] AS [requestOriginal]

WHERE

[requestOriginal].[RequestKey] = @originalRequestKey

ORDER BY

[ObjectKey],

[ObjectTypeKey],

[AttributeKey],

[ValueString];

/* Which results in

clip_image005

Note that the last two rows will cause a problem with the next command because they have the same values in the objectkey, objecttypekey, attribute key and deleted columns.

Yet the adding of two values to a multi-valued string is a legal operation.

*/

CREATE UNIQUE CLUSTERED INDEX [IX_ReEvaluateRequestRequestOutputStringRemovalCandidate_ObjectKey_ObjectTypeKey_AttributeKey_ValueString]

ON #reevaluateRequestOutputStringRemovalCandidate

(

[ObjectKey],

[ObjectTypeKey],

[AttributeKey],

[Deleted]

);

/* Resulting error:

Msg 1505, Level 16, State 1, Line 26

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#reevaluateRequestOutputStringRemovalCandidate______________________________________________________________________0000000178D0’ and the index name ‘IX_ReEvaluateRequestRequestOutputStringRemovalCandidate_ObjectKey_ObjectTypeKey_AttributeKey_ValueString’. The duplicate key value is (23623, 32698, 32655, 0).

The statement has been terminated.

*/

/*

Then the whole transaction rolls back and the request fails

*/

http://feeds.feedburner.com/IdentityLifecycleManagerilmBestPractices