none
Project Server 2010 & AD synchronization error RRS feed

  • Question

  • Hi,

    I configured the "Active Directory Resource Pool Synchronization" in my Project Server 2010, and it failed giving some (5) errors to the event viewer.

    Error 1: A general exception occurred during communication with Active Directory. Context: UpdateResourcesToDatabaseWork. Additional Information: An exception occurred in UpdateResourcesToDatabaseWork.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    Error 3: A general exception occurred during communication with Active Directory. Context: ExecuteERPSync. Additional Information: An exception occurred when reading resource data and synchronizing the enterprise resource pool with active directory.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    Error 4: A general exception occurred during communication with Active Directory. Context: UpdateResourcesToDatabaseWork. Additional Information: An exception occurred in UpdateResourcesToDatabaseWork.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    Error 5: A general exception occurred during communication with Active Directory. Context: SyncErp. Additional Information: An exception occurred while synchronizing the enterprise resource pool.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

     

    I migrated from Project Server 2007 without any error in the ULS and everything is working fine except this!

    What could be the origin of this issue?

    Thanks

    Error 2: A general exception occurred during communication with Active Directory. Context: SyncErp. Additional Information: An exception occurred while synchronizing the enterprise resource pool.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    Monday, March 21, 2011 5:36 PM

Answers

  •  

    Finally I've been able to solve the issue:

     

    Problem:

     

    When synchronizing the process were detecting duplicated names but I didn't have any user duplicated

     

    Reason:

     

    Due to the new Active Directory policy, lots of user accounts are being changed. Display names and also Login names (not to all the users). This caused the process detect new users in the AD but with the same old display name (or the opposite) and this made the process crash because the user (with the same display name but different login or the opposite) was on the project database.

     

    Solution:

     

    1: Execute the command: UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL and start the sync process.

    2: After the sync process crashed, I detected 200 users from 1000 not being synchronized

    3: Compare all those 200 users (because my idea was that at least one of them was creating the problem) with the users in the AD group and update all the possible changes to the Project Users

    4: Sync again and Smile!!!

    • Marked as answer by JosepMola Thursday, March 31, 2011 2:35 PM
    Thursday, March 31, 2011 2:31 PM
  • You have Duplicate display name in the Enterprise resource pool or in the active directory.

    Check the users in the AD group and identify the duplication. At the same time, check the event log to get the users who got issue.

    Clear the GUID after taking the Project databases backup.

    UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL - Try it on test PWA

     


    Cheers. Happy troubleshooting !!! Sriram E - MSFT Enterprise Project Management
    Monday, March 21, 2011 7:52 PM
    Moderator

