An Error occurred try again later when trying to modify subscription

[Anonymous]

I am attempting to do an upgrade on Reporting Services 2012 to upgrade it to PowerBI Reporting Services 2017.

When I attempt to edit a subscription, I get an error message that says oops, an error occured, try again later.

When I look at the RSPortal_xxxxx.log I see the following.

2019-06-10 12:12:45.6858|INFO|12|Sending response. Response code DOM\Username 500, Elapsed time 0:00:00.0040419| RequestID = s_685be2a2-c124-42e1-8c12-19ba3758d78c
2019-06-10 12:13:25.7974|INFO|6|CONFIG : AccessControlAllowCredentials = [false] (env)
2019-06-10 12:13:25.7974|INFO|6|Received request POST /api/v2.0/Extensions/Model.ValidateExtensionSettings| RequestID = s_b6e58aad-1a8c-42dc-968f-f923b502bde3
2019-06-10 12:13:25.8130|INFO|7|Sending response. Response code DOM\Username 200, Elapsed time 0:00:00.0178095| RequestID = s_b6e58aad-1a8c-42dc-968f-f923b502bde3
2019-06-10 12:13:25.8130|INFO|6|CONFIG : AccessControlAllowCredentials = [false] (env)
2019-06-10 12:13:25.8130|INFO|6|Received request PUT /api/v2.0/subscriptions%28d91760f6-730d-4393-b1de-92a2f5c95e64%29| RequestID = s_fb5ea4e3-6b85-4a47-a129-0fabb864e6e3
2019-06-10 12:13:25.8287|ERROR|6|OData exception occurred: System.InvalidOperationException: (processing): Adding more than one data source with null original name
at Microsoft.ReportingServices.Diagnostics.Utilities.RSTrace.DefaultRSTraceInternal.Fail(String componentName, String message)
at Microsoft.ReportingServices.Diagnostics.Utilities.RSTrace.Assert(Boolean condition, String message)
at Microsoft.ReportingServices.DataExtensions.DataSourceInfoCollection.Add(DataSourceInfo dataSource)
at Microsoft.ReportingServices.Library.DBInterface.InnerGetDataSources(Guid itemID, Boolean& itemIDisModelID)
at Microsoft.ReportingServices.Library.DBInterface.GetDataSourcesAndResolveModelLink(Guid itemID)
at Microsoft.ReportingServices.Library.SubscriptionDB.GetSubscription(Guid id, IPathTranslator pathTranslator, Boolean isCacheRefreshPlanExpected)
at Microsoft.ReportingServices.Library.SubscriptionManager.GetSubscription(Guid id, Boolean includeEncryptedSettings)
at Microsoft.ReportingServices.Portal.Repositories.SubscriptionService.GetSubscription(IPrincipal userPrincipal, Guid key)
at Microsoft.ReportingServices.Portal.Repositories.SubscriptionService.UpdateSubscription(IPrincipal userPrincipal, Guid key, Subscription subscription)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.SubscriptionsController.PutEntity(String key, Subscription entity)
at Microsoft.ReportingServices.Portal.ODataWebApi.Controllers.Reflection.EntitySetReflectionODataController`1.Put(ODataPath oDataPath, T value)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext().| RequestID = s_fb5ea4e3-6b85-4a47-a129-0fabb864e6e3
2019-06-10 12:13:25.8287|INFO|8|Sending response. Response code DOM\Username 500, Elapsed time 0:00:00.0036945| RequestID = s_fb5ea4e3-6b85-4a47-a129-0fabb864e6e3

I see an error that cays "Adding more than one data source with null original name"

I go into the dataset for this report.  I can validate the dataset and it works fine.

Thoughts?

Thank you, Chris

[Anonymous · Accepted solution]

I found the answer after reading a myriad of articles.

The problem for me is that the RSExecRole was created without any grants added to it.

So, I found this very helpful article that tells you how the RSExecRole should be setup:

https://docs.microsoft.com/en-us/sql/reporting-services/security/create-the-rsexecrole?view=sql-serv...

The bottom line is that you need to run this script.

USE master;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.xp_sqlagent_enum_jobs TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_is_starting TO [RSExecRole];
GRANT EXECUTE ON dbo.xp_sqlagent_notify TO [RSExecRole];
GO
USE msdb;
GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'RSExecRole') BEGIN
CREATE ROLE [RSExecRole];
END
GRANT EXECUTE ON dbo.sp_add_category TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobschedule TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobserver TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_add_jobstep TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_delete_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_category TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_job TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_help_jobschedule TO [RSExecRole];
GRANT EXECUTE ON dbo.sp_verify_job_identifiers TO [RSExecRole];
GRANT SELECT ON dbo.syscategories TO [RSExecRole];
GRANT SELECT ON dbo.sysjobs TO [RSExecRole];
GO

After that I could edit subscriptions and users could create and edit them.

Hope this helps someone.