Excel Services 2010 Error - “The workbook cannot be opened.”

Question

I enabled Excel Services and added a Excel Web Access viewer to my page. It's just a simple spreadsheet (which incidentally works fine on my standalone development server) that displays the error "The workbook cannot be opened." when the page loads. The trusted file locations are set up appropriately. We are using Kerberos authentication.

My event viewer showed this "Critical" event (ID: 3760):

SQL Database 'Prod_WSS_Content' on SQL Server instance 'servername' not found. Additional error information from SQL Server is included below.

Cannot open database "Prod_WSS_Content" requested by the login. The login failed. Login failed for user 'domain\user'.

In other words, the service account that ECS was running under did not have permission to the content database. So I granted the user read permission on the database. Then I got the same error and this "Error" level event in the event viewer (ID: 5617):

There is a compatibility range mismatch between the Web server and database "Prod_WSS_Content", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range.

This error is typically associated with 2007 to 2010 upgrades apparently, but I did a fresh 2010 installation on a pristine machine. I found a very helpful post on the subject on the MSDN blogs: http://blogs.msdn.com/b/jjameson/archive/2010/05/04/the-workbook-cannot-be-opened-error-with-sharepoint-server-2010-and-tfs-2010.aspx

I gave the ECS service account db_owner permission on the content database. I'm still getting the error "The workbook cannot be opened." I ran the PowerShell commands listed at the bottom of the post, and no change.

What's interesting now, is that nothing at all is showing up in the event viewer. I wonder if it is because of the statement in the event log that says "connections to the data have been blocked due to this incompatibility." Is there something on the SharePoint server blocking the connection to SQL Server that needs to be reset?

Answer 1

http://wellytonian.com/2011/10/excel-workbook-cannot-be-opened-error-%e2%80%93-sharepoint-2010/ Similar issue

I had a similar issue couple of hours before writing this blog post. Simple Doc Lib that wouldnt open Excel Workbook within the Browser. My Google page as usual has turned pink searching for an answer.

The solution was either one of the following

1. Hotfix missing within your environment. The post has a link to the Hotfix. You would need this no matter what.
2. The Script helps resolve the permission issue for your Service Application's Service Account to have proper authentication within Content DB on SQL> Ultimately it is giving the service account DBowner status and nothing else :) but we all know how touchy DB Admins are aye :) hence the script.

Answer 2

I had the same error "The workbook cannot be opened." on pretty much a fresh 2010 install too--except i hadn't even configured kerberos or any trusted file location.

All I did was the first thing your links to blogs.msdn.com suggested--to give the account db_owner on the content db (which doesn't seem right, but works).

And from then on the spreadsheet loaded fine, in the doc lib and in the excel web part. I didn't have to reset any services or do any PowerShell. I did close browser session down and open a new one, but that was it.

thanks for the tip. Hope you solved yours.

Answer 3

Great Article Thanks for posting. Attempt all other options; but finally result into giving the AppsPool account for Excel Web Application Service db Owner of the content database that house the location of the excel spreadsheets.

Answer 4

Microsoft say you need to give your Excel Services account access to all content databases in the SP2010 Kerberos Guide. You can do this with the following PowerShell instead of manually with SQL Management Studio:

$w = Get-SPWebApplication -Identity http://portal
$w.GrantAccessToProcessIdentity("vmlab\svcExcel")

Answer 5

Late post, I know but it might help someone...

After following the above guides I had to remove the Excel Services Application Proxy and the Excel Services Application and recreate them again. After that everything worked.

Answer 6

I don't know what the magic combination was, but we restarted all the servers (SharePoint and DB) because of a different issue and created a new site collection. ECS works as expected now.

Answer 7

What a coincidence,i too had the same error "The workbook cannot be opened." But even i have installed SP2010. I also could not find the correct reason. Please anybody help..

http://godwinsblog.cdtech.in/2010/11/sharepoint-2010-installation-language.html

Answer 8

Technet Has an article that gives the least privilege. Its the best solution I could find.

If db_owner solves the problem, follow the steps below to give the least privilege access to your service accounts:

1. Let us assume we have 4 accounts: domain\sp_visio, domain\sp_word, domain\sp_excel, domain\sp_ppt that are not used anywhere else except in Word, Excel, PowerPoint and Visio service applications.

2. Open the SQL Server Management Studio and connect to your SharePoint 2010 database instance.

Note: if you gave any of these accounts db_owner, make sure you remove the db_owner and give them db_datareader and db_datawriter roles before going forward.

1. Go to the SharePoint content database that contains your site collection (ex. http://intranet). For a reference purpose, let's call this content database SP_Content_01.

2. Under SP_Content_01 -> Security -> Roles -> Database Roles, right-click and select to create a New Database Role.

3. Enter WSS_Content_Application_Pools as the role name and dbo as the owner.

4. On the same screen, click Add button located at the bottom to add domain\sp_visio, domain\sp_word, domain\sp_excel and domain\sp_ppt accounts.

Note: Assumption is that these accounts already exist in SP_Content_01 -> Security -> Users prior to this step.

1. After adding necessary users, click Securables on the left and then click Search button on the right.

2. With the All objects of the types selected, click OK.

3. Choose an object type as Databases and click OK.

4. At the bottom in Explicit tab, look for Execute and check the Grant checkbox. Click OK.

5. Under SP_Content_01 -> Security -> Schemas, right-click and select to create a New Schema.

6. Create a schema with WSS_Content_Application_Pools as both the schema name and schema owner. Click OK.

7. Make sure you delete your browsing history, offline files, etc. in your browser. Run an iisreset –noforce in your SharePoint front end servers.

8. Try opening Word, Visio, PowerPoint and Excel document in a browser and also try editing them to make sure your Office Web Apps work fine.