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):
SOAP Web Service of SSRS
ReportViewer Controller for ASP.NET and ASP.NET Core
Developing and installing a Custom Security Extension
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:
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 "inject" the basic headers you need to authenticate with the reporting server, into the end-user's HTTP headers (which isn't too difficult), and then forward them to your report server URL.
Disadvantages
This method is also the least secure. If you're using the JavaScript method, then not only would you be exposing the direct URL to your report server, but you would also be exposing the username and password that you use for authenticating with SSRS in such a way that it could be visible to the end-user.
Needless to say, this could be extremely dangerous.
Using HttpClient instead at the webserver backend would, therefore, be preferable.
Also, due to various reasons, you'll have to disable the built-in Toolbar and Parameters pane when using this method. Otherwise, you'll get reference errors related to CSS and JavaScript. This also means that dynamic report functionality such as drill-down, sorting, and expand/collapse wouldn't work either.
If your reports are simple enough, then this could be a good enough solution. Otherwise, you'd have to look into the other methods instead.
Resources
Microsoft has a detailed page explaining how to enable basic authentication in SSRS right over here:
The next step after that is to learn how to inject basic authentication credentials into HTTP headers.
Unfortunately, I found no open-source examples of this. But, I did find a few stack overflow answers which were very helpful:
I also decided to create my own open-source sample project to demonstrate an implementation of SSRS with Basic Authentication, and you can find it here:
Adding another layer of security
Once you override the authentication of SSRS and implement a potentially weaker alternative (especially such that exposes your Report Server URL), you potentially expose yourself to malicious hackers that could gain access directly to your report server, and from there, possibly, into your database.
That is why it's strongly recommended, in those cases, to add more layers of security.
External layer
First, which should be obvious, you should be protecting your report server URL with your own layer of security. So that, in order for a visitor to even reach your report server URL, they would first need to be authenticated using your own authentication method. It could be something like aspnet role-based security, or an SSO (Single-Sign-On such as Microsoft, Google G-Suite, Twitter, Facebook, etc), or whatever it is that you're currently using to authenticate end-users at your website. In other words, when an end-user is exposed to your report server, it would be AFTER they've already been vetted and authenticated via your own methods.
Also, the underlying credentials that you'd be using for actual authentication with your Report Server, should definitely belong to a weak user that has minimal permissions: Read-only permissions at the SSRS level, as well as making sure that the login used with your reporting data source has minimal read and/or execute permissions at your SQL Server database.
Internal layer
Secondly, you could also implement temporary data-driven tokens to authenticate your users by using your SSRS reports themselves.
This could be done by having your website backend add a row to a table right before forwarding the user to the relevant SSRS report. This record would hold a randomized string, an identification of the specific user, and expiration date.
Then, your website would forward the user to the report while sending the user identification and the token randomized string as report parameters. Kinda like this:
https://mywebsite.com/reportserver/page.aspx?Account=Johnny5&Token=dyjnvf36ht9In your SSRS report itself, you would have these two parameters ready to receive values, and you would have a dataset that takes these parameters and queries the tokens table to look for the corresponding record. We'll call this your "token-validation dataset".
And here's the kicker: if this dataset doesn't return anything, then you would not show anything in your report.
This can be done by something like the following:
Add an internal parameter in your report. Give it a default Query value retrieved from your token-validation dataset.
Add a Filter on all of your other datasets, based on the value of the aforementioned internal parameter (right-click on the dataset and select "Properties", and open the "Filters" section).
Alternatively, you could have your token-validation dataset run a stored procedure that throws an error when a valid token is not found. That should help stop your report in its tracks when needed.
Additionally, if you want to be even more sophisticated, you could have this stored procedure update the expiration date on the token that it finds. This way, your user's token will be "kept alive" as long as the user is actively viewing reports.
In short, you would have something like the following flow:
The disadvantage here is that you would need to implement this mechanic for ALL of your SSRS reports. That's obviously a lot of development overhead, especially if you have a lot of existing reports that you now must refactor.
If you're only starting, however, you could create one "template" RDL file with this mechanic already implemented, and then use that template file as a starting point for all of your other reports that you'd be developing (copy-paste galore!).
I will probably create such a template sample report and release it as open-source at some point in the future. But that's something for another time.
Meanwhile, if you liked this post and want to learn more about SQL Server Security best practices, then be sure to check out this post as well:
留言