Saturday, 19 February 2011

ReportViewer - Sub Report with integrated security problem

I have recently had a problem with using Sub Reports in Report Viewer when the SQL server is a seperate box from the web server. The initial data is loaded correctly but when the subreport processing event is fired the second SQL command comes back with a failed login error as it was trying to connect with the local system account of the web server. The connection strings to the database are all using Integrated Security so the impersonation context needs to be there to access the database as the correct user.

After much investigation is looks as though this is caused by a phenomenon called a 'double hop'. The request is sent from the web server, to the SQL server and identity information is passed across to the SQL server. When the sub report is processing the request is again made to the SQL server but at this point the indentity information is lost as it only lasts 2 'hops' This is aparently by design for NTLM authentiation. I have read this can be fixed by using Kerberos authentication but I know nothing about this.

Two solutions that I have come up with seem to work.

1) Change all connection strings to the database to explicity specify the UserID and Password for the SQL connection. This seems to work ok but unless you want to mess about encrypting web configs and connect strings then this isnt really an option.

2) Change the user account the web applications app pool is running under. I changed this account to use the same domain user account that the web application was impersonating. It seems the sub report request is using the identity of the application pool to connect. Changing this then uses the correct user account for the integrated security in the connection string. One thing to note with this method is that I had to change the Anonymous access user in IIS to also use this domain account as a login box was being presented when browsing to the site.

Here is the code for the aspx page that is loading the report. The problem manifests itself in the LocalReport_SubreportProcessing event.

No comments:

Post a Comment