sql server reporting services: Hi,

I'm having a little difficulty getting my head round windows
integrated
security/impersonation with reporting services, and I'd appreciate a
little help with the problem I'm trying to solve (or an indication
that what I'm trying to do is too hard to be worth it!)

To give you the background: I'm developing a web portal application
with SQL Server reporting services. A number of the reports need to be
bound to groups of users; also, some of the reports need to know the
logged-in user to use directly in the SQL queries (to prevent any
malicious changing of hidden parameters through the querystring).
Also, another piece of info, I can't justify the cost of the
Enterprise version of SQL Server and so cannot use a reporting
services custom security extension. Also, I'm serving
up my reports using the reportviewer custom control.

I saw the posts entitled 'Well, now how do I NOT logon', but these
seem to be using custom security, which as I said I can't use as we
only have a standard SQL Server license.

So back to Windows Integrated security, I have no problems creating
accounts on the server for every user. What I don't like, however, is
the integrated security popup box. It's quite ugly and from a user
experience point of view really doesn't fit in with their expectations
of a web application, where they would expect a more forms based view.
I thought that I might be able to do something in the background
whereby they could login through a form and I could manually do the
logging in, and from then on (until timeout) this user would be
regarded by the webapp and report server as the credentials supplied.

Okay, so I used the demo in msdn:
ms-help://MS.MSDNQTR.2004JUL.1033/cpref/html/frlrfSystemSecurityPrincipalWindowsIdentityClassImpersonateTopic1.htm

and webapp'ed it. This works and I was hoping that if I didn't undo
the
impersonation at the end, that all future http requests from this
client
would be regarded as the impersonated user, which would obviously
enable
someone to login and then when they view reports they would be that
user. I
kinda knew that wasn't going to work and it feels like I might still
be able
to do this by doing something with the security token.

Is what I'm trying to do mad? Am I going to have to implement my own
HttpHandler and impersonate the user I think someone is, at each
request? It
would be great if there are any tutorials out there. Obviously if it's
too
difficult, or will introduce huge security weaknesses in the system
then it's
just not worth it. As I said, all I'm trying to do here is remove the
popup
login box!

Thanks in advance for your help,

Hi Phil:

See my inline comments below. I hope they provide some help.

--
Scott
http://www.OdeToCode.com

[quoted text, click to view]

<snip>
[quoted text, click to view]

If the accounts are synchronized (i.e. the login name and password are
the same in both the user's domain and on the server, you should not
get a prompt to enter credentials again. (I'm assuming you are not on
a domain, or the report server and the user machines are not on the
same domain).

This also depends on some IE settings: Tools -> Internet Options ->
Security -> Custom Level -> User Authentication (scroll down to the
end). I believe the default is "Automatic logon only in Intranet
Zone". If the user makes the report server a trusted site, and sets
this to automatic logon under the custom level for trusted sites, I
believe you should be good to go.

[quoted text, click to view]

Earlier you mentioned you were using the reportviewer custom control.
I assume this is the control provided as a sample by SSRS. The report
conrtol viewer uses an IFRAME with a URL set directly to the report
server. In this scenario the impersonation setup in your code would
not change the credentials sent to the report server from the browser
rendering the IFRAME. Impersonation only changes the identity of the
thread on the server side.

[quoted text, click to view]

The HttpHandler would be worthwhile only if the request for the report
was coming through your code. With the report viewer, the browser is
going directly to the report server.

Hope this makes some sense for you,

Phil,

This question pops up quite so often in this newsgroup. There is one thing
that needs to be made clear. The authentication dialog has nothing to do
with RS. Instead, it is a browser-generated dialog. Take a simple web app
and configure to run under IIS with Basic or Integrated security. If the
user belongs to the same domain as the server no dialog will be shown,
otherwise the browser will pop up the dialog when negotiating the
challenge/response with the server.

So, your question boils down to "How I could suppress the NLTML browser
authentication dialog when a web app is configured for Windows-based
security and the user belongs to another domain?" This is a noble quest but
as far as I know it is is doomed to fail. Instead, your time and budget will
be better spent in campaigning for upgrading to Enterprise Edition and
replacing the Windows-based security with custom security.

--
Hope this helps.

----------------------------------------------
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com


[quoted text, click to view]
ms-help://MS.MSDNQTR.2004JUL.1033/cpref/html/frlrfSystemSecurityPrincipalWin
dowsIdentityClassImpersonateTopic1.htm
[quoted text, click to view]