Technology Toolbox

Your technology Sherpa for the Microsoft platform

Jeremy Jameson - Founder and Principal

Search

Search

"The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

Note
This post originally appeared on my MSDN blog:

Since I no longer work for Microsoft, I have copied it here in case that blog ever goes away.

In an earlier post today, I described how I recently upgraded from Team Foundation Server 2008 (and Windows SharePoint Services v3) to TFS 2010 (and SharePoint Server 2010).

While most of the upgrade went fairly smooth, during the process I discovered (what I believe to be) a bug in SharePoint Server 2010 with Excel Services.

Note that if you use Microsoft Office SharePoint Server (MOSS) 2007 or SharePoint Server 2010 when creating new TFS project sites, you get some really rich dashboard functionality that is enabled through Excel Services (which, as I noted earlier, is a compelling reason to upgrade from WSS to SharePoint Server 2010 for TFS project sites).

Also note that Jon Tsao wrote a blog post a couple of months ago with the steps to configure SharePoint Server 2010 for dashboard compatibility with TFS 2010. [Be aware that Jon's post was based on a pre-release version of SharePoint Server 2010, so a couple of the screenshots and corresponding configuration steps are out-of-date (for example, the link you click to configure Excel Services is no longer accessible via the Manage services on server link under the System Settings section of SharePoint Central Administration. Instead, you need to click the Manage service applications link under the Application Management section.]

I followed Jon's post to configure my environment, but I found that when I browsed to a new TFS project site, all of the Excel Web Parts displayed the following error:

The workbook cannot be opened.

Looking at the event log on the SharePoint/TFS server, I found the following error occurred each time I requested the dashboard page:

Source: Microsoft-SharePoint Products-SharePoint Foundation
Event ID: 3760
Task Category: Database
Level: Critical
User: TECHTOOLBOX\svc-spserviceapp-tst
Computer: cyclops-test.corp.technologytoolbox.com
Description:
SQL Database 'WSS_Content' on SQL Server instance 'beast-test' not found. Additional error information from SQL Server is included below.

Cannot open database "WSS_Content" requested by the login. The login failed.
Login failed for user 'TECHTOOLBOX\svc-spserviceapp-tst'.

Note that in my environment, I use different service accounts for the TFS Web application and SharePoint service applications (e.g. Excel Services and the Secure Store Service). I believe Jon configured his environment such that the Web application and SharePoint service applications use the same service account.

I say this because looking at SQL Server Management Studio, I discovered that while the service account for the app pool of the TFS Web application (TECHTOOLBOX\svc-web-tfs-test) had access to the underlying SharePoint content database (WSS_Content), the service account for Excel Services (TECHTOOLBOX\svc-spserviceapp-tst) did not.

This explained the error that I was seeing on my test SharePoint/TFS server (CYCLOPS-TEST) -- which seems like a bug in SharePoint Server 2010. In other words, it appears that you have to use the same service account for the Web application and service applications -- or else Excel Services simply doesn't work (without some workaround).

I confirmed that changing the identity of the application pool in IIS for Excel Services to be the same as the service account for Web application resolved the problem. However, my preference was to keep the discrete service accounts -- if at all possible.

Consequently, I added the service account for Excel Services to the SharePoint content databases for the Web application. At first, I tried giving it "low privilege" access, but I discovered this only resulted in different errors in the event log when browsing to the dashboard page:

Source: Microsoft-SharePoint Products-SharePoint Foundation
Event ID: 5586
Description:
Unknown SQL Exception 262 occurred. Additional error information from SQL Server 
is included below.

CREATE TABLE permission denied in database 'WSS_Content'.

and

Source: Microsoft-SharePoint Products-SharePoint Foundation
Event ID: 5617
Description:
There is a compatibility range mismatch between the Web server and database 
"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.

While it's somewhat bewildering that Excel Services needs to create a table in the SharePoint content database, I decided to just go ahead and give the service account for Excel Services the same permissions to the content database as the service account for the Web application (which, is to say, db_owner). [Yeah, I know, that's essentially the same as using a single service account for both the Web application and SharePoint service applications. Perhaps someday the TFS dashboards and Excel Services will "play nicely" together, even when configured with restricted access.]

To add the the service account for SharePoint service applications to the underlying content databases:

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio. The Connect to Server dialog box opens.
  2. In the Server type list, click Database Engine.
  3. Type the name of the server which hosts the SharePoint content databases, and then click Connect.
  4. In Object Explorer, expand Security, and then expand Logins.
  5. Right-click the login corresponding to the service account used for SharePoint service applications (TECHTOOLBOX\svc-spserviceapp) and then click Properties.
  6. In the login properties dialog box,
    1. On the User Mapping page, in the Users mapped to the login list, click the checkbox for the SharePoint content database (WSS_Content), and then in the database role membership list, click the checkboxes for the following roles:
      • db_owner
      • public
    2. Repeat the previous step for any additional content databases that need to be accessed by Excel Services.
    3. Click OK.
Update (2011-04-14)

I should have updated this post long ago based on the comment added by "todh2" regarding granting access to the database. Instead of using SQL Server Management Studio to configure permissions on the content database for the service account, use a little PowerShell to invoke the SPWebApplication.GrantAccessToProcessIdentity method:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0

