When you configure the Excel calculation services (ECS) on your SharePoint2010 farm it might happen that you (or latest your endusers) face some error when trying to open an excel sheet stored in a document library.
When you check your ULS logs, you will find something like:
ServerSession.ProcessServerSessionException: An exception during ExecuteWebMethod has occurred for server: http://yourserver andsomeguid/ExcelService*.asmx, method: OpenWorkbook, ex: Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: The workbook cannot be opened..
Result=Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession Exception: The workbook cannot be opened.
at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.Execute WebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)
at Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSession.Execute WebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)
After a little bit of research I came across this blogpost http://blogs.msdn.com/b/jjameson/archive/2010/05/04/the-workbook-cannot-be-opened-error-with-sharepoint-server-2010-and-tfs-2010.aspx written by Jermey Jameson (a guy obviously way smarter than me) recommending running some PowerShell to fix this issue.
So I looked at the PowerShell and saw its granting the ECS service account some permission on the webapplication level.
So I decided before running his script that I should check the database permissions this account has and found this:
After I ran the script from Jermey’s blog (copied it from there):
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0
$webApp = Get-SPWebApplication “http://cyclops”
I checked the database permissions again and found:
So the script grants the ECS service account db_owner permission to you content database.
After that you find ECS working just as expected:
Thanks again Jermey you made my day.
But as nice as the resolution might look at the first glance I can not be satisfied with it, let me explain why.
In the most environments I handle you have multiple content databases and on some sitecollections living in this contentdatabases you want to have the ECS functionality enabled. This means whenever you are adding a new content database the permissions will be missing and your users will face this issue again.
I see two possible solutions (if you see more or a better one please leave a comment)
- Creating new contentdbs using a script including the lines mentioned above to grant the permission.
- Grant the ECS service account a higher SQL server role. (Which will cause a conflict to the “least privilege account” concept which I allways try to follow.
For me I will stick to approach 1 (2 is not an option at all for me) and hope Microsoft will provide an fix for this soon.