question

Blackhawk-17 avatar image
Blackhawk-17 asked

SSRS Proxy Account

I have a request from a dev team to create a proxy account for SSRS.

Their stated goal is to reduce administrative burden.

They want reports to use a data source with embedded credentials. The account would have permissions on the databases in question to access the data/procedures to fulfill the reports.

I can agree with that. It's easier then ensuring each user has the correct permissions on every table.

They also want this account to be included in the DBO role for every user database. At this point I get nervous.

Is this a standard practice? How are others dealing with this scenario?

Bless it or counter it?

sql-server-2005ssrssecurity
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered

You have a few possibilities

1) the proxy account mentioned - the easiest way and user do not need to enter a password to da datasource when he runs an report

2) Asking for a password to the datasource when user runs a report.

3) Using user domain acount and database security set for his own account. - hereare three scenarios

  • SSRS are running on the same machine SQL Server (Ideal solution as user is automatically authetificated and using his own rights on the data sources)
  • SSRS are running on different machine from SQL server and Kerberos is set up in the network (Ideal solution as previous - the result is the same as previous)
  • SSRS are running on different machine from SQL server and you have no Kerberos in the network - the user will not be able to authetificate to the data sources.

If you have no other solution than the proxy account, the I suggest to create a standalone role for this account and Create also separate Reporting schema. Then in this schema create stored procedures as sources for the reports and grant the role only access to the Reporting schema to execute the reporting stored procs.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jack Corbett avatar image
Jack Corbett answered

No dbo. You should create a database role, ReportRunners or something, that yougrant specific rights needed to run reports. Should only have Select rights. DBO has rights to add users, objects, etc.. and bypasses security checks within the database. DBO is to the database what sysadmin is to the server.

Pavel provided some good options and 3 is the best one. How much work is it for the network admins to create a group in AD for reporting and then you can add that group to the server and put that AD group in the ReportRunners role in the database. Then all they need to do is add/remove users from the role. They are likely already doing something similar to grant the users rights to get to the Report Server anyway.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.