Accessing SQL Server Reporting Services Mobile Reports with the Power BI app from the Internet using Active Directory Application Proxy

 

 

In SQL Server 2016 the team introduced Reporting Services mobile reports to SQL Server Reporting Services optimized for mobile devices and connected to on-premises data, with an assortment of data visualizations.

The mobile reports can be consumed using any mobile device (IPhone, IPad, Android, Windows 10) in the Power BI mobile app while you are connected to the corporate network where Reporting Services is hosted, however some of the users also want to consume the mobile reports outside of the corporate network just using the internet, in the past such scenario required the careful configuration of different firewalls , demilitarized zones (DMZ) and opening specific tcp ports , for instance this article cover the basics but usually you work with your IT specialists to deploy such configuration.

Recently one of my friends in the Active Directory team mentioned Active Directory Application Proxy , which show promise as an option to open the mobile reports to those users outside of the corporate network with a simpler solution, so I decided to give it a try during the weekend and see how it would work with Reporting Services (warning: every single step is very well documented for people that does a better job than me writing so mostly I’m just providing the links to the documentation that I used and providing specific details of my configuration)

The first thing that you need is an Azure Active Directory (AAD) configured and syncing with your Active Directory (AD) on premises, in my test environment I just followed the instructions from Integrating your on-premises identities with Azure Active Directory and voila I have my AAD with the users from my on premises AD.

The next step is to Enable Active Directory Application Proxy , just following the detailed instructions.

Now is time to Publish applications using Azure AD Application Proxy , you can provide any name and the internal URL that is used to access SSRS is your corporate network, you also should use the Passthrough preauthentication method.

You might be wondering why Passthrough and not Azure Active Directory as the documented instructions, well it as long story about how the different authentication methods have evolved from basic with your password traveling as clear text over the network, NTLM , Kerberos and the “modern” authentications protocols like OAuth for applications over the Internet, Reporting Services being primary an On Premises services supports Basic, NTLM and Kerberos out of the box, and the Power BI app for Reporting Services expects those authentication protocols so if you add something in the middle like Azure Active Directory authentication the application doesn’t know what to do, so for this configuration you need to use Passthrough to allow the mobile app to authenticate

After publishing the app you need to Assign users and groups to the application , previously I created in the on premises Active Directory a group called SSRSAccess so I can just add to it the users I would like to grant access to the service, I assigned this group to the newly created Jaime SQL Server Reporting Services Application.

Now in the Azure Portal if you click in the Application and in the option Configure you will see a section with the public URL of the endpoint, is your opportunity to customize it a little bit, in my case I just rename from what the portal automatically provided to mobile

 

 

Its time for the test, as is something I never tried I didn’t expect full functionality but the url should be a good start to see if the publishing worked, so I took the URL and pasted on my browser, that’s when you realize and a newbie in the AAD matters as I got the following error

Lucky for me the AAD documentation has a very detailed troubleshooting guide where I found “Go to the subscriber’s Active Directory Licenses tab and make sure that this user or user group is assigned a Premium or Basic license.” , that fixed my issue

You know is ready to work when you type your URL in the browser https://mobile-jaimetars.msappproxy.net/reports/ and you are able to see the new Reporting Services Portal

 

 

The next step is to configure the Power BI Mobile app with the URL and the credentials

Yes!!! Average Joe (ajoe) can now access his mobile reports from the Internet.

 

Limitations and Considerations

My main goal during the experiment was to try out Active Directory Application Proxy to provide access to the mobile reports, this is not an official recommendation is provided "AS IS" with no warranties and before you try this in your environment you should consult with your IT department about your policies to enable external access to corporate information.

You can also provide full Reporting Services Portal access exposing the URL of the server (https://ssrs-rs/) instead of just the portal (https://ssrs-rs/reports/) as I did, for that scenario you can even enable Active Active Directory as an PreAuthentication method and use two factor authentication and any other policy you have configured in your AAD, however there are few limitations to keep in mind:

• You need to type your credentials again after the Azure PreAuthentication
• Simple paginated reports and KPIs just works !!!
• The future is here , we have fixed this issue. I have found the Mobile Reports doesn’t render in the browser in this configuration , it’s something I will look in our code to fix it in the future.
• Check https://powerbi.microsoft.com/en-us/documentation/powerbi-mobile-oauth-ssrs/  The Mobile App would not work as it doesn’t know how to authenticate
• Subscriptions and other features that relies in the URL configured in Reporting Services might be affected as SSRS doesn’t know anything about the URL that you get from AAD which is totally different from the one configured in Reporting Services

There are plenty of other things you I haven’t tried like Publish applications using your own domain name, Single sign-on and conditional access