locked
Question about data access and subscriptions in SSRS with Row Level Security enabled on the source database RRS feed

  • Question

  • I've set up a Reporting Services Server 2016 in native mode. After that I migrated our SSRS 2008 Reports to the new server. While doing so I added a location based parameter/filter. The values of the parameter come from a shared dataset, that lists our company sites. This dataset represents a table in the SQL server database. Each user that queries data from that table (through accessing the shared dataset in SSRS) get's checked by a Row Level Security policy/function that looks up whether or not a user is member of a specific Active Directory group. Based on that group the parameter in the report (which is based on the dataset) shows different company sites to different people and filters the rest of the data accordingly.

    My problem is the following:

    I can't seem to creat subscriptions, because the data source for the shared dataset isn't set up to store the credentials of the user. But I can't store the credentials, because I need them to get the group of the user accessing the report.

    Is there a solution to this?

    I mean, I only need to run the subscription with some service account and configure the parameters myself, so that the user can't acces parameter values he's not allowed to see/use. But I don't think that is a configurable possilbilty. Are there other options to get subscriptions running?

    Thanks in advance!

    Kind regards,

    Chris

    EDIT:   

    I opened up this suggestion Plz vote for this feature to be developed. See answer to this question-thread below.

    https://connect.microsoft.com/SQLServer/feedback/details/3141155

    • Edited by Justify87 Monday, September 18, 2017 7:36 AM
    Thursday, September 14, 2017 10:19 AM

Answers

  • Hi Justify87,

    Thank you for your reply. If so, as far as I know, currently there is no configuration allowed to achieve it. The SSRS report parameter cannot be controlled by AD permissions. Sorry for the limitation, 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.

    Best Regards,

    Henry

    • Marked as answer by Justify87 Monday, September 18, 2017 7:20 AM
    Monday, September 18, 2017 5:46 AM

All replies

  • Hi Justify87,

    First thing first, to create subscriptions, it is essential and inevitable to store the credential for the dataset, otherwise the sql server agent job will not proceed and complete the subscription. 

    If you would like the users receive subscription reports which are only under their permissions, (i.e, reports based on different parameters), you could use the data-driven subscription. (store credential is also needed). You can set parameters for the data-driven subscription, using a value from the result set. The columns of the result set can be selected as a source of a data value to use with each subscription instance.

    For detailed information, you could refer to Create Data-driven Subscription Page (Report Manager)Create a Data-Driven Subscription (SSRS Tutorial)

    Best Regards,

    Henry 

    Friday, September 15, 2017 7:45 AM
  • Hi Henry,

    thanks for your reply!

    But I don't see how data driven subscriptions would help me achieve my goal. Its not stored in the data who has permission to sepcific data. Only our Active directory groups will give me the info on who will have access to which company site data. 

    Friday, September 15, 2017 8:02 AM
  • Hi Justify87,

    Thank you for your reply. If so, as far as I know, currently there is no configuration allowed to achieve it. The SSRS report parameter cannot be controlled by AD permissions. Sorry for the limitation, 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.

    Best Regards,

    Henry

    • Marked as answer by Justify87 Monday, September 18, 2017 7:20 AM
    Monday, September 18, 2017 5:46 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:46 AM
  • If I enable option "Log in using these credentials, but then try to impersonate the user viewing the report"

    and modify existing cache and try to apply, I am getting this error. 

    An error has occurred.

    The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Thursday, March 28, 2019 6:07 AM