FIM Bug for multi-valued strings that need approval

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).

 

 

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:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

<System>

<Provider Name="Microsoft.ResourceManagement" />

<EventID Qualifiers="0">3</EventID>

<Level>2</Level>

<Task>0</Task>

<Keywords>0x80000000000000</Keywords>

<TimeCreated SystemTime="2011-06-28T01:33:52.000000000Z" />

<EventRecordID>9448</EventRecordID>

<Channel>Forefront Identity Manager</Channel>

<Computer>fimserver</Computer>

<Security />

</System>

<EventData>

<Data>Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other ---&gt; 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 ---</Data>

</EventData>

</Event>

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

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

*/

SQL Extensible Management Agents That Scale (Rebecca Croft)

Rebecca, a fellow Ensynchian, presented at TEC 2011 on the limitations of the standard out of the box SQL Management and how she overcame them by writing a very fast eXtensible Management Agent (XMA).

First attempt use ado.net sql reader to read data (really fast) and write one row at a time to the AVP file (but that gets slow when dealing with large data sets).

Second attempt use the T-SQL “FOR XML” clause to transform the data to XML and then use an XSLT to transform to LDIF.

So the XMA executes a T-SQL statement to export the data to XML and then XSLT to transform to LDIF and then returns the LDIF file to the FIM Synchronization Service.

She even showed off a wizard to create the XMA for us. When it completed successfully she received a spontaneous round of applause.

RCDC Editor

As previously discussed the RCDC is a very powerful tool for customizing FIM without writing your own front-end and web client. There are several drawbacks to the RCDC. The worst is that you have to export the RCDC to an xml file, open it up in your favorite XML editor, modify it by hand, load it back into the FIM Portal and then run iisreset. All of which means that mistakes are quite painful, as it can take you several minutes to discover your mistake. Worse if you made more than one change. Ugh!
So thanks to my friends over at OCG there is an RCDC editor. While not perfect it can shave hours off your time to edit RCDC’s.
You get an almost WYSIWYG editor that saves you from making many easy simple mistakes. If I need to tweak something simple I might for go it, but then again I have lots of experience tweaking the RCDC by hand (painful experience). For $775 for a project I can get an editor that makes life much simpler. No brainer!
The UI is good but not perfectly intuitive. I found several “bugs” only to discover that I needed to learn just a bit more about the tool.
You will need to run a PowerShell command to export the FIM Configuration, install the software before you can use it at all. After activating the license you can save the RCDC’s as XML. Then yes you still have to load the RCDC manually and run iisreset. Nonetheless, this is still much easier.
While you are still learning more about what the RCDC can do, this is still an iterative process. Creating an RCDC for a new FIM resource type is now a 2-8 hour job instead of 8-32 hour job.
The Resultant Rights Editor is a nice bonus that allows you to setup scenarios (who is accessing what resource and which attributes to include) so that you can see what control will be visible, and enabled for the different users.

Three complaints (with paraphrased responses from Tools4FIM):
1) When I purchased the tool the purchase was for a 1 year license – I wasn’t warned anywhere that this was only for a 1 year license until I was completing the purchase. I didn’t spot it in the EULA when I installed the demo version (yes I did read it, if I missed it please let me know). In my opinion, limit the license by time or project, not both. (You get licensed for 1 project – one FIM install, for one year).
– Apparently, the one year bit is that you only have 1 year to activate the license. You get the license without a time limit. I love it when my complaints are resolved before I make them.
2) Sometimes I may want to add a control that doesn’t really have anything to do with an attribute, yet the tool forces me to name the control after the attribute.
-- Next version (due out Q3) and they intend to allow those that purchase now access to that new version without penalty.
3) When adding/editing UocDropDownList controls it doesn’t let me set the Caption (what the users sees) for the Option differently than the Value (what the computer sees). It lets me set a hint but not the Caption.

Yes I did read the help file which tells me that I can do it but not how. So I do think that is a bug.
-- Aha! They told me that there is a way to do this. When you modify the Value:

<edited 6/23/2011>

The Option Value is the value (what the computer will see – SA) and the Constant is the caption that the user will see (Admin Account).
So it looks like this:

</edited>
Wish list:
1) Copy all of the XML data sources (like regions, or other custom data sources) so that I can manage them centrally and copy them into other RCDC’s. That way if a new country is born tomorrow, or next year, I only have one place to go update the list of countries and their codes instead of in several different RCDC’s for several different resource types.
2) Let me copy all of the settings from one control to another. Sometimes I want 4 text boxes right after another with all of the same settings, just different binding.
-- Next version and they promised to name the feature after me
3) XML editor (or link to) so that I can open it up for the manual tweaks that RCDC Editor doesn’t do yet.
-- They are thinking about it for the next version.
4) Add the ability to run the export PowerShell script from the tool (probably also need to let me configure the exact command line).
-- Already planning on it in the next version
5) Add the ability to upload the RCDC right from the tool, optionally running iisreset.
-- Already planning on it in the next version
6) Home Page and Nav Bar editing, especially with the Resultant Rights Evaluator.
7) FIM Portal style sheet editing
8) Ability to diffs with previous versions (whether stored by the Editor or by querying the FIM Service for recent requests to modify the RCDC
Bottom line: This is a tool that I as a FIM implementer can’t live without, especially at the $775 price.

RCDC Requiring another field

Ok I just had to blog this.

I created a custom resource type in FIM for resource mailboxes (Room and Equipment) with accompanying RCDC’s. Based on a Boolean attribute I hide or make visible a tab of info about Room resources on the edit and view RCDC’s.  (You can’t do that to the create RCDC because the object doesn’t yet exist)

But, I would like to make room number on the Hidden tab to be required when the tab is visible, and not when the tab isn’t. Obviously I can’t do that on the create because the object doesn’t yet exist and so I can’t reference the Boolean attribute. So I just set the required property to true and figured it would work or not. – It does not work. The tab is still hidden until I click finish and then the tab is revealed and it insists on input to the field “The required field cannot be empty”.

Isn’t that just weird?

However, by binding the required property of the control to the same Boolean attribute as the visible property of the tab uses we don’t get the same issue. It keeps the tab hidden when it is not a room, but shows the tab when it is a room and requires those fields.