Not long ago, something rather stupid happened to me while running a few tests on our Power BI on premise server. I was playing around with permissions and I deleted my own view permission from a single report. I still had management permissions, but how to bring back the report? I’m a PowerShell addict and so my first idea was that somewhere in the SqlServer module there must be something. Spoiler alert: there isn’t.
Basically the only way to connect to the server is via WebServiceProxy. Your connection string might look a bit different, but roughly it will be something like this:
$rsProxy = New-WebServiceProxy -Uri "https://powerbi.mycompany.com/web/ReportService2010.asmx?wsdl" -UseDefaultCredential
Now you might be like “Oh wait, what? ReportService2010, isn’t there something newer?” and trust me, I’ve been there. The short answer is no, there isn’t. This class does wonders when it comes to Paginated Reports, but you will see that there are certain limitations for Power BI Reports. To me it feels like Microsoft has lost interest in the on premise report server. But is it really that bad? Well actually no, because after all, there are still a lot of cool things you can do.
The full documentation can be found here: ReportingService2010 Class
One of your best friends will be ListChildren, because no matter what you do, you will need paths a lot. In my opinion this is the part that makes doing complex things so clumsy. If you access an SSAS instance via the SqlServer module, you will be able to browse through the objects naturally, because the return values are usable objects again. With the proxy it often boils down to calling the ListChildren method and then using the output to call another method using the paths you get as return value.
$rsProxy.ListChildren("/myfolder",$false)
The $true / $false indicates whether recursion will be used or not. A word of warning: this can take some time on servers with a lot of items. Let’s follow up with an example of what I mean by: you’re always calling multiple times. Now we don’t want the names only, but we also want the policies associated with these items. The method we need is GetPolicies and you guessed it, it wants a path as parameter.
$rsProxy.ListChildren("/myfolder",$false) | Select-Object Path, @{ label="Policies";expression={$rsProxy.GetPolicies($_.path,[ref]$false)} }
So instead of simply using the object, you have to call the GetPolicies method using the path as input. It’s not bad, but it’s far from perfect.
Oh and did I get back my read permission? Yes I did, simply by resetting the report’s permissions to inherit from parent:
$rsProxy.InheritParentSecurity("/myfolder/myreport");
Final thoughts
Basically you can do a lot of nice things with that class and the huge upside is, that you don’t need any form of administrator access or database access. It’s possible to automate different actions on your report server, such as setting permissions, which can come in handy.
But I’m not going to hide that there are downsides. Direct access to the database of a Power BI server yields much better results when trying to gain insights about your server. And let’s get back to the 2010 in the class name. Back then, nobody was thinking about Power BI and that’s a huge problem when trying to work with Power BI Reports. There are quite a few methods that allow you to discover more about a Paginated Report, like connections or even report content and honestly, it would’ve been great to see an update of this class.
Ultimately, though, I like versatility and even if it’s not perfect, it’s an option that will help you in some scenarios.