Radi Atanassov

SharePoint MCM, MVP, MCT and owner of OneBit Software

Troubleshooting Permissions with Excel Services

I was setting up Excel Services with Kerberos following the steps of this great whitepaper:

Configuring Kerberos authentication for SharePoint 2010 Products

I did everything fine, except I missed a step a while back when initially configuring the farm with least privileged accounts. This led to an interesting permission troubleshooting exercise, so I thought it might be interesting to share.

It started of with a frequent error when opening an Excel document with a connection to a database on an SQL server. The error: The workbook cannot be opened.

clip_image001

Great.

First place to look: the grand ULS logs:

clip_image002

The error was obvious, the Excel Service Application service account didn't have access to the Content Database of the web application. But hang on, I was confident I allowed it access via the PowerShell script described in the document:

$webApp = Get-SPWebApplication -Identity https://claims.mcm.com

$webApp.GrantAccessToProcessIdentity("mcm\svc_excel")

So I did it again:

clip_image003

Naturally, the next step would be to check the DB's, and to my surprise there was no "SharePoint_Shell_Access" assigned to the svc_excel user. In fact there was nothing in there for him...

PowerShell didn't indicate any errors, but ULS clearly did:

clip_image004

The user running the PowerShell script (mcm\sharepointowner), which is also the SP Farm Account, didn't have access to the DB. But more specific, I must have forgot to give that user PowerShell access in this particular SharePoint farm. I'm sure I did it, but it must have been on another farm (my lab is running 3 farms).

Most of you will have guessed the solution by now - run the PS Add-SPShellAccess command as I should have during the install of the farm:

Add-SPShellAdmin -Username "mcm\sharepointowner" -Database (Get-SPContentDatabase -Identity "WSS_Content_ClaimsDB")

Here's a PS screenshot:

clip_image005

Running the GrantAccessToProcessIdentity() goes ahead again, but this time the ULS logs appropriately show success:

clip_image006

It is worthwhile to point out how the command adds "mcm\sharepointowner" to WSS_ADMIN_WPG and gives "SharePoint_Shell_Access" to both the Config and Admin Content DB's on top of the Content DB.

Hope this helps!