none
SharePoint 2010 SSRS 2008 SP2 - User cannot be found for some reports after database attach migration from WSS 3.0

    Question

  • We have been struggling to find the solution to the following problem for 2 days now with a very difficult to trace error.

    We have used database attach method to migrate the content from WSS 3.0 to SP 2010 successfully. Then we upgraded our SSRS server to 2008 SP2 and upgraded the SharePoint on SSRS server to 2010. The issue we have is not all reports created by a user giving the "User cannot be found" error. Some reports work fine from the same user but some throwing this error. Followed the suggestion from the blog  http://chanakyajayabalan.wordpress.com/2011/06/17/ssrs-report-server-has-encountered-a-sharepoint-error-user-cannot-be-found/ to see what user is causing the issue. But we don't see any info from the tracelog related to the proc_SecGetPrincipalById call. How can we pin point what user ID SharePoint is trying to use on the report that is failing? Is there any way we can increase the debugging to see what is going on with this either on SSRS server or SharePoint server? Really appreciate if anyone share the solution to this. Reviewed all traces. The following is the info from:

    1. SharePoint 2010 trace log

    07/28/2013 07:43:56.70  w3wp.exe (0x1010)                        0x121C SQL Server Reporting Services  SOAP Client Proxy              aacw High     Exception encountered for SOAP method GetItemDataSources: 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.     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.GetItemDataSources(String Item)     at Microsoft.SqlServer.ReportingServices2006.RSConnection2006.SoapMethodWrapper`1.E... 55d7d760-050c-4f51-9fe4-8ec42f22aac3
    07/28/2013 07:43:56.70* w3wp.exe (0x1010)                        0x121C SQL Server Reporting Services  SOAP Client Proxy              aacw High     ...xecuteMethod() 55d7d760-050c-4f51-9fe4-8ec42f22aac3

    2. SSRS 2008 SP2 trace log

    library!ReportServer_0-2!1074!07/28/2013-07:43:56:: Call to GetPermissionsAction(http://sp2010server1/dashboards/Dev Reports/report1.rdl).
    library!ReportServer_0-2!1074!07/28/2013-07:43:56:: Call to GetItemTypeAction(http://sp2010server1/dashboards/Dev Reports/report1.rdl).
    library!ReportServer_0-2!1074!07/28/2013-07:43:56:: Call to GetItemDataSourcesAction(http://sp2010server1/dashboards/Dev Reports/report1.rdl).
    library!ReportServer_0-2!1074!07/28/2013-07:43:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error., ;
     Info: Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: User cannot be found.
       at Microsoft.SharePoint.SPUserCollection.get_Item(String loginName)
       at Microsoft.ReportingServices.SharePoint.Server.

    Thanks

    • Changed type ski13 Monday, July 29, 2013 4:26 AM This is a question
    Sunday, July 28, 2013 1:25 PM

Answers

  • After a through research, we found what is causing the issue and resolved it. Below is the method you can use to identify and fix the issue.

    1. Connect to the content database using SQL server management studio. You can identify the database name using central admin site.

    2. Select list ID from the database using the following SQL command. Replace the

    -- get the list ID
      select tp_ID FROM [RFPRDWSS30SP2_Content].[dbo].[AllLists]
      where tp_Title like '%<put your list title here>%';
     

    3. Get the leaf name and identifier in the list.

      -- get docLibRowId
      select DoclibRowId, LeafName
      FROM [RFPRDWSS30SP2_Content].[dbo].[AllDocs]
      where LeafName like '%<put your list item name here>%' and
      ListId = '<put the list ID value you got in step#2 above>' and
      DirName like '%<put your list title here>%'

    4. Get the author and editor of the item from AllUserData table. 

      -- get the author and editor of the item 
      select * -- tp_id, tp_author, tp_editor
      from [RFPRDWSS30SP2_Content].[dbo].[AllUserData]
      where
      tp_ListId = '<put the list ID value you got in step#2 above>' and
      tp_ID = <put the DoclibRowId value from the step#3>;
     

    5. Find out the deleted and isActive flags for the author and editor of the item

      -- get the tp_Deleted and tp_isActive values for the orginal author and modified user
      select * from
      [RFPRDWSS30SP2_Content].[dbo].[UserInfo]
      where tp_ID = <put the author or editor ID from step#4 above>;  -- 378 / 424
     

    6. If the tp_deleted value is not 0, then, set it to 0 by updating the table. 

    -- update the tp_Deleted value if it is not 0
      -- currently the value of tp_Deleted is 378 for tp_ID = 378
      UPDATE [RFPRDWSS30SP2_Content].[dbo].[UserInfo]
      SET tp_Deleted = 0
      WHERE tp_Id = <put the author or editor ID from Step#4 above>

      AND tp_SiteID = '<put the site ID returned from the step#5>';

    -------- 0 ---------------

    Hope this helps. If you need any further help, please send an email - atginfo@adeptechglobal.com with your question.

    Thanks.

    • Marked as answer by ski13 Monday, July 29, 2013 4:26 AM
    Monday, July 29, 2013 4:24 AM