SharePoint 2010 Excel Services Failure and You

An odd issue happened when I tried to configure SharePoint 2010 Excel services with the new MySites. I uploaded a test Excel workbook and tried to open it, but I kept on getting the following error.

An error, which I might add, was very non-descript

“The workbook cannot be opened.”

Wow, of course with that piece of information how could I not know what it was. So I started from the basics. I checked to make sure Excel application services was running and configured properly. Check and check. Hmmm… I was stuck until I noticed this interesting Windows Application log entry.

Log Name:      Application

Source:        MSSQL$SHAREPOINT
Date:          7/6/2010 3:58:41 PM
Event ID:      18456
Task Category: Logon
Level:         Information
Keywords:      Classic,Audit Failure
User:          DOMAIN\SP_Services
Computer:      sharepoint.domain.local
Description:
Login failed for user ‘DOMIN\SP_Services’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

Quickly followed by this Windows Application log

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          7/6/2010 3:58:41 PM
Event ID:      3760
Task Category: Database
Level:         Critical
Keywords:
User:          DOMAIN\SP_Services
Computer: sharepoint.domain.local
Description:
SQL Database ‘WSS_People_Content’ on SQL Server instance ‘SQLserver\SharePoint’ not found. Additional error information from SQL Server is included below.
Cannot open database “WSS_People_Content” requested by the login. The login failed.
Login failed for user ‘DOMAIN\SP_Services’.

Interesting right? It seems even though have everything configured correctly for Excel Services to run correctly some privileges need to be configured in SQL server.

I logged into the SQL server opened the mapped windows account by opening the following:

SQL server>Security>Logins>DOMAIN\SP_Service

Right click on account select properties. From there go to ‘User Mapping’ and check the target database and add db_owner and public permission set for that user. In addition I added the account ‘DOMAIN\SP_Service’ as the default Schema. Your mileage may vary but you get the idea. Just fill in your domain info as needed.

If this does not make sense. contact your Database administrator to make the needed changes. Just tell him/her the something like the following:

“The <ACCOUNT> account needs to be mapped to the <DATABASE> database with ‘db_owner’ and ‘public’ access” If they ask why. Tell them know something like, “SharePoint 2010 Excel service, which runs under this <ACCOUNT> account needs to have ownership privileges over the <DATABASE> database to run properly.”

Once this is done run a IISRESET on the SharePoint web front end and retry opening the Excel document. After I know what I was looking for I found this article, “The workbook cannot be opened” Error with SharePoint Server 2010 (and TFS 2010)”while not exact was close enough. He hypothesis that Excel services creates new tables in the target database. Which is why simple db_datareader and db_writer are not enough.

In my SharePoint 2010 Farm I routinely use least privilege access installs with separate databases servers. In all cases exercise care, but mapping access in SQL is not really a deal breaker.

–Javi

About the Author

1 thought on “SharePoint 2010 Excel Services Failure and You

Leave a Reply

%d bloggers like this: