Random Procrastination

Darren Gosbell [MVP]

Month: November 2021

Power BI Report Server – an unexpected error occurred (Nov 2021)

Earlier this week we started getting reports of the following error on our production Power BI Report Server (PBIRS) instance for any reports that were using a live connection back to one of our Analysis Services models.

All it basically said was “an unexpected error occurred. Please try again later”. And if you looked at the details it showed the message above saying that “We could not connect to the Analysis Services server”.

Some other fun facts about this issue are:

  • It was not affecting all users (at least in our environment)
  • There had been no recent updates or configuration changes on PBIRS, SQL or SSAS
  • The same issue appeared to be affecting our test environment but not our development environment.
  • It was happening for both pbix and paginated reports
  • It was affecting reports that sourced data from SQL Server as well as SSAS
  • When running a profiler trace against SSAS listening for the session events I could see some sessions with usernames and others where the user was listed as “ANONYMOUS USER”

Putting all of the above evidence together point to some sort of Kerberos issue. In our environment our SQL and SSAS instances are on different servers to our PBIRS instance we had to configure Kerberos with constrained delegation to allow for the user credentials to flow from client > to PBIRS > to SSAS.

Kerberos can be a little fiddly to setup initially, but once it’s configured I’ve never had an issue with it until the one this week. When we initially configured Kerberos I had distilled the generic instructions from the Microsoft documentation down into a series of specific steps for our production environment listing the specific setspn commands that needed to be run and how the constrained delegation settings needed to be configured and saved these in a document. This made it very easy to cross check that all these configurations were still correct.

At this point we knew that the Kerberos configuration appeared to be unchanged, but we still had an issue where it worked for some users and not for others.

So the next thing I started doing was to get some of our users to run the klist command from a command prompt. This command lists all the Kerberos tickets that are cached on the current machine. This lead us to find a pattern where everyone that was getting the error were getting their kerberos tickets from a specific list of domain controllers while people that did not have the issue were getting theirs from a different list of domain controllers. Our IT admins then noticed that all the failing servers were in a group which had had security patches applied earlier that day.

The specific patch that had been applied was KB5007206 and after raising a support ticket with Microsoft they were able to point us to the following hotfix (KB5008602) for this update which fixed an issue when you were using Kerberos authentication with constrained delegation and protocol transition (which is exactly how Kerberos needs to be configured when using PBIRS or SSRS or SSRS in Sharepoint integrated mode for people with on-prem Sharepoint environments).

Hopefully this post will help anyone else that runs into this issue.

DAX Studio 2.17.2 released

DAX Studio 2.17.2 has just been released and the following is a run down of the new features and fixes that you will find in this release.

Apologies to anyone who attempted to download the 2.17.0 release – there was an update to the code signing tool and it failed to correctly sign the installer. The automated build has now been fixed so that it correctly signs the installer again.

New Features

Added Parameter support to Query Builder filters

The Query Builder now supports parameters. Typing an @ as the first character in a query builder filter value will switch the value into parameter mode and the following text will be treated as the parameter name. (typing @ a second time or clicking on the purple @ button will toggle the parameter back to the standard value mode)

When you run the query builder where a filter has a parameter you will be prompted for the parameter value just as if you had typed out a query that included a parameter reference. This allows you to build a query with parameters that can be pasted into a paginated report or any other tool that supports DAX parameters.

Running Query Builder with a filter parameter

Removing <pii> tags from xmSQL in Server Timings to improve readability

Power BI and AzureAS have added <pii> tags around some of the text in the query events that DAX Studio captures as part of the Server Timings. These tags allow Microsoft to strip out information that could potentially be personally identifiable when writing them to centralized logs. But because DAX Studio does not send any of this information off your machine and because this information also includes important contextual information for troubleshooting and performance tuning we are just removing these tags to reduce the noise and improve readability.

Fixing accuracy of Server Timings for queries with Batch events

Prior to this update Storage Engine batch events timings were not being added to the SE duration figures. For a lot of models this difference is insignificant, but for large queries against large models this can result in an incorrect inflation of the FE duration. Keep an eye on the posts from Marco and Alberto at sqlbi.com as they are working on an in-depth video to explain this improvement to the Server Timings.

Query Builder queries are now captured in the query history

Prior to this update the Query History pane was only capturing the text of queries that had been typed into the query editor. Now the Query History pane captures any query that was executed regardless if it was from the edit window or from the Query Builder.

There is now an icon next to each query history row indicating where it was captured from and if you double click on a query that was originally run in the Query Builder it will be restored back into the Query Builder window

The /f parameter can now open vpax files

Now when you run DAX Studio from the command prompt the /f parameter will open either .dax or .vpax files.

Added extra information to the error message when the XMLA endpoint is not correctly enabled

If the Power BI XMLA endpoint is not properly enabled the Microsoft ADOMD provider returns a very generic error message making it hard to know what has gone wrong. DAX Studio now adds some extra text after the Microsoft error if you are attempting to connect to an XMLA endpoint and hit this generic WebException error to suggest you check the XMLA endpoint settings in your tenant.

Improved caching of measure expressions

Prior to this update features that required access to the text of the multiple measure expression such as the “Define All Measures” feature would request the expression text one at a time, now all of the measure expressions are retrieved in a single request.

Fixes

  • Fixed #703 preventing a crash when entering a proxy password
  • Fixed #715 added DOUBLE data type to intellisense and syntax highlighting
  • Fixed #740 export to Excel failed on some machines
  • Fixed #755 saving parameters to XML was not correctly encoding special characters
  • Fixed #750 would crash on startup if the WMI information on the machine was corrupt
  • Fixed download link in Help – About screen