Executing a SSRS report in the VS works fine, but when deployed in SP environment throws error “User cannot be found“. Below is the detailed error.
System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Report Server has encountered a SharePoint error. —> Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. —> Microsoft.SharePoint.SPException: User cannot be found.
— End of inner exception stack trace —
at Microsoft.ReportingServices.WebServer.ReportingServiceSPImpl.ListParents(String Item)
at Microsoft.ReportingServices.WebServer.ReportingService2006.ListParents(String Item)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object parameters)
at Microsoft.SqlServer.ReportingServices2006.ReportingService2006.ListParents(String Item)
at Microsoft.ReportingServices.SharePoint.Soap.ReportService2006.ListParents(String Item)
But other reports in different sites of the same farm seemed to be working fine. Then digging further, we came to find that the site has been restored from a different domain. The report seemed to reference a user that was in the old domain. The user is marked deleted(tp_deleted flag set to 1) in the Content DB. We just need to set this as o. But we have to find who is that user and then do the change actually. Follow the below simple steps..
- Open SQL Profiler and set up a trace on the target SharePoint Content DB.
- Start SQL Profiler trace.
- Open the report you are getting the error in a browser.
- Go back to the SQL Profiler Trace. You should see, somewhere, a string which looks like the following
<GUID> is the GUID of your SharePoint site.
<INT_1> is the user id in UserInfo table that cannot be found and which we want to update.
- Run the following SQL :
SELECT * FROM UserInfo WHERE tp_SiteID = <GUID> AND tp_ID = <INT_1> (<GUID> & <INT_1> comes from step 5)
- Check if the value of tp_Deleted = 1. If yes set it to 0 using the below sql query.
UPDATE UserInfo SET tp_Deleted = 0 where TP_Id = <INT_1> AND tp_SiteID = <GUID>
- If the error persists, repeat steps 3 to 7 as there may be more users.
You are done. Reports work like charm 🙂