Reporting Service and Application Role

Question

First poster, long time lurker here. What is the best way to activate application role in a report ?

I've tried different things and so far the only method that works is to embed the call to the application role like so :-

EXEC sp_setapprole 'REPORTZ', 's3cr3t';
select *
from mytable
where ID < 10000

in the Dataset. It does work... but not to my liking (certainly not in the shape I'd want to roll into production environment).

I'd prefer if I could somehow 'hijack' or 'inject' the application role activation line at runtime either through custom assemblies or probably some sort of 'server hook' in the Reporting Service (which in both cases, I have no idea how)

Much appreciated for your time + kind attention.

YS.

Answer 1

I see a couple of ways this could be done without resorting to anything overly fancy.

1. The first would be to use Windows integrated authentication and assign permissions to the groups which represent users of the application.

2. You could create roles which could be granted, and then use set role to assume the role in your code in the db. This way you aren't passing in a password and you are authenticating the application first.

The advantage of the first is that it is easy to manage, in an AD domain, who has access to the application. The management would be relatively simple. The major disadvantages would be that it would be limited to cases where integrated auth makes sense, and you'd have to support integrated auth all the way through. Additionally every hop ends up requiring 3-way authentication (between server, client, and KDC).

The advantage of the second is that it is probably easiest to shim in, and it exposes no security info over the network, and it works in cases where integrated auth is not going to work well.

I would try both of those approaches before trying to extent the reporting service. As the comment suggested, http://msdn.microsoft.com/en-us/library/aa237582%28v=SQL.80%29.aspx may be helpful and may work, but it is conceptually more complex than trying to manage roles directly.