Unable to run any SSRS report last modified by this user

Question

When I try to run any report by one of our previous SharePoint admins, I'm getting error.

Report Server has encountered a SharePoint error. (rsSharePointError) Object reference not set to an instance of an object. For more information about this error navigate to the report server on the local server machine, or enable remote errors

The log file doesn't give much more:

library!ReportServer_0-7!d84!04/18/2016-16:24:45:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: , Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpUser.get_LoginName() at Microsoft.ReportingServices.SharePoint.Utilities.CatalogItemUtilities.GetListItemUserLoginName(RSSPListItem item, String fieldName) at Microsoft.ReportingServices.SharePoint.Utilities.CatalogItemUtilities.CreateCatalogItem(RSSPFile file) at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPFileProperties(RSSPFile file) at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.GetSPItemProperty(ExternalItemPath wssPath, CatalogItemDescriptor& itemDesc, String& customizedPropertyXml) at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.GetAllProperties(ExternalItemPath objectName, ItemProperties& properties, Guid& id, Guid& linkID, ItemType& type, Byte[]& secDesc, Int32& executionOptions, Int32& snapshotLimit, String& subType) --- End of inner exception stack trace ---;

This seems to have something to do with the user being previously in our system, them removed and re-added, but I can't see how to resolve these errors. If I upload a new version of the SSRS report (thus becoming the last modifier), then it runs, but there are many of them, and this seems like a bad solution.

I've been doing some research here, here, and here, but I can't figure out how to resolve.

What else can I do or check?

EDIT: SSRS SQL Server 2008 R2, Sharepoint 2010.

Answer 1

As I mentioned in my comment, Microsoft recommends fixing this issue by applying a CU: https://support.microsoft.com/en-us/kb/2878807

Consider the following scenario: You are running an instance of Microsoft SQL Server 2008 R2 Reporting Service (SSRS 2008 R2) or SQL Server 2012 Reporting Service (SSRS 2012) in Microsoft SharePoint integrated mode. You have a report or subscription that was authored by a user who no longer has permissions on the SharePoint site collection. A user who has the appropriate permissions on the SharePoint site collection tries to open the report or subscription. In this scenario, the user cannot open the report or subscription.

If you're on SQL Server 2012 SP1, this would be in CU 8, like the link says. In SQL Server 2012, you're looking at CU 11.

It looks like this bug fix is bundled into the Cumulative Update Package so you can't just apply your single issue's hotfix, but the CU installation should be a straightforward upgrade process - of course, you should absolutely test the CU on your development environment before applying it to any higher environments.

Technically speaking you would only need to install this CU on the SQL Server Instance that hosts your Reporting Services Integrated Mode instance - that being said, if all your SQL instances are at the same patch level I'd recommend keeping that in sync when you upgrade.

Hope this helps!

Answer 2

I'm sure SP2 with CU 9 is the right way to fix the problem, but I've found a workaround for the immediate term. The "object" that was null was the author/editor of the sharepoint document. In the WSS_Content_... database, this problem is in orphaned records between 2 tables.

The user in question had been removed from UserInfo, where a tp_ID (int column) acts as a user id. This tp_id is stored in the UserDataVersioned table in columns tp_author and tp_Editor.

So, the UserDataVersioned.tp_Author and UserDataVersioned.tp_Editor columns had values that did not exist in the UserInfo.tp_ID column for these reports. To correct, I added the user back, then changed her UserInfo.tp_ID to match the UserDataVersioned.tp_Author and UserDataVersioned.tp_Editor columns (in other words, her OLD tp_ID). Now that they match, the reports run!

This is NOT a permanent solution, as the next time an author is removed we will have the same problem with their reports. I'm confident the SP and CU solution would actually prevent that problem, but luckily I stumbled upon this fix, and have now shared it for all posterity here.