Power BI Report Server Issues with HA and NLB

[Anonymous]

Hello everyone,

I’ve installed Power BI Reporting Services as a scaled out deployment on two servers with the SSRS DB on a HA Always On Cluster. I’ve used a SSL Certificate for a virtual host name which is provided via a Windows Server NLB Cluster.

The configuration are guided by following msdn documentation:

https://docs.microsoft.com/de-de/sql/reporting-services/report-server/configure-a-report-server-on-a...

The most time everything works fine. But sometimes I get a “Element not found” error when I navigating between folders on the report manager webpage. Sometimes I get a error when I publish a PBI Report via the Desktop Client. If I close the desktop client and reopen it, mostly it works. If I upload the report via the webpage, it works almost good.

Does anyone have a idea?

I think there is a misconfiguration with the NLB – is there any best practice settings?

Thanks a lot!

Bye.

PS: The Event Log is clear - client and server side ...

[pbx]

Have you checked the PBIRS logs? They can be found in LogFiles directory in installation path. Check them on both nodes for the time that errors happen. Things can go wrong depending on your URL reservation, SSL CN, and other configurations. The MS documentation is not without errors.

[Anonymous]

Thanks for your reply - i've searched the last RSPowerBI Logfiles and I foud the following errors:

1.

2017-12-30 19:07:00.8832|FATAL|1|Could not start PBIXSystem.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.HttpListenerException: Failed to listen on prefix 'https://<SERVERURLREMOVED>:443/powerbi/' because it conflicts with an existing registration on the machine.
   at System.Net.HttpListener.AddAllPrefixes()
   at System.Net.HttpListener.Start()
   at Microsoft.Owin.Host.HttpListener.OwinHttpListener.Start(HttpListener listener, Func`2 appFunc, IList`1 addresses, IDictionary`2 capabilities, Func`2 loggerFactory)
   at Microsoft.Owin.Host.HttpListener.OwinServerFactory.Create(Func`2 app, IDictionary`2 properties)
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.Owin.Hosting.ServerFactory.ServerFactoryAdapter.Create(IAppBuilder builder)
   at Microsoft.Owin.Hosting.Engine.HostingEngine.Start(StartContext context)
   at Microsoft.PowerBI.ReportServer.WebHost.Program.Main(String[] args)

2.

