Reporting Services in SharePoint Integrated Mode with SSPI/Windows Authentication Architecture

Question

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:

1. 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.

2. 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.

Answer 1

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.

Answer 2

Actually It is not the case so we want. I recommend to install SQL Server Reporting Services another machine different from the Server1 and Server2. Every server must be responsible their duty.

In this case i was set up Reporting Services on Server 1 which running corporate's SQL Server engine. If we consider data source and reports on the same machine. Troubleshoot will be much more easier.

There is not any difference about report security when you install reporting services Server1 or Server2. Because you are already covered this problem on the SQL Server engine.

Tip: if there is a chance to consolidate databases it would be best choice i think. (By the way SQL Server parallelism parameter must be 1 for sharepoint databases it is important) I recommend to you about consolidate your databases under one engine. In this way you have one database machine. :)