SSRS My Subscriptions view

Subscriptions failing after migrating a Native mode SSRS Report Server database

I was recently involved with the migration of a Native mode SQL Server Reporting Services (SSRS) database from a non-domain server to a domain-based server.  The migration itself was fairly straightforward (as it usually is) thanks to the inherent portability of the Report Server database and required configurations, such as encryption keys and .NET config files.  However, after getting everything up and running on the new server, and successfully testing the reports manually, I discovered that the report Subscriptions were failing.

Looking into the SSRS logs on the server, I discovered the below exception:

Microsoft.ReportingServices.Diagnostics.Utilities.AuthorizationExtensionException: An error occurred when invoking the authorization extension. —> System.ArgumentNullException: SafeHandle cannot be null. Error occurred when invoking the authorization extension.

Naturally, my response was to head over to Google and search for the error message.  The majority of the results I found were relating to problems with custom authorisation components, something I don’t use.  So, I posted a question over on MSDN social and waited for a response.

 

The first response to my question provided the (in hindsight, rather obvious) answer.

The ReportServer database used by SSRS to store the subscriptions maintains a record of the subscription owner (as well as audit fields) which track the user accounts that have created/modified the subscriptions.  My subscriptions had been created on the original non-domain server using a Local User account.  Therefore, once the instance was migrated to a new server on the domain, said Local User was no longer available.  Every user with access to the ReportServer database has an entry created in the Users table and a unique GUID generated.

 

To work around the issue, I ran a simple SQL Update query which changed the OwnerID and ModifiedByID fields on the Subscriptions table to relate to the GUID of the equivalent user on the domain.  Once I did this, the error disappeared and subscriptions began to arrive again.

Details on what to consider during SSRS migration are available over at MSDN, there’s no point in me re-writing them here.  Check it out if you’re looking at undertaking an SSRS migration.

 

Have you encountered this error or anything like it during or after SSRS migration?  Let me know in the comments below.

Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET, now mainly AWS. Data geek, football fan, and Xbox gamer. Check out my other blog Press B To Parent if you're a gamer.

Leave a Reply

1 Comment

  1. sharmila

    I have a similar kind of an issue with migrating a reporting server to a different domain. I can view some folders but not all the folders in the new server with the new domain login. I updated all the objects in the catalog table (modifiedbyid, createdid) that had the replicated domain id . I am still not able to view the folder. What am I missing here? Any help would be appreciated.

Next ArticleMetadata Discovery in SSIS 2012 not working with temp tables