Showing posts with label Automated reporting. Show all posts
Showing posts with label Automated reporting. Show all posts

The slow death of SSRS and future of "PBIRS"

Microsoft has announced the inevitable- SSRS is not long for the world.


SSRS is now PBIRS and sadly, after 2033, PBIRS is likely going away in favor of costly, subscription-based MSFT reporting


There will apparently be no new features for RDL-based paginated reports moving forward unless MSFT changes course. All MSFT investments in data visualization and business intelligence are going to Power BI and other "Power Automate" tools.


For over 20 years, SSRS and RDL-based paginated reports have served a vital role supporting business intelligence and data visualization


This is not entirely a bad thing but I do have some thoughts on the sad reality that is the current state of Power BI, namely- its massive feature and extensibility limitations with respect to its predecessor, SSRS.

The first big one is report access. SSRS reports were available to anyone in the enterprise. And with custom authentication like extRSAuth, reports could be made available to any logged-in SaaS customer or otherwise authenticated client- for free. Most everything in Power BI requires a monthly subscription.


Note that the "Free" pricing tier has significant limitations (not very useful to develop reports in isolation...)


The second big one is scheduled reports and report archiving for auditing and comparison purposes via report snapshot functionality. Scheduled reports and snapshots are made possible via the PBI Report Server on-prem engine, but as the PBIRS on-prem engine is only supported through January of 2033 the question for organizations considering continuing with RDL-based schedulable and snapshottable reports is- "will we be cut off (unsupported) after January 2033?".

Sadly, everything is moving to a subscription-based model and this has everything to do with short-term "value demonstration" and the prioritization of short-term profits over long-term product excellence and long-term customer satisfaction. (more on this in a future post...).


Not an ideal way to present an external customer-facing report...

If I had to make a bet on the future, based on MSFT's current avaricious and short-sighted, "Subscription-and-AI-everything-customer-opinion-and-developer's-be-damned*" business direction- SSRS and its successor PBIRS will be completely phased out after 2033 and the only option will be Power BI reports, with Power BI offering some expensive, newfangled version of schedulable .pbix reports. Probably called something ridiculous like "Fabric Delivery Service". I doubt they will continue to support snapshot auditing capabilities, but they should.

Lastly, a sad consequence of the universal move away from code sharing, "actually free/unlimited use" software and software extensibility in general, is that Power BI binary files (.pbix) cannot be utilized outside of being decompiled by a Power BI editor. In the heydays of SSRS, you could view any .rdl, .rsd or .rds file and immediately inspect a report, dataset or data source (XML) file and make edits on the fly through a text editor or an API. This is no longer possible.

There have been innumerable SSRS scripts implemented at organizations to update large batches of data sources, datasets and reports to point to new data sources, data fields or to make mass updates to column text, report labeling or table definitions. So the removal of this functionality is kind of a big (raw) deal.


Paginated RS Reports (.rdl)  

RDL report files are XML-based and report definitions are clearly defined in the files; fortunately PBIRS is supporting paginated reports and the PBI Report Server database and engine for several more years.


Interactive PBI Visualizations (.pbix)


PBI report files are illegible compiled binary code; report definitions can only be decompiled, viewed and modified through tools like the online Power BI editor or downloadable Power BI Desktop programs. PBI interactive reports are the MSFT-recommended approach for all data reporting (except apparently, their own as is demonstrated below in the example of a SSMS 21/SQL Server 2025 standard, RDL-based database report).


There is a long way to go before SSRS reports disappear from the MSBI landscape completely however; SSRS will continue to be supported until January 11, 2033 according to Microsoft's recently posted Reporting Services consolidation FAQ.

In fact, if you look at Microsoft products like SSMS 21, you'll see regular old SSRS/RDL reports being rendered through the Report Viewer control when running reports for SSIS execution history, Job execution history, Database Standard Reports and many other SSMS reports.


Right-click any SQL Server database and you can view a suite of useful (if outdated looking) Standard Reports 




