Not too long ago I ran into a crazy situation where just about everything that could have gone wrong with my SharePoint environment after a restore did go wrong. I was planning on blogging a bunch of it and thanks to time constraints I had to just get it backup and running and couldn’t take down notes about most of it. After getting everything back up and running I thought I was good to go until two days later when an interesting issue came up. Running an SSRS report (SSRS 2012 using the SSRS Service Application) the first time would work fine, but if I changed the parameters or hit the View Report button a second time the following error would come up.
[error]The permissions granted to user ‘DomainUser’ are insufficient for performing this operation. Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ‘DomainUser’ are insufficient for performing this operation.[/error]
My execution account was set up and consequently not the account listed in the error message. The more intriguing part was that the account noted in the error message was was the service account running SSRS so it had all necessary rights on the reporting services databases. Just for the heck of it and to speed up my troubleshooting I made the account a sysadmin (yes, I know…I know…it was in DEV, calm down please) and the error persisted.
According to section 8.3 of this MSDN page (http://msdn.microsoft.com/en-us/library/ee384252(SQL.100).aspx) there are two possible reasons for the given error:
- This is caused when the application pool account that runs the SharePoint Web application you are on in IIS and the application pool account that SharePoint believes is running the site differ. This happens if the application pool account is changed directly in IIS instead of through SharePoint (using the approved method).
- This error can be caused when Kerberos authentication has been set up correctly and delegation in a double hop scenario will succeed, but Reporting Services integration has been set up for Trusted Account rather than Windows Authentication. This causes SharePoint to continue to impersonate the logged-on user when contacting Reporting Services; when Reporting Services compares the application pool account to the user account making the request, they are not equal.
Awesome. Kerberos isn’t in play so that narrows down my problem, right? If you’ve read many of my blog posts you will know the answer: WRONG! Of course my application pool account was the same in IIS and in SharePoint. The password for the account had not been changed and other applications using the same account were functioning just fine. As it turns out the first option had some merit. Since the two accounts were the same here is the final order of operations that fixed the problem for me:
- Change the service account for the application pool in SharePoint to a DIFFERENT account
- Change the service account for the application pool back to the desired account
- Do an IIS reset on all SSRS application servers
- Happy reporting
I didn’t even need to touch the IIS interface to modify the account.
Brad you beauty. Had me struggling with all sorts of spn differences between test and prod. Nice work.
Thank you!! I have performed the SP2010 to SP2013 upgrade multiple times in our test environment and encountered numerous errors EXCEPT this one. I am literally in the middle of upgrading our production server and just got this error. You saved me so much time, thanks!
Which application pool in SharePoint? I have three websites in IIS: SharePoint:80, SharePoint:8000, and SharePoint:1000 (for Central Administration) — only two of which are visible in the Central Administration > Service Accounts > Credential Management page (the one that is not visible is the Central Administration Web Application Pool). So, to clarify, if I wanted to change the service accoutn associated with the Application Pool for Central Administration I would not be able to.
YES! Who would think to change the account from the correct setting? A stroke of genius, and something I’ll add to my “Microsoft Solutions” file along with “reboot it” and “iisreset”.
This is not unlike the solution I found when I had a web application where the root site collection was inaccessible even though others SCs in the web app were – enable anonymous access, then disable anonymous access.
I had the same issue (stopped 2 days after restore) but the steps above did not resolve it for me. Finally fixed it by setting an “Execution Account” in SQL Reporting Service settings
You may also see this error if the datasource of the report is in pending state. If versioning is enabled for the datasource library you will need to approve that first.
Your blogged really helped solve my, as you put it, crazy situation. I rebuilt the web application and everything was fine except the problem referred to in this blog. After double checking all the appropriate settings, I was left a little stumped. When I say this blog, it made perfect sense and