Bug Report - Active Directory users not listed in the dbo.Users table are unable to upload reports to Power BI Report Server (January 2024)
We are running several on premises Power BI Report Server installations in different Active Directory domains. All the report servers are running the latest update - January 2024. We recently discovered that some of our report developers are unable to upload new or changed reports to some of our report servers.
Steps to reproduce the issue
- The affected report developer (the user) logs in to the web interface for the report server at https://ServerName.mydomain/Reports using his or her Active Directory account (Kerberos authentication - single-sign-on since the report server web sites are part of the intranet).
- The user browses to a report folder on the report server.
- The user clicks Upload in the web interface and chooses a PBIX file from the local computer.
- If the target report already exists, the user is presented with a dialog asking if the report should be overwritten. The user selects Overwrite.
- At this point, the web interface seems to hang from the perspective of the user. No progress feedback is provided and the web interface is grayed out.
- After precisely 5 minutes, the web interface reports to the user that the upload failed with the following error message:
An error has occurred.
An internal error occurred on the report server. See the error log for more details.
Information points
- For non-affected report developers, the above operations work without any delays and without any error messages. New or changed reports are uploaded within a few seconds to the various report servers.
- Affected report developers can upload reports to some report servers without any issues. On the affected report servers the operation fails as described above.
- All affected and non-affected report developers are members of the same Active Directory groups. All access management in Site Settings and specific report folders is administered using Active Directory groups that are common to the report developers.
- Access configuration is the same on both affected and non-affected report servers.
- Affected developers can even be system administrators, with full rights everywhere in the report server web interface through their AD group memberships, and still be affected by the issue.
Further investigation results
- The only common cause for this issue that we have found is that:
- On the report servers where the issue exists, the personal Active Directory user accounts of the affected report developers are not yet listed in the table dbo.Users in the ReportServer database.
- As soon as the user account of an affected report developer is added to the dbo.Users table separately, for example by temporarily adding some access rights directly to the personal user account on a report folder, uploading reports starts working for that user everywhere on that report server. Later removing the temporary access rights again does not impact the outcome of the report upload operation.
- On the report servers, we have only found one error message related to the problem. It can be found in the log file RSPortal_<datetime>.log.
<datetime_start>|INFO|138|Received request PUT /api/v2.0/catalogitems(<ItemGUID>)| RequestID = <RequestID>
<datetime_start + 300 seconds>|ERROR|138|library|Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: , Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- 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 Dapper.SqlMapper.<QueryRowAsync>d__34`1.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 Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.<QueryFirstOrDefaultAsync>d__15`1.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 Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection.<QueryFirstOrDefaultAsync>d__14`1.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 Microsoft.BIServer.Configuration.Catalog.CatalogAccessFactory.<QueryFirstOrDefaultAsync>d__5`1.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 Microsoft.ReportingServices.CatalogAccess.CatalogDataAccessor.<GetUserID>d__9.MoveNext()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
at Microsoft.ReportingServices.Portal.Repositories.CatalogItemRepository.SetDataModelDataSourcesTrusted(IPrincipal userPrincipal, Guid itemId, IEnumerable`1 dataSources, Boolean isOverwrite)
at Microsoft.ReportingServices.Portal.Repositories.CatalogItemRepository.<>c__DisplayClass82_0.<| RequestID = <RequestID>
<datetime_start + 300 seconds>|INFO|138|library|Report server dump occured. Exception: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out, Message: , Unhandled Exception: False| RequestID = <RequestID>
<datetime_start + 300 seconds>|INFO|138|library|Report server unique dump occured. Exception: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out, Message: , Unhandled Exception: False| RequestID = <RequestID>
<datetime_start + 300 seconds>|INFO|138|library|Dump result: External dump process return code 0x20000001.
External dump process returned no errors.
.| RequestID = <RequestID>
<datetime_start + 300 seconds>|ERROR|138|OData exception occurred: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out.| RequestID = <RequestID>
<datetime_start + 300 seconds>|INFO|139|Sending response. Response code <UserDomain>\<UserName> 500, Elapsed time 0:05:05,7753504| RequestID = <RequestID>
- Monitoring of and traces on the SQL Server instances where the ReportServer databases reside reveal that:
- There are two SQL sessions involved in the report upload activity from the report server side:
- Session A - application name "Report Server" - responsible for uploading the report and modifying the related SQL tables.
- Session B - application name "RSPortal" - responsible for reporting status of the operation back to the report developer.
- Session B is blocked by session A until session B reaches its execution timeout of 5 minutes and is terminated by the report server, resulting in the error message in the RSPortal.log file above.
- The last few SQL statements in session A before the error occurs can be seen below:
exec SetLastModified @Path=N'<ReportPath>',@ModifiedBySid=<SID_of_Report_Developer>,@ModifiedByName=N'<UserDomain>\<UserName>',@AuthType=1,@ModifiedDate='<ModifyDate>'
exec UpdateCatalogContentSize @CatalogItemID='<ReportItemID>',@ContentSize=262381
exec UpdateDataModelParametersById @CatalogItemID='<ReportItemID>',@Parameters=N'[]'
- The last SQL statement in session B can be seen below. It is blocked by an active transaction in session A above.
declare @p4 uniqueidentifier
set @p4=NULL
exec GetUserID @UserSid=<SID_of_Report_Developer>,@UserName=N'<UserDomain>\<UserName>',@AuthType=1,@UserID=@p4 output
select @p4
- The blocking in the database is centered around the dbo.Users table in the ReportServer database. An open transaction in Session A holds an exclusive lock on the table, causing session B to pause, waiting for the table to become available again.
- When monitoring the sessions interactively during the execution timeout period, no active work is being performed by session A after executing the last few statements above. No reads or writes are taking place and no CPU resources are consumed even though the session has an active transaction running.
- When the execution timeout for session B occurs, the operations in both sessions are rolled back and errors are reported in the report server log files and in the user interface to the report developer.
Workaround
- Add the personal user accounts of the affected report developers to the dbo.Users table in the ReportServer database separately.
- Example: Grant access rights in Site Settings or on a report folder to the specified personal user account. Then remove the access rights.
When the workaround is in place, session A above behaves differently and continues executing SQL statements during the report upload operation. No blocking between session B and session A occurs.
Session A SQL statements with the workaround in place:
exec SetLastModified @Path=N'<ReportPath>',@ModifiedBySid=<SID_of_Report_Developer>,@ModifiedByName=N'<UserDomain>\<UserName>',@AuthType=1,@ModifiedDate='<ModifyDate>'
exec UpdateCatalogContentSize @CatalogItemID='<ReportItemID>',@ContentSize=262381
exec UpdateDataModelParametersById @CatalogItemID='<ReportItemID>',@Parameters=N'[]'
-- Session stops here when the report developer is not in the dbo.Users table
exec ObjectExists @Path=N'<ReportPath>',@AuthType=1
exec GetAllProperties @Path=N'<ReportPath>',@AuthType=1
...
