An SSRS IFrame/CORS infinite redirect loop error and a quick and easy solution

The redirect loop looks like this and, in Edge, will display the error message: "[domain] redirected you too many times"

If you are trying to render the SSRS ReportViewer control within an <iframe>, you may run into a CORS issue that manifests in a series of 302 (Found) responses and an infinite redirect loop between ReportViewer control (ReportViewer.aspx) and Logon.aspx.

As of SSRS 2022, without an explicit instruction to allow CORS, ReportViewer cannot be rendered within an <iframe> on an origin different than the origin of the report server.

If you are using custom authentication, the solution is easy enough. Just add cookieSameSite="None" and enableCrossAppRedirects="true" to the authentication <forms> tag in the report server's web.config.

 <authentication mode="Forms">  
   <forms loginUrl="logon.aspx" name="sqlAuthCookie" cookieSameSite="None" timeout="60" path="/" enableCrossAppRedirects="true" requireSSL="true">  
   </forms>   
 </authentication>  

You may also need to enable CORS in your client app. In ASP.NET Core 8, this can be achieved through the following in your application startup code:
 app.UseCors(builder => builder  
 .WithOrigins("https://localhost", "https://[domain]")  
 .AllowAnyMethod()  
 .AllowAnyHeader());  

XML vs. JSON for API requests and responses

XML is a document-centric data format and data processing language and stands for, "Extensible Markup Language".

JSON is a lighter-weight messaging-centric data format and stands for, "JavaScript Object Notation".

A question that often comes up in discussing APIs is whether an endpoint should accept and return JSON (increasingly the standard) instead of XML (was the old standard with XML-based SOAP services). If I have just 1 opinion to give, I would say that it is important for an API response model to be format agnostic (at least in regards to JSON vs. XML). Why not have the API accept and return both types and give clients the choice of either JSON or XML?

If your web service is so complex and has so much XML-specific dependencies, you may want to look into simplifying your model. None of the heavy lifting (of database communication, of data configuration and security information, data processing, etc.) should ever be done with dependencies from our sent and received XML and JSON GETs and POSTs.

That should be done on the server side, as much as is possible.


Here is an illustration from xml.com which describes the functionality equivalents in each format


In reality, if we model our program entities with easily understandable properties and no embedded config in the XML (this was the norm for SOAP), we will have no problem serializing our model objects into XML or JSON- losing no information in the process.


"XML is a data format, AND it is a language also. It has many powerful features that make it much more than a simple data format for data interchange. e.g., XPath, attributes and namespaces, XML schema and XSLT, etc. All these features have been the main reasons behind XML popularity.

JSON’s purpose is solely structured data interchange. It serves this purpose by directly representing objects, arrays, numbers, strings, and booleans. When meta-data and document markup is not a requirement, always use JSON." 
 -Lokesh Gupta


JSON vs. XML: A simple example

 {   
  "json": {   
   "myAppSettings": {   
   "appSettingDatabaseUri": "https://mysecuredatabaseserver.net"   
   }   
  }   
 }   
 <xml>  
 <myAppSettings>  
 <appSettingDatabaseUri>https://mysecuredatabaseserver.net</appSettingDatabaseUri>  
 </myAppSettings>  
 </xml>


It is a matter of preference for developers: For some, XML reads like a book and XML element structure and hierarchy makes for more easily understood data messages. For others, XML is overload, JSON is far more lightweight (ie. faster), and JSON is more readable (esp. for anyone knee-deep in NodeJS development where JSON is the default format for everything). JSON is also inherently parse-able with JavaScript. 

With XML it is not so. And XML's additional concerns (document media type mixing, security, channel bindings (remember WCF? 😅)), looping and various other other namespace and xsl/xslt configurations and transformations)- make it seem far more unwieldly than it actually is.

Aim for offering your API clients the option of JSON or XML for response type via "Accept: application/xml" or "Accept: application/json" header requests. Happy Parsing!

References: 

https://restfulapi.net/json-vs-xml

https://www.guru99.com/json-vs-xml-difference.html


