Reporting Service and Application Role
I see a couple of ways this could be done without resorting to anything overly fancy.


The first would be to use Windows integrated authentication and assign permissions to the groups which represent users of the application.
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.