reporting services configure to use application role

0
votes

I researched through Stackoverflow and some tutorials but cannot find an information on whether it is possible to configure the account under which the Reporting Services are running to have the permissions of an application role which is set on the source database? For example, I created a specific application role on the database for the reports and report builder to only being able to query certain schemas. How do I force the Reporting Services to use that role when connecting to that database as its source?

I want to make this work in Reporting Service itself. Because when the client deploys our reports, by default my database is the source for all of those reports. So it makes sense, for security reasons, to make sure that all deployed reports can only access what they should be accessing. And if a client decides to add a new report - they only have access to certain schemas that they can query.

I looked under the Security drop-down option from the data source, but see no way of configuring it to work against the application role.

1
sql-serverreporting-services
This sums it up: dba.stackexchange.com/questions/19967/…. Are you certain you want/need to use application roles? - Nick.McDermaid
No, that example shows it how to do it in the report. I want to make this work in Reporting Service. And yes. Because when the client deploys our reports, by default my database is the source for all of those reports. So it makes sense, for security reasons, to make sure that all deployed reports can only access what they should be accessing. And if a client decides to add a new report - they only have access to certain schemas that they can query. - Widunder
Yes I understand you don't want to have to special code/passwords in every report. You can't set the app role in the connection string, you have to set it after you connect. Perhaps you can use a logon trigger to run sp_setapprole after logon: sqlservercentral.com/blogs/basits-sql-server-tips/2012/09/17/…. - Nick.McDermaid
.... or why don't you instead use specific report credentials in the data connection and limit the schema to those credentials? For example, run SSRS under a windows service account, limit that service account to SSRS schema, then use windows authentication in your data conneciton. Then no app role is required... that's why I asked why you need to use an app role - Nick.McDermaid
since using the app role proved to be too complicated or nearly impossible to implement into SSRS, which I think shouldn't have been. The windows login on the data source will have to do for now. The problem with your suggestion is that I do not know the clients domain or the service name under which the SSRS will be deployed to on their environment - Widunder

1 Answers

0
votes

Because this has proven impossible in a simple way. And the problem that we do not know the account SSRS (deployed data source and the reports) will be deployed under on the client's environment. The specific role will be added as Database Role, and the membership of which will have to be set upon the manual configuration on the client's environment through the application.