SSRSandRLS

SQL Server 2016 Row-level Security and SSRS a perfect match

In this blog I will explain and demonstrate how to leverage a new feature in SQL Server 2016, Row Level Security (RLS) with SSRS.  Detailed steps including the TSQL script and SSRS project is provided at the end of this post.  To get started, you need to setup RLS.  In this example I will use Windows Authentication when connecting to the data source (SQL Server).  Therefore, the first step is to create a Login on the SQL Server from a Windows Account:

USE [master] GO
CREATE LOGIN [domainusername] FROM WINDOWS
GO

After the login is created, the next step is to create a User in the database that is associated to the data that requires RLS. 

USE [databasename] GO
CREATE USER [UserName] FOR LOGIN [domainusername];

At this point there are a couple paths that can be taken.  For the sake of brevity and so that we are all on the same page I will provide sample scripts.  To start run the following statement:

DROP TABLE IF EXISITS dbo.Students
GO
CREATE TABLE dbo.Students
(
StudentID int,
SchoolRep sysname, -–This column will store the database username
Class varchar(20),
Grade char(1)
)

Next populate the table with some data by running this script:

–Add a few rows.  Any rows that include dbo as the SchoolRep will be return for those users that are dbo or system administrator

INSERT INTO dbo.Students
VALUES
(1, ‘<DATABASE USERNAME>’, ‘College Algebra’, ‘A’),
(2, ‘<DATABASE USERNAME>’, ‘English 100’, ‘B’),
(3,’dbo’, ‘English 101’, ‘F’),
(4,’dbo’, ‘College Algebra’, ‘C’)

Replace DATABASE USERNAME with your user.  Now give the user read access to the table:

GRANT SELECT ON dbo.Students TO [UserName];

Now the fun begins.  Execute the following script in the same database where the table was created:

CREATE SCHEMA Security;

–Function that returns 1 when the SchoolRep column is the same as the user executing the query

CREATE FUNCTION Security.fn_securitypredicate(@SchoolRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SchoolRep = USER_NAME();
GO

–Policy that that adds the function as a filter predicate

CREATE SECURITY POLICY SchoolFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SchoolRep)
ON dbo.Students
WITH (STATE = ON);
GO

You can test your implementation by executing the following script:

EXECUTE AS USER = ‘Database UserName’  –Replace with your User
SELECT * FROM dbo.Students;
REVERT;

That was not that bad.  Now let’s get to SSRS.  I am going to assume that everyone can create a Data Source, Dataset and a report that leverages that Dataset. Deploy all three to your Report Server.  Once the data source is deployed open it in Report Manager and verify that the radio button labeled Windows integrated security is selected.  The Data Source should resemble the following:

image

Finally, connect to the Report Manager or web portal (New In SQL Server 2016) as the windows user and run the report.  You should only see the rows that contain that user as the SchoolRep. Like I said, a perfect match.

image

Steps to Reproduce (as of the writing of this blog)

1.  Install SQL Server 2016 CTP 3.3

2.  Create a database or use an existing database

3.  Download and run this script.  Please read the comments.  You will be required to adjust certain items to fit your environment.

4.  Install SQL Server Data Tools 2015

5.  Download this SSDT 2015 SSRS Project.

6.  Run the ReadMe report and follow the outlined steps.

7.  Deploy the report to your report server.

8.  Connect to the Report Manager or web portal (new in 2016) as the user and run the report.

Wow, I cannot wait to help a customer get this implemented into a production environment.

Talk to you soon,

Patrick LeBlanc

2 thoughts on “SQL Server 2016 Row-level Security and SSRS a perfect match

  1. Permalink  ⋅ Reply

    Sumit Pandit

    May 18, 2016 at 12:57pm

    Hi,
    I have tried the steps you provided above. But wehn I create a data source from SSRS portal it gives an error “Cannot open database requested by the login. The login failed.
    Login failed for user ‘domain\user’.”

    Please guide here

Leave a Reply

Your email will not be published. Name and Email fields are required.