Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 26 '12 at 16:40

This question came from our site for professional and enthusiast programmers.

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 Aug 16 '11 at 7:42
    
thanks Bogdan - I will certainly look into it. –  YS. Aug 16 '11 at 23:03
    
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 Aug 19 '11 at 20:41
    
Hi DForck - the entire system had been based around approle and we want to keep it that way. –  YS. Aug 23 '11 at 23:46
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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