History of XML

  • XML was also derived from SGML.
  • Version 1.0 of XML was released in February 1998.
  • Jan 2001:IETF Proposed Standard: XML Media Types
  • XML is the Extensible Markup Language.
  • 1970: Charles Goldfarb, Ed Mosher, and Ray Lorie invented GML
  • The development of XML started in the year 1996 at Sun Microsystem

History of JSON

  • Douglas Crockford specified the JSON format in the early 2000s.
  • The official website was launched in 2002.
  • In December 2005, Yahoo! starts offering some of its web services in JSON.
  • JSON became an ECMA international standard in 2013.
  • The most updated JSON format standard was published in 2017.

Power BI and SSRS - A complimentary symbiosis

 

"Generally, Power BI paginated reports (SSRS reports) are optimized for printing, or PDF generation. Power BI reports are optimized for exploration and interactivity."

 

So, what really IS up with MSBI these days? Is SSRS getting shuttered? PBI has paginated RS-like reports, but not a lot of the other features SSRS provides. Microsoft marketing will continue to hate people like me who would go to great lengths to help keep alive an aging reporting technology. But the thing is- SSRS simply does the job for the vast majority of reporting use cases. In the last 18-20 years, there have been no major advancements in scheduling and snapshotting and otherwise caching and managing and distributing electronic information. And SSRS has all of that built in.


Similar BI products- but they'll both be around for a while, with PBI eventually subsuming all the most useful SSRS tech


Nearly all of the advancements in reporting technology have come on the presentation and client-side. We can now create beautiful ad-hoc analysis and brilliantly composed interactive charts and other data presentations. But this all comes with a not insignificant price ($10 per user/month). And beyond the price, much like Azure SQL (vs. a genuine "Microsoft SQL Server" VM) and the extremely limited Azure SQL Workbench (vs. SSMS), there is a lot that Power BI cannot do well or at all.

You may have noticed the built-in SSRS reports in SSMS19's new QueryStore feature. These are very useful reports that can give DBAs an idea of how queries and being processed and what processes are consuming the most CPU. And it is a good example of a company "eating its own dog food".

I've seen SSRS installations that contained thousands of reports representing trillions of dollars of value, categorized and summarized with realtime security ownership, counterparty, price and other core trade data. Several of these business-ctitical reports had scheduled delivery and were cached and snapshotted programmatically.

Little old SSRS is a quiet but reliable business data spartan. To my surprise it is actually quite popular in the investment banking industries where stock valuations and company summaries on reports are a big part of the lifeblood that drives investment banking decision making.


A professionally developed PBI report looks and behaves more like an interactive BI dashboard- this is a good example of a good PBI report


And with a little bit of customization magic via things like ExtRSAuth, ExtRSNET48, ExtRS and other RS extension tools, SSRS and Power BI can be tailored-made to serve as a uniquely effective symbioses of print formatted, scheduled and data-driven management reports (SSRS) and ad-hoc or OLAP-based interactive data analysis charts with data visualizations (Power BI).

To answer the question of "when will SSRS be end of life?", I would say that SSRS isn't going away anytime soon. Microsoft has decided to combine SSRS and PBI (RS, .rdl reports are the "Paginated Reports" in PBI) in a way that serves both platforms. The PBI3.0 REST API indicates as much as the combined SSRS/PBI API offers a plethora of functionality that .NET developers can use to get the best of both worlds (SSRS and Power BI) and customize RS and/or PBI dashboards to support unique business processes. 


A print-formatted SSRS report- great for standardized, templated data reporting


