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:
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.