Forum Discussion
Create scheduled refresh plan. An internal error occurred on the report server database.
Hello,
I am trying to troubleshoot a PowerBI report server issue for my clients.
: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
I am not finding any connection failures, timeouts or low disk space on the servers.
In this setup we have two Power BI Report Server instances that are scaled out. Both are version 15.0.1102.235.
For the backend we have two SQL 2016 instances at the latest CU. The ReportServer Databases are in a High-Availability group.
The PowerBI service account is a gMSA account. This same account is used as the database credential.
Report server is also configured to use RSWindowsNegotiate.
It has been noticed that if we create a refresh plan while the Database Listener is pointing to Node 1 it works fine for the client. However if it is failed over to Node 2 the client as well as myself as an admin get an error.
I used RedGate compare to confirm that Node 1 and Node 2 are identical. I then went ahead and enabled Verbose logging on the report server and started a SQL Server Profiler Trace for the backend.
Here are the results:
SQL Profiler:
exec CreateSubscription @Report_Name=N'/client/clientrep',@ReportZone=0,@id='E2C8394D-8B64-4762-83E4-6FCE7C21E4BC',@OwnerSid=0x010500000000000515000000482DD770E9674F175F52937B51600200,@OwnerName=N'domain\me',@OwnerAuthType=1,@Locale=N'en-CA',@DeliveryExtension=default,@InactiveFlags=0,@ModifiedBySid=0x010500000000000515000000482DD770E9674F175F52937B51600200,@ModifiedByName=N'domain\me',@ModifiedByAuthType=1,@ModifiedDate='2019-06-06 12:31:47.460',@Description=N'test',@LastStatus=N'New Scheduled Refresh Plan',@EventType=N'DataModelRefresh',@MatchData=N'<ScheduleDefinition><StartDateTime>06/06/2019 02:00:00</StartDateTime><DailyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer"><DaysInterval>1</DaysInterval></DailyRecurrence></ScheduleDefinition>',@Version=3
RS Log:
2019-06-06 12:31:47.4604|ERROR|20|OData exception occurred: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors at Microsoft.ReportingServices.Library.ReportingService2010Impl.CreateCacheRefreshPlan(String ItemPath, String Description, String EventType, String MatchData, ParameterValue[] Parameters, String& CacheRefreshPlanID) at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateCacheRefreshPlan(String ItemPath, String Description, String EventType, String MatchData, ParameterValue[] Parameters, String& CacheRefreshPlanID) 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.ReportingServices2010.ReportingService2010.CreateCacheRefreshPlan(String ItemPath, String Description, String EventType, String MatchData, ParameterValue[] Parameters) at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.<>c__DisplayClass136_0.<CreateCacheRefreshPlan>b__0() at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod(Boolean setConnectionProtocol) at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.CreateCacheRefreshPlan(String itemPath, String description, String eventType, String matchData, ParameterValue[] parameters) at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.<>c__DisplayClass19_0.<CreateCacheRefreshPlan>b__0() at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithWindowsAuth[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func) at Microsoft.ReportingServices.Portal.Repositories.SubscriptionService.CreateCacheRefreshPlan(IPrincipal userPrincipal, CacheRefreshPlan cacherefreshPlan) at Microsoft.ReportingServices.Portal.ODataWebApi.V2.Controllers.CacheRefreshPlansController.AddEntity(CacheRefreshPlan entity) at Microsoft.ReportingServices.Portal.ODataWebApi.Controllers.Reflection.EntitySetReflectionODataController`1.Post(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_7d594f1e-986f-4b5f-80e2-84012ada6354
Any ideas on how to resolve this?
4 Replies
Hi,
Check this permissions:
And this premissions:
GRANT SELECT ON [dbo].[syslogins] TO RSExecRole
In scalable solution RS have problem with modify TempFiles or creating Jobs (schedulers).
It's working for me.If you are running PBIRS behind a load balancer make sure you have followed all the configuration steps in the following article https://docs.microsoft.com/en-us/sql/reporting-services/report-server/configure-a-report-server-on-a-network-load-balancing-cluster?view=sql-server-2017
Pay special attention to the BackConnectionHostName registry setting (including re-booting if you need to set this property). PBIRS is made up of a set of 4-5 services and if you don't have this set properly a single request from a client can jump back and forth between the nodes in your cluster and this can cause all sorts of weird errors.
And note the following article outlines the manual steps required when failing over in an Availability Group
Steps to complete disaster recovery of Report Server Databases
The following steps need to be completed after a Always On availability groups failover to a secondary replica:
Stop the instance of the SQL Agent service that was being used by the primary database engine hosting the Reporting Services databases.
Start SQL Agent service on the computer that is the new primary replica.
Stop the Report Server service.
If the report server is in native mode, stop the report server Windows server using Reporting Services configuration manager.
If the report server is configured for SharePoint mode, stop the Reporting Services shared service in SharePoint Central Administration.
Start the report server service or Reporting Services SharePoint service.
Verify that reports can run against the new primary replica.
After upgrading to May 2021, I received the same error from one of my Power BI reports when re-applying a schedule refresh to that report. The issue to my case is the the database server's MSDB doesn't have a record for the report in question so the scheduled refresh failed. My solution is deleting the schedule refresh record from the report in question and reschedule the refresh.
