Row level security options for Mobile Reports in SQL Server 2016

image

Welcome back!

With the latest release candidate of SQL Server 2016 now available for download, let’s take some time to review some options you have to implement row-level security for Mobile Reports.  These options can also be used for paginated reports in Reporting Services, so you don’t have to do the same work twice.

If you’re using SQL Server 2016 (or Azure SQL database) as your data source, you can take advantage of the new, built-in row-level security functionality.  My colleague, Patrick LeBlanc, does an excellent job walking you through how to get this setup in SQL Server 2016 in a blogpost here – http://patrickdleblanc.com/wordpress/?p=90, so I’m not going to redo all the steps he lays out nicely on his blog.  Since the old report manager has been replaced by the sleek new report portal in RC1, I’ve added a new screenshot below to replace the one Patrick included –

image

If you’re using a version of SQL Server prior to 2016 as your datasource, you can achieve something similar by creating a view that filters out records based on the user name accessing it.  Assuming I used the same dataset I used in the first example, my view would look like this –

SELECT        Student, SchoolRep, Class, Grade

FROM            dbo.Students

WHERE        (SchoolRep = CURRENT_USER)

I also have the option to use row-level security from an Analysis Services data source.  Here’s an example of how you could do this using a tabular AS model

1. Setup the execution account in the Reporting Services Configuration Manager to run as an account of your choosing –
image

2. Grant this account admin permissions on your Analysis Services instance

image

3. For the data source you’ll be using for your mobile report, setup roles for your users to be mapped to.

image

Add users or groups to these roles –

image

And setup the filters for the roles accordingly.  These are the records anyone assigned to that role will be limited to seeing.

image

In Reporting Services, when you setup the shared data source for Analysis Services, it’d be setup like this –
image

Note –  Unless you have Kerberos setup in your organization, the Analysis Services and Reporting Services instance will need to be on the same server for this setup to work.

Regardless of which of these options I choose, I’ll always need to do the following steps to create and publish my mobile report –

1. Create my shared dataset in Report Builder or SQL Server Data Tools –

image

2. Save it to my RS server –

image

3. Create my mobile report using the SQL Server Mobile Report Publisher
image

4. Hook up my shared dataset to the visual elements –
image

5. Preview It in the Publisher –

image

6. If everything looks good, I publish it to my server so people can view it –
image

And there you go!  This is one of a series of articles I’ll be doing both on my personal blog and the Reporting Services team blog over the next few weeks to show you how to get the most out of investment in Reporting Services in SQL Server 2016.

Thanks for reading!