FIM 2010 Troubleshooting: Set filter definitions failing with negative condition
Credits
Thanks to Laurent Benmeziani and his team to deep dive on this error and providing the technical content.
Short URL
Bookmark this article as : http://aka.ms/fim_negativefilterissue
Applies to
FIM Build 4.1.3634.0 and 4.1.3646
Symptoms
The first symptoms noticed are errors when you try to create a set with negative conditions
FIM Portal
Screenshots
Error message
Error processing your request: The server was unwilling to perform the requested operation. Reason: Unspecified. Attributes: Correlation Id: facc9263-960f-499e-bf7d-0241226b770a Request Id: Details: Request could not be dispatched. |
Event viewer
The event viewer might display 3 errors that are related.
Error 1: Procedure ReRaiseException
Log Name: Forefront Identity Manager Source: Microsoft.ResourceManagement Date: 30/06/2015 22:57:20 Event ID: 3 Task Category: None Level: Error Keywords: Classic User: N/A Computer: FIMContoso.Contoso.local Description: /../ The following information was included with the event: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 156, Level 15, State 1, Procedure ?, Line 61, Message: Incorrect syntax near the keyword 'AND'. 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="2015-06-30T20:57:20.000000000Z" /> <EventRecordID>10615</EventRecordID> <Channel>Forefront Identity Manager</Channel> <Computer>FIMContoso.Contoso.local</Computer> <Security /> </System> <EventData> <Data>Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 156, Level 15, State 1, Procedure ?, Line 61, Message: Incorrect syntax near the keyword 'AND'.</Data> </EventData> </Event> |
Error 2: Inner Exception Error
Log Name: Forefront Identity Manager Source: Microsoft.ResourceManagement Date: 30/06/2015 22:57:20 Event ID: 3 Task Category: None Level: Error Keywords: Classic User: N/A Computer: FIMContoso.Contoso.local Description: /../ The following information was included with the event:
Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other ---> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 156, Level 15, State 1, Procedure ?, Line 61, Message: Incorrect syntax near the keyword 'AND'. 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.DoRequestCreation(RequestType request, Guid cause, Guid requestMarker, Boolean doEvaluation, Int16 serviceId, Int16 servicePartitionId) --- 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="2015-06-30T20:57:20.000000000Z" /> <EventRecordID>10616</EventRecordID> <Channel>Forefront Identity Manager</Channel> <Computer>FIMContoso.Contoso.local</Computer> <Security /> </System> <EventData> <Data>Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other ---> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 156, Level 15, State 1, Procedure ?, Line 61, Message: Incorrect syntax near the keyword 'AND'. 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.DoRequestCreation(RequestType request, Guid cause, Guid requestMarker, Boolean doEvaluation, Int16 serviceId, Int16 servicePartitionId) --- End of inner exception stack trace ---</Data> </EventData> </Event>
|
Error 3: FIM Service Error
Screenshot
Error message
Log Name: Forefront Identity Manager Source: Microsoft.ResourceManagement Date: 30/06/2015 22:57:20 Event ID: 3 Task Category: None Level: Error Keywords: Classic User: N/A Computer: FIMContoso.Contoso.local Description: /../ The following information was included with the event:
Requestor: urn:uuid:7fb2b853-24f0-4498-9534-4e10589723c4 Correlation Identifier: 0e25d080-0a1d-4876-b41c-aa8df1d74c56 Microsoft.ResourceManagement.Service: Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other ---> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 156, Level 15, State 1, Procedure ?, Line 61, Message: Incorrect syntax near the keyword 'AND'. 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.DoRequestCreation(RequestType request, Guid cause, Guid requestMarker, Boolean doEvaluation, Int16 serviceId, Int16 servicePartitionId) --- End of inner exception stack trace --- at Microsoft.ResourceManagement.WebServices.RequestDispatcher.CreateRequest(UniqueIdentifier requestor, UniqueIdentifier targetIdentifier, OperationType operation, String businessJustification, List`1 requestParameters, CultureInfo locale, Boolean isChildRequest, Guid cause, Boolean doEvaluation, Nullable`1 serviceId, Nullable`1 servicePartitionId, UniqueId messageIdentifier, UniqueIdentifier requestContextIdentifier, Boolean maintenanceMode) at Microsoft.ResourceManagement.WebServices.RequestDispatcher.CreateRequest(UniqueIdentifier requestor, UniqueIdentifier targetIdentifier, OperationType operation, String businessJustification, List`1 requestParameters, CultureInfo locale, Boolean isChildRequest, Guid cause, Boolean doEvaluation, Nullable`1 serviceId, Nullable`1 servicePartitionId, UniqueId messageIdentifier) at Microsoft.ResourceManagement.WebServices.ResourceManagementService.Create(Message request)
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="2015-06-30T20:57:20.000000000Z" /> <EventRecordID>10617</EventRecordID> <Channel>Forefront Identity Manager</Channel> <Computer>FIMContoso.Contoso.local</Computer> <Security /> </System> <EventData> <Data>Requestor: urn:uuid:7fb2b853-24f0-4498-9534-4e10589723c4 Correlation Identifier: 0e25d080-0a1d-4876-b41c-aa8df1d74c56 Microsoft.ResourceManagement.Service: Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other ---> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 50000, Level 15, State 1, Procedure ReRaiseException, Line 37, Message: Reraised Error 156, Level 15, State 1, Procedure ?, Line 61, Message: Incorrect syntax near the keyword 'AND'. 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.DoRequestCreation(RequestType request, Guid cause, Guid requestMarker, Boolean doEvaluation, Int16 serviceId, Int16 servicePartitionId) --- End of inner exception stack trace --- at Microsoft.ResourceManagement.WebServices.RequestDispatcher.CreateRequest(UniqueIdentifier requestor, UniqueIdentifier targetIdentifier, OperationType operation, String businessJustification, List`1 requestParameters, CultureInfo locale, Boolean isChildRequest, Guid cause, Boolean doEvaluation, Nullable`1 serviceId, Nullable`1 servicePartitionId, UniqueId messageIdentifier, UniqueIdentifier requestContextIdentifier, Boolean maintenanceMode) at Microsoft.ResourceManagement.WebServices.RequestDispatcher.CreateRequest(UniqueIdentifier requestor, UniqueIdentifier targetIdentifier, OperationType operation, String businessJustification, List`1 requestParameters, CultureInfo locale, Boolean isChildRequest, Guid cause, Boolean doEvaluation, Nullable`1 serviceId, Nullable`1 servicePartitionId, UniqueId messageIdentifier) at Microsoft.ResourceManagement.WebServices.ResourceManagementService.Create(Message request)</Data> </EventData> </Event>
|
Solution
Note
To solve the error described in this article, a stored procedure in the FIMService database must be changed.
But it's not supported to change the FIMService on your own initiative.
You must request a MS Support fix for an SQL stored procedure: fim.GenerateMembershipConditionStatementNegativeLiteralValueSQL