Cannot click Subscriptions tab after Moving to Windows Authentication from Forms based Authentication

I was working on an interesting case where the reporting service was moved back to Windows authentication from Forms based authentication (FBA).

There were lot of subscriptions which were created using a forms based user account.

Now when the reporting service was moved back to Windows authentication, the reports which contains all these subscriptions we couldn’t click on the Subscriptions tab. When you click it, it was failing with the following error and call stack.

w3wp!ui!5!9/2/2009-11:01:29:: e ERROR: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.

   at Microsoft.ReportingServices.Authorization.SdAndType.PrepareToRestoreBinaryPolicy(Byte[] secDescBlobStored, Hashtable& secDescHash)

   at Microsoft.ReportingServices.Authorization.WindowsAuthorization.InnerCheckAccess(String userName, IntPtr userToken, SecurityItemType itemType, Byte[] secDesc, UInt32& rightsMask, ReportSecDescType rptSecDescType)

   at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)

   at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath)

   at Microsoft.ReportingServices.Library.SubscriptionManager.ListSubscriptions(String user, String path, Boolean pathIsSite)

   at Microsoft.ReportingServices.Library.ListSubscriptionsAction.PerformActionNow()

   at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()

   at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListSubscriptions(String Path, Boolean pathIsSite, String Owner, Subscription[]& SubscriptionItems)

   --- End of inner exception stack trace ---

  at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListSubscriptions(String Path, Boolean pathIsSite, String Owner, Subscription[]& SubscriptionItems)

   at Microsoft.ReportingServices.WebServer.ReportingService2005.ListSubscriptions(String Report, String Owner, Subscription[]& SubscriptionItems)

w3wp!ui!5!9/2/2009-11:01:29:: e ERROR: HTTP status code --> 200

-------Details--------

System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.

   at Microsoft.ReportingServices.Authorization.SdAndType.PrepareToRestoreBinaryPolicy(Byte[] secDescBlobStored, Hashtable& secDescHash)

   at Microsoft.ReportingServices.Authorization.WindowsAuthorization.InnerCheckAccess(String userName, IntPtr userToken, SecurityItemType itemType, Byte[] secDesc, UInt32& rightsMask, ReportSecDescType rptSecDescType)

   at Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)

   at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath)

   at Microsoft.ReportingServices.Library.SubscriptionManager.ListSubscriptions(String user, String path, Boolean pathIsSite)

 

If you look at the above call stack, the current user who is a Windows user account is getting validated against the person who created the subscriptions for the report, which is nothing but the FBA user and that is the place it is failing (Authorization section).

To confirm this I created the following SQL statement,

select s.*,c.Name as [Report Name],u.* from Subscriptions s, [Catalog] c, Users u where s.Report_OID = c.ItemID and s.OwnerID = u.UserID

What is does is, it will give me the list of subscriptions and their details from the Subscriptions table, then display the corresponding Report Name for which it was created, from the Catalog table and then display the user details who created the report from the Users table.

When I ran this SQL statement, it clearly showed that the subscription was created by a FBA user and the AuthType showed 3 which is nothing but custom authentication.

Now there is nothing much we can do here other than dropping the subscriptions and recreating them. But if you’re having too many then that is always going to be a problem.

So the only other remaining option is to DIRECTLY EDIT THE SUBSCRIPTION TABLE’s USERID column, which is NOT RECOMMENDED and FALLS UNDER MICROSOFT UNSUPPORTED SCENARIO.

But Since there are no other option let, the RISK was taken by the END USER himself and implemented the following.

Back up the ReportServer database.

Go to the corresponding subscription in the subscription table and change the value UserID value to one of the valid windows USERID listed in the Users table.

That bought back the subscriptions and things were back on track.