ReportViewer - Subreport processing impersonation context

I have a reportviewer report with a sub report. Running locally the report works fine, when i deploy to another server a get a SQL access denied error for the local user on the remote machine. eg

access denied for servername\servername$

i have a feeling this is due to me firing a SQL command to get the data required for the sub report and when deployed on the server the report viewer is firing this command and becuase all of my connection strings are using integrated security (for security reasons) its then not got the correct impersonation context.

My database access class is in a different assembly than the reporting assembly, both of these assemblies are referenced from the web application

It seems the report viewer is calling the sub report processing event when it needs to get the data for the sub report. The web application impersonates a user when it runs but the sub report processing event does not use this specified user. Instead perhaps the local system account instead, and as the connection strings are using integrated security the local system account does not have access to the database which is on another server.

anyone else have this problem?

here is the code for my report page

/// <summary>
        /// Page Load
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load( object sender, EventArgs e )
        {
            try
            {
                if ( !Page.IsPostBack )
                {
                    Reporting.Common.SetReportEmbeddedResource( this.ReportViewer1, "xxx.Web.WAP.Reporting.Reports.ApprovalRouteHeader.rdlc" );

                    this.ReportViewer1.LocalReport.DataSources.Add(
                        new Microsoft.Reporting.WebForms.ReportDataSource(
                            "CostDept",
                          Reporting.Repositories.ApprovalRoute.GetHeaderApprovalRouteList() ) );

                    this.ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
                    this.ReportViewer1.LocalReport.SubreportProcessing += new Microsoft.Reporting.WebForms.SubreportProcessingEventHandler( LocalReport_SubreportProcessing );

                    this.ReportViewer1.LocalReport.Refresh();
                }
            }
            catch ( Exception ex )
            {
                ErrorLogging.LogError( ex );
            }
        }

        /// <summary>
        /// Loads the sub report
        /// </summary>
        /// <param name="sender">object</param>
        /// <param name="e">args</param>
        protected void LocalReport_SubreportProcessing( object sender, Microsoft.Reporting.WebForms.SubreportProcessingEventArgs e )
        {
            try
            {
                e.DataSources.Add(
                    new Microsoft.Reporting.WebForms.ReportDataSource(
                        "CostDept",
                        Reporting.Repositories.ApprovalRoute.GetDetailApprovalRouteList(
                        Convert.ToInt32( e.Parameters[ "AccountNumberID" ].Values[ 0 ] ),
                         Convert.ToInt32( e.Parame开发者_JAVA百科ters[ "SageDatabaseID" ].Values[ 0 ] ),
                       Convert.ToInt32( e.Parameters[ "RequestingUserID" ].Values[ 0 ] ),
                        Convert.ToInt32( e.Parameters[ "ProjectID" ].Values[ 0 ] ),
                          Convert.ToInt32( e.Parameters[ "ProjectItemID" ].Values[ 0 ] ),
                        e.Parameters[ "DocumentType" ].Values[ 0 ].ToString() ) ) );
            }
            catch ( Exception ex )
            {
                ErrorLogging.LogError( ex );
            }
        }

I think I have got to the bottom of this. As the SQL server is on a seperate box than the web application a 'double hop' is caused when the sub report is processed which looses the identity context.

There are 2 work arounds I came up with.

1) Use UID and PWD in the sql connection strings so Integrated Security is not used

2) Change the Identity of the Application pool and anonymous user access in IIS to the same account that has permissions on the database. Then when the sub report is processed the identity of the application pool will have access and will load correctly.

I posted a mored detailed explanation on my blog: WraithNath

I ran into a similar situation importing a Chart into an image.

My chart was rendered via an aspx page, and had dynamic parameters. When running local, it worked fine. Deploying to our staging box... it broke. Same errors as the sub-report issue mentioned here.

I finally just removed the security from the chart rendering (we already used UID/PWD for all DB connections).

So I think this issue occurs with pretty much any "sub" task for the reporting control. Whether it's calling images, sub-reports or whatever else.