Ngl these reports look very 2008ish (3D is not recommended for serious dataviz)- but they are indeed SSRS/RDL


Comparing search terms via Google Trends it is clear that the push to Power BI accelerated in 2017 and has ramped up to the point where general interest in SSRS is negligent compared to interest in Power BI, despite SSRS offering robust, extensible on-prem report solutions for free and Power BI be buggy, considerably "locked down" and charging a relatively expensive monthly per-user subscription.


Google search interest in Power BI eclipsed SSRS in 2017 and has only risen since


But this is merely how marketing-sales-product singularity and product obsolescence works. If Galactus (MSFT) deems it so, it'll be so.


😕

😞



*we have come a long, long way from then-Microsoft CEO Steve Ballmer's infamous but earnest, "Developers!!, Developers!!, Developers!!" rant at a Windows conference in 2006. 😐


References:

https://www.mssqltips.com/sqlservertip/6867/ssrs-vs-power-bi

https://learn.microsoft.com/en-us/sql/reporting-services/reporting-services-consolidation-faq?view=sql-server-ver16


extRSAuth for Custom SSRS Authentication (works w/newest SSRS version 16.0.8)



Fortunately, ExtRSAuth code doesn't require any updates to work with MSSQL SSRS 2022


ExtRSAuth for custom SSRS security 

This assembly, forked from the Microsoft Custom Security Sample extends and improves custom authentication to allow for mechanisms other than user/pwd credential check and to offer a seamless pass-thru of the Login page if something present in the HttpRequest verifies that user is already authenticated. For instance, the user already has an app token from an app that communicates with the report server, and you require the communications with the report server to not involve any intermediate screen or login UI. The user just wants to auth as fast as possible and get to their report, right?


What does ExtRSAuth do to authenticate SSRS user connections?

For direct URL report server access, the default here is to allow local connections, which grants Admin rights for any local requests. If the SSRS request is external, a fallback option accepts an AES 128-bit encrypted querystring from the calling app, and the application, if decryption works, is authenticated and allowed to communicate using a read-only SSRS user; any exception thrown indicates the request is neither from a local connection nor a secure request from the external app.


ExtRSAuth gives SSRS environments the freedom from MS Active Directory that they deserve



To secure the built-in SSRS REST API v2.0 access, you can simply customize the LogonUser() and VerifyPassword() methods in AutneticationExtension.cs and AuthenticationUtilities.cs, respectively:


If you don't perform a security check here, the SSRS REST API will be open to anyone who knows the SSRS admin username


As you can see, this is but one of many approaches we can take with ExtRSAuth in an SSRS-connected application or business environment. Any type and granularity of custom authentication and level of authorization is possible. The only ingredient needed is a .NET developer or developers willing to customize a pretty basic .NET security model.

Real-world applications

This Custom Auth assembly has been tested with (1) several .NET Framework 4.8 and .NET 5, 6, 7 and 8 web and mobile applications, (2) with the SSRS API and all its operations, (3) with the SSRS /ReportServer and the /Reports management web interface as well as (4) Visual Studio 2022 Reporting Services projects (report designers can seamlessly deploy Report Server projects from VS to the Report Server with ExtRSAuth).


After running InitalizeExtRSAuth.ps1, a successful installation will output the above


Demonstration

This YouTube explainer video describes the SSRS external user authentication problem that ExtRSAuth addresses.

Requirements
This plug-in relies on SSRS (2016 or later), and a report server configuration as described in Microsoft's Reporting Services Custom Security Sample

-Replace [your_sym_encr_key] with your symmetric encryption key. Clients can encrypt SSRS URL access querystring with Sonrai.ExtRSAuth.Excryption.Encrypt() or a similar 128-bit AES encryption implementation, or modify Encrypt() with any encryption algorithm and key and block sizes.

Clone it, customize it further (or not) and get started today: https://github.com/sonrai-LLC/ExtRSAuth