Problem

  • Production SharePoint 2010 farm.
  • User base using Office 2016 documents.
  • Excel Calculation Services started on WFE.
  • Excel Services Application Web Service Application running. 

Deployed new web application to farm, dedicated to single site collection.  Claims-based, Kerberos authentication.

Site Collection Administrator reports that when she clicks on an Excel spreadsheet link in a document library, in the new site, the spreadsheet viewer appears in the browser, but then displays the message: The workbook cannot be opened.

Troubleshooting

  • Using own account, was able to repeat the user experience.  Noted down exact time when message was presented. 
  • Searched ULS log on WFE, narrowing search to time when message was presented and the by web application name.
  • Found a number of entries associated with content database of new web application and a particular service account's inability to access that database.  Example:
    System.Data.SqlClient.SqlException: Cannot open database "[content database name]" requested by the login.
    The login failed.
    Login failed for user '[service account name]'.
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)...
    SqlError: 'Login failed for user '[service account name]'.'
    Source: '.Net SqlClient Data Provider'
    Number: 18456
    State: 1
    Class: 14
    Procedure:''
    LineNumber: 65536
    Server: '[SQL server name]'.
  • Using SQL Server Management Studio, checked mapping of [service account name] to [content database name] and found that it was only mapped as public.
  • Checked mapping of [service account name] to other content databases and found it additionally mapped as db_owner.
  • Searched Internet postings on ULS text message and found [1].  This referenced [2].  Further searching found [3].
  • Based upon [1] and [2], executed following script in elevated SharePoint Management Shell on a farm server:
    $wa = Get-SPWebApplication -Identity "[web application URL]" $wa.GrantAccessToProcessIdentity("[service account name]") $wa.Update() 

After executing first two lines, checked mapping of [service account name] and found that it was now mapped to [content database name] as db_owner.  Executed last line as indicated in [1], but did not observe anything else occurring. 

Solution

  • Ensure that Excel Services Application Web Service Application identity is granted access to target content database. 

References

  1. Issue when creating Excel Services application - Service account has no permissions to content database
  2. The Excel Services Application for SharePoint 2010 does not load or display workbooks
  3. The workbook cannot be opened" Error with SharePoint Server 2010 (and TFS 2010)
  4. How to reverse GrantAccessToProcessIdentity(“Domain/User”)?
  5. What method is the reverse of SPWebApplication.GrantAccessToProcessIdentity

Notes

  • tbd