locked
SSRS 2017 - Row Level Security for Custom Authentication (Forms Based) RRS feed

  • Question

  • QUESTION: Has anyone used Row Level Security for SSRS 2017 using Custom Authentication (Forms Based) or even aware if this is possible in SSRS 2017?

    USE CASE:

    I am looking to incorporate SSRS 2017 as the reporting solution for a multi-tenant application, hence we have a need for using row level security (RLS).  It is a custom security model using Forms Based Authentication that leverages the security extension, so incorporating windows integration is not an option (similar to the solution posted here).  Ideally, there would be a setting in the report (or data source) to set a session_context to be leveraged in the corresponding query, but there is nothing short of writing our own custom Data Processing Extension which theoretically would be using mirroring SQL Server processor but adding a way to set the session context when opening a connection.  Unfortunately there is no extension point that would allow this to be done and those classes are all internal.  While not impossible, it's a good amount of work and requires another maintenance component.

    I have some "brute force" approaches in the queue, but ideally would like something similar to the solution that leverages windows integration.

    Wednesday, January 10, 2018 7:27 PM

Answers

  • Hi,

    Thank you for your reply. 

    In that case, I am afraid RLS feature is not available. 

    Personally, I recommend you that submit this suggestion at https://connect.microsoft.com/SQLServer/ . If the suggestion mentioned by customers for many times, the product team may consider to improve it in the later SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

    BR,

    Henry

    • Proposed as answer by Henry Jiang Wednesday, January 17, 2018 3:04 AM
    • Marked as answer by ShewDawgNYC Wednesday, January 17, 2018 1:28 PM
    Wednesday, January 17, 2018 3:04 AM

All replies

  • Hi,

    You could try with the built-in function User!UserID and set the visibility based on the users identity. 

    You could also set on the datasource and report manager to enable it:  Enabling Row-Level Security in Report Manager.  Here is another relative thread: SQL Server 2016 Row-level Security and SSRS a perfect match

    Best Regards,

    Henry 

    Thursday, January 11, 2018 7:17 AM
  • Thank you Henry for your response.  The articles you linked are based on Windows Integration, which is not my use case.  I might be able to work with the UserId but there will be more to it than just using the UserId with it being a multi-tenant.  
    Thursday, January 11, 2018 9:29 PM
  • Hi,

    Thank you for your reply. 

    In that case, I am afraid RLS feature is not available. 

    Personally, I recommend you that submit this suggestion at https://connect.microsoft.com/SQLServer/ . If the suggestion mentioned by customers for many times, the product team may consider to improve it in the later SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

    BR,

    Henry

    • Proposed as answer by Henry Jiang Wednesday, January 17, 2018 3:04 AM
    • Marked as answer by ShewDawgNYC Wednesday, January 17, 2018 1:28 PM
    Wednesday, January 17, 2018 3:04 AM
  • Guys,

    I have similar issue.

    We have RLS applied at SQL Server database and same security we want to go through SSRS Reports. 

    SSRS Reports have Subscriptions as well as Scheduled cache. To make Subscription and Scheduled cache work, "it is essential and inevitable to store the credential in Data Source"

    If we do so then can't pass real user to SQL Database and RLS will not be effective.  I am sure If I enable option "Log in using these credentials, but then try to impersonate the user viewing the report" then It will pass real user to database but looks like user browsing reports should have permission to Database (sad news)

    and not sure that after enabling "Log in using these credentials, but then try to impersonate the user viewing the report" subscription and Scheduled cache will work or not..

    Still we testing but if anyone has done this please let us know.

    Thank you in advance. 


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, March 28, 2019 5:54 AM