Export / Import Excel Service Trusted File Locations
After installing and configuring office web apps in my customer’s environment we faced an interesting issue related to Excel Service Application. Issue was the application pool used for the excel service application was crashing in every few seconds.
We have created single “application pool” for Word Viewing, PowerPoint Viewing & Excel Service applications. After that we saw the below error in one of the application servers. (we have two application servers which are running all three services – Word Viewing, PowerPoint Viewing & Excel Calculation Service). What we found that the issue was happening only once we start the “Excel Calculation Service”, more details of the issue given below.
"Unable to process request"
You review the SharePoint server diagnostic (ULS) logs, and find performance counter call failure and the following error:
The Performance Counter OS (pdh) PdhAddEnglishCounter method call failed with error code PDH_CSTATUS_NO_MACHINE.
and
Unexpected exception occured while trying to access the performance counters registry key. Exception: System.InvalidOperationException: The Performance Counter OS (pdh) PdhAddEnglishCounter method call failed with error code PDH_CSTATUS_NO_MACHINE. at Microsoft.Office.Excel.Server.PdhPerformanceCounter.Pdh.CheckReturnValue(PDH_STATUS status, Boolean throwOnError, String methodNameForTracing) at Microsoft.Office.Excel.Server.PdhPerformanceCounter.Initialize() at Microsoft.Office.Excel.Server.PdhPerformanceCounter..ctor(String categoryName, String counterName) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp.Initialize()
We thought that we are lucky as there was a KB recently published related to this issue.
https://support.microsoft.com/kb/2711527
Again the issue was happening in only one application server. We double checked that the application pool identity has necessary permission on the windows groups “Performance Log Users” & “Performance Monitor” as per the above KB. Also, there was no issue related to the performance log generation in the problematic application server. So, the KB was not helping us in our scenario.
We have created a new excel service application with it’s own application pool and we didn’t see any error reported in that server about newly created application pool. Finally, we thought that to delete the problematic excel service application and recreate it. We have many “Trusted File Locations” created in the problematic excel service application and re-adding those all required a repetitive manual work. That ends up in the creation of the attached export / import PowerShell Scripts.
For both export and import you have to pass two parameters, first parameter has to be the name of the excel service application and the second one CSV file path as given below.
Scripts are using Get-SPExcelFileLocation & New-SPExcelFileLocation out-of-the-box PowerShell commandlets.
Usage – Export
.\Export-SPExcelFileLocation.ps1 "Excel Service Application - source" "c:\trustedlocations.csv"
Usage – Import
.\Import-SPExcelFileLocation.ps1 "Excel Service Application - target" "c:\trustedlocations.csv"
You can download the scripts from the below links.