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.
My Questions:
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.
Best Answer
Reporting Services in SharePoint Integrated Mode can only be installed on a SharePoint Server. That is, you can only have the Reporting Services on the same server because Report Server database is no longer utilized when you install in SHP Integrated mode. Instead, SharePoint databases are used, there are a number of sharepoint databases used by SHP and one of them will host your meta data and report definitions. This makes centrally managing reports possible. So there is only one answer to your question, Server 2 or Sharepoint server.