asp.net - ReportViewer - Subreport processing impersonation context -

i have reportviewer report sub report. running locally report works fine, when deploy server sql access denied error local user on remote machine. eg

access denied servername\servername$

i have feeling due me firing sql command data required sub report , when deployed on server report viewer firing command , becuase of connection strings using integrated security (for security reasons) not got correct impersonation context.

my database access class in different assembly reporting assembly, both of these assemblies referenced web application

it seems report viewer calling sub report processing event when needs data sub report. web application impersonates user when runs sub report processing event not use specified user. instead perhaps local system account instead, , connection strings using integrated security local system account not have access database on server.

anyone else have problem?

here code 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 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.parameters[ "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 have got bottom of this. sql server on seperate box web application 'double hop' caused when sub report processed looses identity context.

there 2 work arounds came with.

1) use uid , pwd in sql connection strings integrated security not used

2) change identity of application pool , anonymous user access in iis same account has permissions on database. when sub report processed identity of application pool have access , load correctly.

i posted mored detailed explanation on blog: wraithnath