- Brain LOGs

While publishing a Excel Sheet from Microsoft Excel 2010 to my local development SharePoint Server 2010, I got this error on the browser – “The workbook cannot be opened“.

Quick steps to publish Excel data into SharePoint: File -> Save & Send -> Save to SharePoint -> (Select your location) – (Use Publish Options to select the scope of excel data)

By the way, I used the instructions in Chapter 1 of Real World SharePoint 2010 book to build my SharePoint 2010 virtual machine for development.  I guess I created multiple AD users (sp_farm, sp_serviceapp, sp_apppool,  SQL_service) and messed up with some settings. Let us forget the past and move ahead to troubleshoot the above error.

1. Quickly searched Internet and got multiple ways to resolve this problem.  I was not sure which one will suit my need.
2. Thought why don’t I first get the exact issue in the log file.  Went through the Log file in SP_ROOT\Logs folder, but it didn’t help.
3. Got an idea of trying verbose logging for Excel Services.  SharePoint 2010 Central Administration -> Monitoring section -> (Reporting Section) Configure diagnostic logging . Selected Excel Services Application and changed the two drop-down values to verbose.
4. Performed the action of save/publish from Excel again to repeat the error with additional logs.
5. This time I managed to get the below error message in the Log File: Error initializing Safe control – Assembly:Microsoft.Office.SharePoint.ClientExtensions
6. This error is well documented on this Microsoft KB983007. You will get a hint here that this is due to security issue with your Managed Accounts setup. Meanwhile, do not forget to revert back the verbose logging settings that you performed in earlier Step 3.
7. My next task was to find out the Managed Account which is responsible for Excel Services.  To to this, I went back to Central Administration -> Security section -> Configure managed accounts option.
8. Clicked on Edit option against each account and reviewed the farm components using this account. I found that WROX\sp_serviceapp account was using Excel Services Application.
   
9. My next task was to review the security settings of sp_serviceapp user in SQL Server.
10. I launched SQL Server Management Studio and connected to the default instance which is used by SharePoint.
    
11. Reviewed its permission as shown below:
    
12. Noticed that this account didn’t had permission to WSS_Content_3000 and WSS_Content_Team databases, which were the actual Content Databases used by the Web Applications on which I was trying the Excel save/publish operation.
13. Went ahead and assigned db_owner and public roles for both these databases for the user sp_serviceapp.
    
14. Save the above changes by click OK button. (No reboot / iisreset is required)
15. Tried the Excel save/publish operation once again and it worked now!  Below is the expected result:
    

I hope that some of the above steps that I shared above will help you to resolve similar errors in your environment.