x

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?

more ▼

asked Dec 16, 2009 at 01:56 PM in Default

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Dec 16, 2009 at 04:20 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 17, 2009 at 06:04 PM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x555
x162

asked: Dec 16, 2009 at 01:56 PM

Seen: 3031 times

Last Updated: Dec 16, 2009 at 01:56 PM