Leveraging Web Application Proxy in Windows Server 2016 to provide secure access to your SQL Server Reporting Services environment

  • Article

 

A common ask from customers is around how to make their Reporting Services environment available to users outside of their internal corporate network.  This is especially important for them when they’re trying to use the Power BI mobile apps to view mobile reports and KPI’s while on the go.  Today, we’re pleased to announce that we’ve made updates to both SQL Server 2016 Reporting Services and the Power BI mobile apps to give companies some additional options to enable this in their organizations.

First introduced in Windows Server 2012, Web Application Proxy (WAP)provides reverse proxy functionality for web applications inside your corporate network to allow users on any device to access them from outside the corporate network. WAP pre-authenticates access to web applications using Active Directory Federation Services (ADFS), and also functions as an ADFS proxy.  Below is a typical network topology when using WAP.

Web Application Proxy Topology

While we’ve provided ad hoc guidance in the past around taking advantage of this functionality, we’ve not had official support for it in the mobile apps, nor did we have full support for viewing mobile report content in the browser for many scenarios using this setup.

ADFS Support using WAP now available in the Power BI mobile apps

As first announced on the Power BI blog today, there is now support to access your mobile reports and KPI’s through the mobile app when used in conjunction with the Web Application Proxy functionality in Windows Server 2016.  Currently in preview for both iOS and Android devices, this will allow organizations using Kerberos in their corporate environment to access the mobile apps outside of that environment using ADFS.  The apps now also support modern security features that can be enabled in WAP like multifactor authentication, providing additional security options for organizations that can be tailored to their specific needs.

Keep in mind that though you must use Windows Server 2016 to enable the functionality, you don’t need to run your Reporting Services instances on servers running WS2016 machines, nor do you need to update your entire ADFS infrastructure to the latest release.  There is a detailed walkthrough available for you to setup and enable this in your organization posted in the Power BI documentation.

We’ve also made accessing your reports through your web browser using WAP more flexible with the recently released Cumulative Update for SQL Server 2016 SP1.  In this update, a bug that had prevented certain customers from accessing mobile reports through their web browser using WAP was addressed.  If you want to access reports through your web browser using WAP, make sure you have this update applied to your SQL Server Reporting Services environment.  Please note - you can use the WAP functionality found in either Windows Server 2016 OR Windows Server 2012 R2 when accessing reports through the browser.  There is a walkthrough guide of setting up WAP in the context of an application you can access through the browser.  We’ll be putting together a specific walkthrough for Reporting Services for this scenario in the coming weeks as well.

Try it now and send us your feedback

Thanks for all the feedback so far around the need for this capability, which allowed us to prioritize it accordingly.  As always, let us know if you have any questions or comments about the functionality in the blog comments.

Comments

  • Anonymous
    February 02, 2017
    I feel that the Report Web App should eventually be separated entirely from the Report Server host. The latest improvements feel like a SPA was just shoved into Report Server to improve the UI a bit and now we're setting up reverse proxies to allow non-VPN users to access this web via a convoluted reverse-proxy through ADFS (surely prompted by the fact that almost no one uses VPN on mobile).Network administrators don't relish the idea of exposing SQL Server, in any context, to the public web and I don't like hosting a full SQL Server license on a VM just to host the Report Viewer on the proper front-end subnet. If the report web app was distributed as something I could simply install on a web host, such as IIS, and configure to connect to the Report Server database, it'd be fantastic. The app itself would be the proxy of the Report Service and it could support virtually any enterprise SSO configuration, without taking dependency on additional security products.I implemented a custom security extension for SSRS to authenticate users and inspect roles via IdentityServer. I imagine not every organization uses ADFS or Azure AD and this approach to SSO doesn't feel all that expanded over the SharePoint integrated security. What if my BI users aren't necessarily in my on-premises AD? The consumers of my RDBMS are definitely not the same as those interested in SSRS generated reports.
    • Anonymous
      February 07, 2017
      Hi Matthew,Good feedback - definitely not the extent of what we want to do when it comes to security options, but wanted to provide some guidance for folks around a solution option that some organizations were looking to leverage. There are many things we'd like to do in the future, just a matter of resources and customer feedback around priorities. This is definitely helpful.Thanks,Chris
      • Anonymous
        February 27, 2017
        Hi Christopher, I installed the requirements for 'Technical Preview of Power BI reports in SQL Server Reporting Services' using this link: https://www.microsoft.com/en-us/download/details.aspx?id=54610 , Opened PowerBI, Get Data, selected analysis services, entered my name of server, 'Connect live' was selected, click on OK, select cube, click OK. Error Message: Couldn't load the model schema.AS Live Connection: TrueInternal error prevented loading the schema:An internal error prevented loading the model schema associated with this report. Please try again later. If the issue persists, contact Power BI support.
        • Anonymous
          April 02, 2017
          Sorry you're running into that error. The report server trace log files might contain more clues about where the problem lies.
  • Anonymous
    February 21, 2017
    Perhaps not the correct thread but is there any plans to allow users to publish rdlx files directly to the PowerBI Cloud based. Given PowerBI will be able to published to SSRS V.Next we were thinking symmetry could be achieved by delivering this functionality. Would also then remove the need for this scenario altogether?
    • Anonymous
      April 02, 2017
      Did you mean RDL files (reports you create in Report Builder or SQL Server Data Tools)? We've been executing on our roadmap to deliver the sort of symmetry you allude to -- both Power BI reports and RDL reports available both in the cloud and on-premises. Today, we're focusing on enabling Power BI reports on-premises, while in future, we'd like to enable RDL reports in the cloud as well.
  • Anonymous
    February 23, 2017
    Any comments where WAP with SharePoint Server 2013/2016 and SSRS 2016 will allow for viewing PBI report in an html iframe in a SharePoint page? The users would be external but authenticated through WAP + an authentication protocol/provider.
    • Anonymous
      April 02, 2017
      Ultimately, WAP needs to translate the user authentication into an identity type Reporting Services supports, either natively (Active Directory on-premises) or via a custom security extension you develop. You might be able to get the scenario you describe to work if you either convert to an AD identity or develop a custom security extension.