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.


  • 2
    you can start from here msdn.microsoft.com/en-us/library/aa237582(v=SQL.80).aspx to understand how to extend Reporting Service (inject code that sets application role) , but I would not take this comment as an answer , not sure it this is really most easiest way and it could not be done in configuration
    – Bogdan_Ch
    Commented Aug 16, 2011 at 7:42
  • depending on how people are accessing this report you could embed Report User credentials in the data set and then set up the login sql server side so that it has limited privileges.
    – DForck42
    Commented Aug 19, 2011 at 20:41
  • Hi DForck - the entire system had been based around approle and we want to keep it that way.
    – YS.
    Commented Aug 23, 2011 at 23:46

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.