Project Server 2007: WSS Sync issues – Error: Access Denied

After the notorious check-in pending, and the slow cube building issues, the next hot topic has to be problems around WSS Synchronization.  This was noted in one of the recent TechNet updates (I’ve also just corrected the link in my previous posting – sorry) for Active Directory synchronization, but AD Sync is just one of the scenarios leading to this issue.  In case you are not familiar with this one, basically there are two parts to the story:

  • Sync of permissions for sites (including /PWA) can be really slow if you have lots of users
  • If more than one sync is in progress then SQL deadlocks are likely to occur

For our larger customers the first item is bad enough, but generally if the sync is slow there is a greater risk of another sync clashing.  These synchronizations can be a result of changes in group security settings, syncs to AD or just re-sync’ing the site permissions for the workspaces.  They are also more likely if your category settings mean that virtually all users can access all projects – more permissions to sync!

The typical symptoms for users is that they can be left out in the cold with an Error: Access Denied – with the option to sign in as a different user.  Behind the scenes, in the queue and the ULS logs you will see such things as:

Manage Queue page

“User Synchronization for Project Web Access App Root Site and Project WSS Workspace” fail with the Job State error of “Failed But Not Blocking Correlation”

ULS Logs

10/06/2008 17:11:37.16  Microsoft.Office.Project.Server (0x07D8) 0x0CF0 Windows SharePoint Services    Database                       6f8g Unexpected Unexpected query execution failure, error code 1205. Additional error information from SQL Server is included below. “Transaction (Process ID 98) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” Query text (if available): “{?=call proc_SecAddPrincipalToRole(?,?,?,?,?,?)}”

The updated part of the TechNet article is worth repeating here, as it gives some suggestions for reducing the occurrence of the problem, and links to some tools which you may find useful, so here it is:

Caution Caution:

Under certain circumstances, synchronizing Project Server users and workspaces with Active Directory can cause a “deadlock” situation in which all users are locked out of a PWA site or the respective workspaces. This causes user synchronization jobs to fail and site permissions to synchronize partially or not at all. Users may not be able to log on to PWA or their workspaces.

A deadlock can occur if the user synchronization process is taking too long to complete. This is due to the synchronization job iterating through many users and workspaces, for example, when large membership changes are being made. A synchronization job remaining in the queue a long time increases the possibility of other jobs starting inadvertently, which can also cause a deadlock.

To reduce the chance of a deadlock, you can do the following:

  • Before making large group membership changes, verify that there are no jobs named “User Synchronization for Project Web Access App Root Site and Project WSS Workspaces” currently processing or waiting to be processed in the queue.

  • Run the Project Server Workspace Sync tool on the CodePlex site (http://go.microsoft.com/fwlink/?LinkId=147394). The tool controls what is to be synchronized when the job starts — PWA and workspaces, workspaces only, PWA only, or no synchronization for either PWA or workspaces — and allows the administrator to perform the user synchronization during non-working or off-peak hours when server overhead is lower.

    Note that the Project Server Workspace Sync tool does not speed up the synchronization process beyond normal. However, being able to synchronize users when server overhead is lower reduces the possibility of synchronization failures.

Unfortunately there isn’t a fix coming for this one any time soon – it is harder to do than you might think, with so many moving parts.  I hope this at least helps you to work around the issues and sorry for the undoubted inconvenience this one has been causing.

 

Technorati Tags: ,