• Eitan Blumin

Overriding the Authentication in SSRS for External Access

Updated: Apr 21

The last version of SQL Server Reporting Services (SSRS), which was a simple web portal hosted on the local IIS, was version 2005. Since SQL Server 2008, the SSRS was implemented as an independent, self-hosted, web service. This change also meant that SSRS was managing its own authentication mechanism, instead of authenticating via the standard IIS. And that, in turn, meant that it became more complicated to implement client-facing SSRS reports... Unless you didn't mind your clients getting that annoying authentication prompt in their browser.


In this post, I hope to summarize the various methods that we have, in order to get rid of that annoying authentication prompt. Each method has its own advantages and disadvantages in terms of complexity of implementation, versatility, and the level of security that it provides. More specifically: the more secure and versatile a method is - the more complicated it is to implement.

The various methods to override the SSRS authentication mechanism are (in descending order based on their complexity):

  1. SOAP Web Service of SSRS

  2. ReportViewer Controller for ASP.NET and ASP.NET Core

  3. Developing and installing a Custom Security Extension

  4. Enabling Basic Authentication and injecting authentication headers

For each of the methods above, I'll provide a short explanation, list advantages and disadvantages, and direct you to available resources and code samples for more details.

And, an added bonus for you at the end:

  1. Adding another layer of security with Data-Driven Tokens


SOAP Web Service for ASP.NET

SQL Server Reporting Services exposes a SOAP web service API which would allow you to implement a backend "middle man" of sorts, that would be responsible for passing on parameters, configurations, and authentication, to the SSRS web server, get the HTML response back and output it back to the client.

Advantages

Since it's up to you to implement pretty much everything in-between the client and the report server, that also means that you have ultimate control over everything. Including, of course, how you would perform the authentication. At no point whatsoever would an end-user be able to access the report server directly.

Another advantage of the SOAP web service is the ability to have full management capability with your Report Server. You can use it not only to display reports, but to also manage and deploy reports, create schedules, configure server properties, manage the report server namespace, create subscriptions, and more.

Disadvantages

While this method is the most secure and versatile, it's also the hardest to implement, since it's up to you to implement everything in-between the end-user and the report server. Think about everything that you take for granted in the report manager portal... And throw it out the window: The parameters pane, the report viewer toolbar, drill-down between reports, and of course the authentication... Now it's YOU who needs to implement the equivalent of all that.

Resources

Microsoft has a few resources to help you get started with the SOAP method:

There's also this sample project developed by Marian Dumitrascu way back in 2005. I couldn't find a newer sample project, but if you want some kind of starting point, I suppose this is better than nothing:

ReportViewer Control for ASP.NET

A somewhat easier alternative to using a SOAP web service is to integrate a special ASP.NET control in your existing website, called ReportViewer.

After installing a NuGet package in your project and importing a few assemblies, you can add an HTML element in your web page which looks something like this:

<rsweb:ReportViewer ID="ReportViewer1" runat="server" ProcessingMode="Remote"> <ServerReport ReportServerUrl="https://AContosoDepartment/ReportServer" ReportPath="/LatestSales" /> </rsweb:ReportViewer>

Advantages

This method, similarly to the SOAP web service method, is something that you implement on your ASP.NET backend. As such, this also means that you have total control over the authentication method.

It's also easier to implement compared to using the SOAP web service.

Disadvantages

Same as with the SOAP method, there's also quite a lot of work for you to do here in order to implement this ReportViewer control. Specifically, the lack of a ReportViewer toolbar and complicated report drill-down should prove the hardest to deal with.

Resources

Luckily, we can give big thanks to one Stefan Steiger, who went to great lengths to implement and maintain fully working ASP.NET examples for every version of reporting services, and even a special version for ASP.NET Core.

It still leaves a lot of work ahead of you in order to integrate his code into your own website somehow, but it's a significantly favorable starting point nonetheless.

Check out Stefan's repositories below:

You can also find some Microsoft resources in this section of the Microsoft Docs:

Custom Security Extension

Another thing you could do is to implement a custom security extension for reporting services.

It's a bit like developing an authentication provider.

The security extension must be built in a separate .net project, and the output DLLs and other artifacts copied into the reporting services installation folder. Then, you edit a few reporting server configuration files to enable your custom extension, and that's about it.

Advantages

You don't have to implement an entire middle-man between the reporting server and the end-user, and therefore, you don't need to implement your own ReportViewer toolbar and fuss around with report drill-downs and such. You can send your users directly to the report server URL, and the authentication process would be the one implemented by your custom extension.

It would be fairly easy to implement anonymous authentication this way.

Disadvantages

The downside is that it would be up to you to implement an alternative authentication mechanism end to end. This means: Reading the user HTTP context, managing users and roles in a database, parsing headers, validating passwords and sessions and roles and groups and... You get the idea.

Another disadvantage, in terms of cybersecurity, is that you're exposing your report server URL to your end-users, and possibly giving them a wide-open backdoor into your reports. And if you're using it to implement anonymous authentication, well... That's like leaving your house completely unlocked.

Resources

Microsoft themselves came to our aid in this particular case, by releasing an open-source example of a custom security extension already developed end to end. And they have some helpful resources to help on this topic:

Also, one Frans van der Geer took it upon himself to take Microsoft's sample extension, and create a new version from it, specifically meant for anonymous authentication:

Basic Authentication

Every visitor has what's called "HTTP headers" that are forwarded from their web browser and into your web server. They serve as a kind of "metadata" about the user. These headers are what's letting your web server know where the user came from, what kind of browser and operating system they have, what country they're from, and so on. These headers can also hold basic credentials to authenticate against a web server, forwarding a username and password encoded in a base-64 string.

That is the meaning of "Basic Authentication" in a nutshell.

By default, reporting services use far more sophisticated mechanisms. But, by changing a configuration file, you could allow SSRS to accept Basic Authentication headers as a means for authentication.

Advantages

This is by far the easiest method to implement. All you have left to do, after enabling basic authentication, is to somehow