2017-12-31 10:11:15.2128|ERROR|5|Error getting next eventSystem.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'TakeEventFromQueue'.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__174_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__24`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__12`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__8`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.CatalogEventAccessor.<TakeEventFromQueue>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 Microsoft.PowerBI.ReportServer.EventHandler.EventProcessingService.<GetNextEvent>d__6.MoveNext()
ClientConnectionId:af6712f3-16f1-4da8-b549-6313979b4a60
Error Number:2812,State:62,Class:16

By the first error I manually removed the URL. But I don't understand the error, why is there and where a another registration?

On the server there was in August the preview power bi ssrs version installed. After a uninstall the GA version was installed by using the same url. Is there an option to check all url reservations?

At the second error I checked that the SP exists and the user has enogh permessions. The SQL Server can only connect via a FQDN and the Port sqlserver.domain.tld,6600

[pbx]

You can manage URL reservations using netsh http commands, for example: netsh http show urlacl shows you all reservations. See the following;

https://msdn.microsoft.com/en-us/library/windows/desktop/cc307236(v=vs.85).aspx

https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/about-url-reservations-and-r...

For the second error, check to make sure both nodes have access to the SQL server on ther aforementioned port, and also check service account permissions, and local firewall logs (if you are using Windows firewall). I have to admit the error is a bit weird. What log file this error shows up in?

[Anonymous]

Thanks for your reply.

I’ve checked via urlacl the registrations. I’ve found 3 registrations which pointed on a wrong url. The other one seems to be correct. I deleted the three wrong registrations. But must be the registrations for the ssrs services pointed to the service account? Currently the registrations are made for a local service account …

    Reserved URL            : https://powerbi.<domain>.org:443/ReportServer/

        User: NT SERVICE\PowerBIReportServer

            Listen: Yes

            Delegate: No

            SDDL: D:(A;;GX;;;S-1-5-80-1730998386-2757299892-37364343-1607169425-3512908663)

Second Error:

Both Server runs under the same service account. If I stop one of the server, I can use the Portal. So I don’t think that the firewall or something else blocks the request.

The Logfile stored under:

"<DRIVE>:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles\RSPowerBI_<DATETIME>.log"

[jtarquino]

Seems to be you are running the service under your virtual service account (NT SERVICE\PowerBIReportServer), double check you use Network service account (it will show up in the logs as NT AUTHORITY\NETWORK SERVICE) or a service account and check that one have permissions in your SQL database, to be sure you can share the logs of the portal.

For example a Virtual Service account will look like

RSPortal!crypto!RSPortal.exe!1!01/02/2018-14:27:01:: i INFO: Initializing crypto as user: NT SERVICE\PowerBIReportServer

A network service will look like

RSPortal!crypto!1!04/27/2017-11:27:31:: i INFO: Initializing crypto as user: NT AUTHORITY\NETWORK SERVICE

[pbx]

Since my response yesterday disappeared in the thin air, I am posting again.

As for NT SERVICE\PowerBIReportServer, this is an internal group that should contain your service account, I beleive the setup process adds that account to this group. The idea might be that you don't need to change URL reservation if you change the service account. You can check memebership of this group on your Windows server to be sure.

For the second error, let's put the database issue aside for now. Given that it works if you stop one server, I suspect it is an issue with your configuration or your Windows NLB. How confident are you in the performance of the Windows NLB? That service is not that smart and some people experience weird issues with it. Have you checked the NLB logs?

As for the configuration, few things would help troubleshooting the issue. Could you please provide the following (you dont need to provide actual values for URLs, consistently masked ones should be fine)?

1. URL reservations ins rsrepotserver.config file
   
2. SecureConnectionLevel setting in the above file.
3. Value of the following after you restart the PBIRS service:
   1. In the RSPowerBI_YYYY_MM_DD_HH_MI_SS.log, there is one or more lines like this: “|INFO|1|CFG : Hosting-url-ReportServerWebApp = [a url here] (env)”.
   2. In the RSPortal_YYYY_MM_DD_HH_MI_SS.log, there is one or more lines like this: “INFO: Web app url: url binding”.
   3. In the ReportingServicesService_YYYY_MM_DD_HH_MI_SS.log, there are three lines like this:
      1. INFO: Using report server internal url <a url here>.
      2. INFO: Using report server external url <a url here>.
      3. INFO: Using url root <a url here>.

[Anonymous]

Yes, there was a night with many alcohol 😊

After the removal of the three url registrations, I haven’t found the first error.

I already contact the NLB admin (external)  to check the logs, but I haven’t now a response.   

Do you have any best practices for the BLB or necessary configurations/settings?

1. URL reservations ins rsrepotserver.config file
1. <UrlString>https://powerbi.<domain>.org:443</UrlString>
2. SecureConnectionLevel setting in the above file.
1. <Add Key="SecureConnectionLevel" Value="0"/>
3. Value of the following after you restart the PBIRS service:
1. In the RSPowerBI_YYYY_MM_DD_HH_MI_SS.log, there is one or more lines like this: “|INFO|1|CFG : Hosting-url-ReportServerWebApp = [a url here] (env)”.
1. I’ve got 3 files after restarting the service,
   2018-01-02 23:14:51.9064|INFO|1|CFG : Hosting-url-ReportServerWebApp = [https://powerbi.<domain>.org/Reports] (env)
   2018-01-02 23:14:51.9689|INFO|1|CFG : Hosting-url-ReportServerWebApp = [https://powerbi.<domain>.org/Reports] (env)

The values in the three files are the same. There are no errors.

1. In the RSPortal_YYYY_MM_DD_HH_MI_SS.log, there is one or more lines like this: “INFO: Web app url: url binding”.

Here I got 4 files after the restart
RSPortal!reportserverwebapp!RSPortal.exe!1!01/02/2018-23:14:53:: i INFO: Web app url: https://powerbi.<domain>.org:443/Reports/

The values in the four files are the same. There are no errors.

1. In the ReportingServicesService_YYYY_MM_DD_HH_MI_SS.log, there are three lines like this:
   
   Here I got 3 files with after the restart.
1. INFO: Using report server internal url <a url here>.
2. INFO: Using report server external url <a url here>.
3. INFO: Using url root <a url here>.

configmanager!DefaultDomain!b6c!01/02/2018-23:14:52:: i INFO: Using report server internal url https://powerbi.<domain>.org:443/ReportServer.

configmanager!DefaultDomain!b6c!01/02/2018-23:14:52:: i INFO: Using report server external url https://powerbi.<domain>.org:443/ReportServer.

configmanager!DefaultDomain!b6c!01/02/2018-23:14:52:: i INFO: Using url root https://powerbi.<domain>.org/ReportServer.

The URL’s seems to be correct. But I’m confused about the count of the logfles. The two errors from the second posting doesn’t appear anymore. But the issue to deploy a report on the server already exists.

I checked again the Windows Event Log – Application Section. There I got everytime after a restart following error – 3 Events, corresponding with the amount of logfiles:

The description for Event ID 25 from source MSOLAP$PBIRS cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

msmdsrvi.rll

1031

Source: MSOLAP$PBIRS

Event Id: 25

Level: Error

I don’t think that a reinstall fixe this error. The report server was already reinstalled after a Kerberos issue and this error was at this time in the log too. I’ve not noticed that the error is logged since the reinstall (4 weeks) every time by a restart or start from the servers (the error is logged on both server.

I clicked a little bit in the portal on some reports ad folders. I want open the context menu of a report, I’ve got following error (RsPortal…log) . next try it works (the user reported this issue 4 or 5 times …) – the error seems to be a connection problem with the sqlserver:

RSPortal!reportserverwebapp!RSPortal.exe!3!01/02/2018-23:41:51:: e ERROR: OData exception occurred: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: Fehler in der Berichtsserver-Datenbank. Dies kann auf einen Verbindungsfehler, ein Timeout oder wenig Speicherplatz innerhalb der Datenbank zurückzuführen sein. ---> System.Data.SqlClient.SqlException: Invalid object name 'ExtendedCatalog'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader()

   at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()

   --- End of inner exception stack trace ---

   at Microsoft.ReportingServices.Library.Storage.WrapAndThrowKnownExceptionTypes(Exception e)

   at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()

   at Microsoft.ReportingServices.Library.DBInterface.CatalogObjectExists(CatalogItemPath objectName, ItemType& type, Guid& id, Int32& snapshotLimit, Byte[]& secDesc, Int32& execOptions, Guid& snapshotId, Guid& linkID)

   at Microsoft.ReportingServices.Library.DBInterface.ObjectExists(ExternalItemPath objectName, ItemType& type, Guid& id, Int32& snapshotLimit, Byte[]& secDesc, Int32& execOptions, Guid& snapshotId, Guid& linkID)

   at Microsoft.ReportingServices.Library.DBInterface.ObjectExists(ExternalItemPath objectName, ItemType& type)

   at Microsoft.ReportingServices.Portal.Repositories.CatalogItemRepository.ThrowIfRestrictedItemType(RSService rsService, String path)

   at Microsoft.ReportingServices.Portal.Rep. | RequestID = s_f3559e81-6afe-4ea4-ae88-18e096d83eb2

[pbx]

You can ignore the "Event ID 25 from source MSOLAP$PBIRS" errors. I get them as well, has something to do with the local SSAS instance of PBIRS and I wouldn't worry about it.

At this point your issue seems to be database issue. Given you are using Always On AG, you may want to make sure your database and connectivty to that is good from both nodes. Please see the "Report Server Databases and Availability Groups" section in the following link:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-...

Having said that, even after this you will run into issues with the current configuration. But I will save it for later, when you past this database issue 🙂

[Anonymous]

@jtarquino

Thanks for reply.

This is from the log:

RSPortal!crypto!RSPortal.exe!1!01/03/2018-16:25:34:: i INFO: Initializing crypto as user: <domain>\<domainuser>

From the rsreportserver.config:
<AccountName>NT SERVICE\PowerBIReportServer</AccountName>

The user above is on both server member of the local administrators group. The user have also on the Report Server database the permissions which granted via the “RsExecRole” role.

@pbx

Thanks again!! Do you know why the error in the event log shows up three times and why I got more than one logfile after a restart? I don’t get the error on the event log at standalone installations. I got only the error on the installation with horizontal scaling. On which installation kind do you get these error?

From the web link:

“Manual actions or custom automation scripts need to be used to complete the failover and recovery.”

Does it mean that the report server instances should be restarted? So should I better remove the databases from the HA group?

[jtarquino]

You might have to start to investigate why the PBIRS Server isn't able to connect consistently with the database useing the <domain>\<domainuser> user, you can use a combination of the following

SQL Profiler in the database server to ensure is connecting with the right user and the connection is coming from both of your scale out servers.

Also the SQL login failed traces to see if is failing in the security handshake.

If nothing is showing up in the SQL logs you might have to investigate the connectivity fromo the PBIRS server to the database.

Regards

-Jaime

[Anonymous]

@pbx

It is possible that the initial setup was the German. But after the reinstall I absolutely sure that I used the English setup binary. But I can imagine that the uninstall does not remove all resources, Do you have some recommendations in the case of the HA group? Should I remove the database from the group or should I implement into the run book in case of a move of the database that the report server instances restarted?

@jtarquino

Do you really think there is a problem with the user? The most time the server works correctly and everything works fine. Sometimes I can’t deploy a report on the server via Power BI Desktop and sometimes I get curios errors (can’t connect to the database …)

So, I can’t imagine that the report server will hae problems with the user account. It is also very difficult to use the profiler because the database server hosting several production databases and the error is not always reproducible. At least the profiler cost to much performance for the other databases.

 But I tell the database team to check the ErrorLog and to monitor the firewall and the connectivity.

[pbx]

@Anonymous

So I checked. I only get error on scale-out. It's weird, becuase it's looking for particular locale (which is not a suprising locale, it's actually where we are located physically), but both Windows region and PBIRS are US-EN, so I have no idea where it's getting the idea about the locale.

Regarding database: I recommend you remove the RS databases from the AG and give it a try, at least you have removed one possible point of failure.

Regarding the log files:

The following are expected:

1. ReportingServicesService_YYYY...
2. RSPortal_YYYY...
3. RSHostingServiceYYYY
4. RSOffice_YYYY or RSOfficeYYYY
5. ReportingServicesWMI_YYYY
6. RSPowerBI_YYYY
7. and finally RSPowerBIYYYY_

The RSPowerBIYYYY is generated every hour, and the #6 is generated when service starts and once in a while. I think there is a bug somewhere in configuration and two different file name convention is used for logging PowerBI events, one has _ before YYYY, one doesn't. I'm sure if we look at configuration files, we can spot that.

[Anonymous]

I will try it to remove the database from the HA Goup. Did you also get 3 Errors in the EventLog or only one? Hopefully see this error a guy from Microsoft and fixed it in the next release 😉

[Anonymous]

One question more to the HA Group scenario.

On this website (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-...) I read this:

Update the database connection to use the Lister DNS Name. for native mode report servers, change the Report Server Database Name in Reporting Services configuration manager.

Why should I change the database name? The name on each node is the same?!

[pbx]

I guess that means if you want to change it, change it there.

[pbx]

I only get to Event ID 25 errors in the event log. I have two servers in the scale-out, maybe that's why.

[Anonymous]

Î have two server, too. But three errors ...

[Anonymous]

@pbx@jtarquino

Now I removed the reoport server database from the ha group. The server runs stable and the logs are fine.

But if I pubish a report via the dektop, it gaves me 4 of 5 times a a error with no description. The Error is only on client side.

If I shutdown one report server, everything seems to be working. So I think there is a error or misconfiguration with the NLB.

The publishing via the upload feature from the webinterface works everytime.

Does anyone give me some hints what the IT can check? Or are there any best practice?

[pbx]

You may want to add an entry in the host file of each server that points the virual host name to the IP of that server.

[Anonymous]

I've tried it, but this have no impact.