$webApp = Get-SPWebApplication "http://cyclops"

$webApp.GrantAccessToProcessIdentity("TECHTOOLBOX\svc-spserviceapp")

Thanks to "todh2" for pointing this out.

Once this configuration change is made, the "workbook cannot be opened" error no longer occurs.

Comments

  1. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    May 5, 2010 7:07 PM
    todh2

    Jeremy,

    Unfortunately, this is by design for SharePoint Server 2010.  Several of the SharePoint Services (not just Excel Services) require this level of permission to the content database (WSS_Content in your case).  Obviously your method will work, but here’s a snippet of PowerShell that will also do the trick (assuming you only have one web application).  First, open SharePoint 2010 Management Shell (from Start -> All Programs -> Microsoft SharePoint 2010 Products) and then type:

     $w = Get-SPWebApplication

     $w.GrantAccessToProcessIdentity("<insert service account>")

        In your case, this line would be:

         $w.GrantAccessToProcessIdentity("TECHTOOLBOX\svc-spserviceapp”)

    Feel free to contact me if you have any questions.

  2. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    July 18, 2010 3:22 AM
    Ofer Gal

    Did not work!

    I get :

    PS C:\Users\galo01> $w = Get-SPWebApplication

    PS C:\Users\galo01> $w[0].GrantAccessToProcessIdentity("Bluffton\svc-fei-spssp-d")

    Unable to index into an object of type Microsoft.SharePoint.Administration.SPWebApplication.

    At line:1 char:4

    + $w[ <<<< 0].GrantAccessToProcessIdentity("Bluffton\svc-fei-spssp-d")

       + CategoryInfo          : InvalidOperation: (0:Int32) [], RuntimeException

       + FullyQualifiedErrorId : CannotIndex

  3. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    July 18, 2010 4:13 PM
    Jeremy Jameson
    Gravatar

    @Ofer Gal,

    Perhaps you should try again but replacing "$w[0].GrantAccessToProcessIdentity" with "$w.GrantAccessToProcessIdentity".

  4. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    November 11, 2010 8:03 PM
    Donnie

    Great article.  Thx for the pShell exec!

  5. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    January 30, 2011 12:24 PM
    how can I do this if I have more than one webapplication ?

    how can I do this if I have more than one webapplication ?

  6. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    January 30, 2011 4:16 PM
    Jeremy Jameson
    Gravatar

    When you have more than one Web application in your SharePoint farm, the Get-SPWebApplication cmdlet returns an array (instead of a single object). Therefore you need to specify which item in the array you want to apply the changes to (e.g. "$w[0].GrantAccessToProcessIdentity(...)" instead of "$w.GrantAccessToProcessIdentity(...)").

    To determine which item in the array refers to the Web application you want to update, simply type "$w" -- which will list the elements in the array. For example, here is the output from one of my SharePoint servers (on which I have created three Web applications):

    PS C:\Windows\system32> $w = Get-SPWebApplication

    PS C:\Windows\system32> $w

    DisplayName                    Url

    -----------                    ---

    SharePoint - 80                http://cyclops-test/

    SharePoint - extranet2.fabr... http://extranet2.fabrikam.com/

    SharePoint - extranet-fabri... http://extranet-fabrikam/

  7. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    February 1, 2011 1:17 AM
    Peruri

    It worked!

    fyi  if you have multiple urls use get-spwebapplication http://siteurl/

  8. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    February 17, 2011 11:46 PM
    Judy Lancaster

    You can also add the excel service account to the Policy for Web Application

  9. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    March 20, 2011 6:57 PM
    Kevin

    todh2 mentioned that this issue occurs with other service applications, anyone know which ones?  I've seen performance point mentioned in a few places...

    Any help appreciated!

  10. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    March 21, 2011 8:35 PM
    Jonathan Herschel

    boya!  worked, thanks!

  11. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    July 8, 2012 1:57 PM
    Sreehari V I
    Gravatar
    I did all these things but still its showing the same message ("Workbook can not be opened."). My Share poijnt server machine doesn't have MS Excel installed in it. Is MS Office necessary in the server ?
  12. # re: "The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)

    July 13, 2012 6:52 PM
    Jeremy Jameson
    Gravatar
    @Sreehari:

    No, Microsoft Office does not have to be installed on the server.

    I recommend you open a support case with Microsoft. Without knowing more details about your specific issue, it is pointless to speculate on why this error occurs in your environment. Good luck.
  13. # Not working in specific Site Collection

    July 28, 2013 3:09 PM
    Royal
    Gravatar
    Hi James,

    In my case if it working fine in one site collections and not working in the different site collection under the same web application.

    For example its working fine under documents in
    mysites.com/.../AllItems.aspx

    but not working in
    mysites.com/.../AllItems.aspx

    I have verified both the site collections and all site features and advance settings are same.

    Do we have any other site level settings? I am keep getting
    Cannot open the workbook error.

    Thanks,

Add Comment

Optional, but recommended (especially if you have a Gravatar). Note that your email address will not appear with your comment.
If URL is specified, it will be included as a link with your name.

To prevent spam from being submitted, please select the following fruit: Grapes

Cherries
Grapes
Pear
Watermelon
Apple
Strawberry
 
Please add 1 and 2 and type the answer here: