Failed to start macro ‘VerifyUsers’ and ‘Unexpected error occurred while running macro’ for SharePoint 2010 “Projects Web Database” site

We have identified an strange error when we create the SharePoint 2010 “Projects Web Database” site and create new projects on existing “Projects Web Database” site. Basically, there are two issues identified.

      1. Failed to start macro ‘VerifyUsers’ when creating new SharePoint 2010 “Projects Web Database” site. Here are the steps to reproduce.

          a.  Click Site Actions and More Options ...
          b.  Select “Projects Web Database”template and give the name and click create
          c.  You may get the error as in below picture

      2. ‘Unexpected error occurred while running macro’ when adding new projects on SharePoint 2010 “Projects Web Database” site. Here are the steps to reproduce.

           a.  Click the Open Projects tab on the site and enter the name for the project
           b.  Click the Save & New button
           c. You may get the following error

 

There are three items that we finally understand we know of that could be causing this behavior.

1. Insufficient Permissions
2. Dependent Services not enabled
3. Macro not correctly converted into workflows when the site was provisioned

So to go into a bit more detail, if the access services app does not have sufficient permissions this can happen. But the more common scenario is that either the Microsoft SharePoint Foundation Web Application service is not started on the App server hosting the Access Services, or that the macros from the access DB did not get converted into workflows when the site was provisioned. In order to best determine which issue may be occurring here, the following should be done:

Set Logging levels for Access Services, Excel Services and SharePoint Foundation > Workflow Infrastructure to verbose and reproduce the error. Once we get the logs from this we will be able to figure out which of the above is the case. If the issue is permissions we should get an error message stating this.
 
If it is the second issue, we should simply be able to enable the Microsoft SharePoint Foundation Web Application Service on the App server. This normally happens if you have three tier architecture with IIS as front end web servers to host web access, application servers to host services, and database servers for data. The applications normally runs Excel Services but do not need the Microsoft SharePoint Foundation Web Application Service. However, Microsoft has a bug that there is a dependence that Microsoft SharePoint Foundation Web Application Service is required for Excel Service. If this service is not running, we will see the errors listed above.

The procedure for the second issue in to start 'Microsoft SharePoint Foundation Web Application' on the application servers running 'Access Database Service'.You could do this from central admin or powershell. The interesting part is even you stop the Microsoft SharePoint Foundation Web Application Service later, you will still be able to use the “Projects Web Database”. The issues explained by Microsoft is the service is reading some configuration from web.config file.

In our case, we have web tier for IIS and app server tier for app server so we are able to follow th rpocedure to fix this. However, we were not able to start the service correctly, we had to use the below steps to stop the service

Get all the service instance id numbers
  Get-SPServiceInstance
Find item in provisioning status
Microsoft SharePoint Foundati... Provi... 5fbbed46-2ae9-4965-b00f-d5d899c574bd
 
Stop the MS SP Foundation item found above
 Stop-SPServiceInstance -identity 5fbbed46-2ae9-4965-b00f-d5d899c574bd



 If it is the third issue, we have a powershell script that we can run that will be published later.