All replies

  • You have Duplicate display name in the Enterprise resource pool or in the active directory.

    Check the users in the AD group and identify the duplication. At the same time, check the event log to get the users who got issue.

    Clear the GUID after taking the Project databases backup.

    UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL - Try it on test PWA

     


    Cheers. Happy troubleshooting !!! Sriram E - MSFT Enterprise Project Management
    Monday, March 21, 2011 7:52 PM
    Moderator
  • Hi,

    I've checked the Enterprise resource pool and the AD and we don't have any display name duplicated and the event viewer and sharepoint logs don't say anything about a problem with a specific user.

    These are the sharepoint logs:

    PSI: SqlException occurred in DAL:  <Error><Class>1</Class><LineNumber>1</LineNumber><Number>2601</Number><Procedure></Procedure>  <Message>  System.Data.SqlClient.SqlError: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.  </Message>  <CallStack>     at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.HandleUpdateDataTable(SqlDataAdapter sqlDa, DataTable updates, DataRowState operations)  </CallStack>  </Error>


    PSI: SqlException occurred in DAL:  <Error><Class>0</Class><LineNumber>1</LineNumber><Number>3621</Number><Procedure></Procedure>  <Message>  System.Data.SqlClient.SqlError: The statement has been terminated.  </Message>  <CallStack>     at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.HandleUpdateDataTable(SqlDataAdapter sqlDa, DataTable updates, DataRowState operations)  </CallStack>  </Error>


    PSError: NoError (0) A general exception occurred during communication with Active Directory. Context: UpdateResourcesToDatabaseWork. Additional Information: An exception occurred in UpdateResourcesToDatabaseWork.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.  The statement has been terminated.

    PSI: ULS Event: AdSyncExceptionOccurred, tag 2020632186 was associated with exception: System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.  The statement has been terminated.     at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.HandleUpdateDataTable(SqlDataAdapter sqlDa, DataTable updates, DataRowState operations)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.UpdateDataTable(TableInfo tableInfo, DataTable updates, DataRowState operations, Int32 revisionCounter)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.UpdateDataTables(TableInfo[] tableInfos, DataSet updates, Int32 revisionCounter)     at Microsoft.Office.Project.Server.DataAccessLayer.ResourceDal.WriteResourcesViaADONET(ResourceDataSet rds, Boolean isFromWinproj)     at Microsoft.Office.Project.Server.BusinessLayer.ModifyResources.WriteResourcesToDB(ResourceDataSet rds, Guid[] resGuidsForCFValuesToDelete)     at Microsoft.Office.Project.Server.BusinessLayer.ModifyResources.WriteUpdatesToDB()     at Microsoft.Office.Project.Server.BusinessLayer.Resource.UpdateResourceAndAuthorizationInternal(ResourceDataSet resources, ResourceAuthorizationDataSet users, Boolean validateOnly, Boolean skipWssSync, Boolean isAdSync)     at Microsoft.Office.Project.Server.BusinessLayer.AdSync.UpdateResourcesToDatabaseWork(SyncTypeEnum syncType, ActiveDirectoryUserCollection members, List`1 resourcesToCheckoutForUpdate, List`1 ResUids, DataRowState operation, ADSyncStatus& status)

    ----------- End logs ------------

    At this moment I don't have access to the Project Databases but before executing the command (UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL ) I would like to know why it is necessary.

    And in which databases of the four project databases I would have to execute that command?

    Thanks

    Tuesday, March 22, 2011 9:14 AM
  • Hi,

    after executing the command (UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL) I configured again the AD synchronization but with the enterprise resource pool, and in the queue management, it seemed it was working, cause lots of new jobs where appearing when before it didn't happen, but finally the process failed again, and the error I got in the ULS is the following:

    Error is: GeneralQueueJobFailed. Details: Attributes:  eadcd3de-0b7e-4c21-975d-2819fb7c88aa  DSGLESPBR-S4020  AdSyncERP  AdSyncERPMessage  1    . Standard Information: PSI Entry Point:   Project User: DOMAIN\projectuser  Correlation Id: 046bb28f-82cf-4a67-88be-5e6cb7046d14  PWA Site URL: http://s4019/PWA  SSP Name: Project Web App Service Application  PSError: GeneralQueueJobFailed (26000) fdd42f11-8284-4138-98e9-9162ae3201f0

    And the errors in the event viewer:

    A general exception occurred during communication with Active Directory. Context: UpdateResourcesToDatabaseWork. Additional Information: An exception occurred in UpdateResourcesToDatabaseWork.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    A general exception occurred during communication with Active Directory. Context: SyncErp. Additional Information: An exception occurred while synchronizing the enterprise resource pool.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    A general exception occurred during communication with Active Directory. Context: ExecuteERPSync. Additional Information: An exception occurred when reading resource data and synchronizing the enterprise resource pool with active directory.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    A general exception occurred during communication with Active Directory. Context: UpdateResourcesToDatabaseWork. Additional Information: An exception occurred in UpdateResourcesToDatabaseWork.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

    A general exception occurred during communication with Active Directory. Context: SyncErp. Additional Information: An exception occurred while synchronizing the enterprise resource pool.. Exception Info: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.

     

    What else can I do?

    Friday, March 25, 2011 2:10 PM
  • I tried to create a new PWA test (the one that is generating the error comes from a migration from Project Server 2007) and synchronize the AD users and the synchronization has been successful. I have now the users in te enterprise resource pool in the new PWA test I've created.

    Is there a way to detect the specific user or whatever thing that is generating the error in the migration PWA?

     

    thanks,

    Monday, March 28, 2011 9:30 AM
  • Did you review the Event log at the time of failure?

    Please analyze the Event log to identify the resource who is creating the conflict. I am suspecting that there is a duplication in Resource name and NT account. Ex: same Display name/ NT account is trying to configure for two different accounts.

    Since the second PWA works well, assuming that there is no problem in AD group. So check the Display name and NT accounts in the MSP_Resources table.


    Cheers. Happy troubleshooting !!! Sriram E - MSFT Enterprise Project Management
    Monday, March 28, 2011 3:33 PM
    Moderator
  • The problem is that the event log doesn't say anything about the user is creating the conflict. It gives a generic message.

    These are different Error lines during the sync process: (plus the other errors I posted before!!!)

    "The user does not exist or is not unique.<nativehr>0x81020054</nativehr><nativestack></nativestack>"

    PWA:http://project/PWA, ServiceApp:Project Web App Service Application, User:DOMAIN\AppDsglSHPFarm, PSI: SqlException occurred in DAL:  <Error><Class>0</Class><LineNumber>1</LineNumber><Number>3621</Number><Procedure></Procedure>  <Message>  System.Data.SqlClient.SqlError: The statement has been terminated.  </Message>  <CallStack>     at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.HandleUpdateDataTable(SqlDataAdapter sqlDa, DataTable updates, DataRowState operations)  </CallStack>  </Error>

    PWA:http://project/PWA, ServiceApp:Project Web App Service Application, User:DOMAIN\AppDsglSHPFarm, PSI: SqlException occurred in DAL:  <Error><Class>1</Class><LineNumber>1</LineNumber><Number>2601</Number><Procedure></Procedure>  <Message>  System.Data.SqlClient.SqlError: Cannot insert duplicate key row in object 'dbo.MSP_RESOURCES' with unique index 'IX_MSP_RESOURCES_RES_NAME'.  </Message>  <CallStack>     at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)     at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)     at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.HandleUpdateDataTable(SqlDataAdapter sqlDa, DataTable updates, DataRowState operations)  </CallStack>  </Error>

     

     

     

     

     

    We have almost 1000 users in the msp_resources table and after clearing the WRES_AD_GUID and trying to sync again, almost 200 of all still had the value to NULL.

    I guess one of those 200 users is the one which is generating the conflict, but the event viewer or the sharepoint logs don't say which one it is.

    I checked the display names and the userAccounts and I didn't see any duplicate entry

    How can i detect the user?

    Monday, March 28, 2011 4:32 PM
  •  

    Finally I've been able to solve the issue:

     

    Problem:

     

    When synchronizing the process were detecting duplicated names but I didn't have any user duplicated

     

    Reason:

     

    Due to the new Active Directory policy, lots of user accounts are being changed. Display names and also Login names (not to all the users). This caused the process detect new users in the AD but with the same old display name (or the opposite) and this made the process crash because the user (with the same display name but different login or the opposite) was on the project database.

     

    Solution:

     

    1: Execute the command: UPDATE MSP_RESOURCES SET WRES_AD_GUID = NULL and start the sync process.

    2: After the sync process crashed, I detected 200 users from 1000 not being synchronized

    3: Compare all those 200 users (because my idea was that at least one of them was creating the problem) with the users in the AD group and update all the possible changes to the Project Users

    4: Sync again and Smile!!!

    • Marked as answer by JosepMola Thursday, March 31, 2011 2:35 PM
    Thursday, March 31, 2011 2:31 PM