The choice in what tool or tools you will enrich your printed reports and data visualizations with, is yours. Keep in mind that many organizations make use of both- with SSRS getting equal to more attention than PBI even to this day- not only because of the huge, global SSRS install base with all of these currently running SSRS reports- including many which support critical business and governmental processes across the globe. But also because Power BI requires a monthly subscription fee :( . Freeware seems to be slowly dying. Let's hope things change with the next version of SQL Server and maybe we'll get free* PBI. 

SQL Server 2025? Happy reporting and data analyzing. Always remember that PBI and SSRS serve different organizational needs- ad-hoc analyzation of data and pixel-perfect, professional, print-ready reports, respectively.


*(at least a free "tier"? I mean c'mon MSFT..... developers want to CREATE, and MSBI data visualization creativity is dying behind that paywall)- SSRS and PBI should be free and work hand in glove. Anything less is a mistake and a gigantic missed opportunity, imho.


Reference: https://learn.microsoft.com/en-us/power-bi/guidance/migrate-ssrs-reports-to-power-bi

Dichotomies

Apropos of nothing I thought about some interesting dichotomies and wanted to share my perspective. It is interesting how often there are effectively 2 sides to a coin.


Religious Faith/Religious Dogma - Some folks live the "spirit" of the Word and resist structure vs. others, who follow strict adherence to "the letter" of the Word.

Subjective/Objective - You have here opinion and feeling (the supernatural) vs. facts and laws of nature.

Romantic/Classical - similar to the Subjective/Objective dichotomy (see also, "Zen and the Art of Motorcycle Maintenance").

Liberal/Conservative - The ideology of inclusion and change vs. the ideology of exclusion and stasis.

Conglomerate/Individual Co. - Whereas many corporations sought to obtain economies of scale through M&A in 80s, 90s and 00s, General Electric proved- (sold GE Capital, spun off GE Healthcare, GE Aviation and GE Power into 3 individual new companies)- that the conglomerate model doesn't always stand the test of time.

Thin client/Fat client - It makes no sense to be all one or all the other (an overburdened SPA app or an inflexible server-side only app). But we keep moving between one (thin client terminals in the 70s and 80s) and the other (fat client Home PCs in the 90s and 00s). And we've moved back to thin client again with Azure, AWS and GPC and the omnipresence of SaaS. But at the same time, fat-client SPA apps are as popular as ever... I guess we have fat client UIs and thin client APIs.

Imperative/Declarative code - Instructions that read like a book vs. instructions that read like a mathematical proof.

Monolithic app/Microservices - a very heavy Swiss-Army knife vs. a bunch of lightweight kitchen knives.

Software/Hardware - Recipes vs. the raw food itself.

Socialism/Capitalism - The idea that everyone is the same and should be reduced to (or propped up by) exactly such vs. the idea that everyone is incompatibly unique and should be uniquely catered to and provided maximum personal freedoms that can- like assault weapons and ammunition- come at the expense of the greater community.

Urban/Rural - Those from the densely populated cities and suburbs vs. those from the sparsely populated country towns.

Introvert/Extrovert - He or she who expends a great deal of energy socializing and finds solace in silence vs. he or she who is energized by interpersonal social connection and is not comfortable alone for extended periods of time.

Centralization/Decentralization- Putting the heart of a system at the center with dependent, often necessarily-generic/homogenized nodes vs. putting the heart on the nodes themselves, at the expense of (losing) ease of simultaneous node (state) synchronization and a "single source of truth/golden records".



Charts Suggestions - "A Chart Chooser" (edited)

Just because you can do things with "non-data ink"* does not mean you should do things with "non data ink". Below is a useful guide to charts for presenting different perspectives of data. I have crossed out the ones that are unanimously decried in the data visualization community for having confusing elements that do not effectively communicate the meaning of data.

Bar charts. Line charts. Dot or scatter plots. And bullet charts for really communicating a lot of information in a small space. That's really all you need.

 

The "Thought-Starter" above, edited to cross out (in red) all useless and confusing visuals to AVOID


Bullet charts are highly effective at displaying sub-ranges within a spectrum; the sole max line communicates threshold or "target" values



Focus on ensuring that every piece of ink in your chart is conveying some kind of useful information. If not, delete it. Blank space is better than distracting ink.

If you need an example of "distracting, non-data ink" then look no further than the following almost headache-inducing example:

I imagine the author of this chart was more interested in the art than the meaning of the data; this is a really bad data visualization



Another simple yet very powerful data visualization technique is to show the same type of chart repeated for contiguous time intervals or for different groups at the same point in time. An example is the following small multiples chart on alcohol consumption in different countries:


Small multiples charts really highlight the outliers (S. Korea?!!)


For more on the use of small multiples for effective data visualizations, I show more examples here: kpitsimpl: Small Multiples (are awesome) a while back. KEEP IT SIMPL.





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