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.
Great.
First place to look: the grand ULS logs:
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:
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:
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:
Running the GrantAccessToProcessIdentity() goes ahead again, but this time the ULS logs appropriately show success:
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!
