I have a confusion about the place of Reporting Services' installation in my current architecture. I have two servers, one where SharePoint is on, and another where my data resides. Both servers have SQL Server 2016 installed. I need to install Reporting Services in one of the places, I don't know where. Here are my requirements:
The users MUST be represented in the database. This means that if a user, who has access to a report, does not have access to its underlying tables, then the user must not be able to see the data. This means, I'll be managing the security using the DB Roles, and thus I need pass through authentication. I'll also be using Row-Level Security feature, which again, mandates the reporting services to tell the database what is the user that's querying the data.
I need to use all of the features SharePoint has to offer (and so does the Reporting Services). This means that I need to install Reporting Services in Integrated mode.
1- where should the reporting services be installed Server 1 or Server 2 (where SharePoint is located).
2- More importantly: how could I get Reporting Services to communicate the users who are accessing the data. I guess how could I see pass through authentication, to ensure security is not managed by just who has access to the reports and who doesn't. Assume everyone has access to all reports, and DB has to be smart enough NOT to provide unauthorized users anything.