SQL Server Reporting Services Subscriptions with custom security
I ran into an interesting 'gotcha' when working with an SSRS site that has a custom security extension implemented (instead of using the built in Windows authentication model, see the MSDN security extension example). The problem was that when users had set up a subscription through the SSRS web site that requested a copy of the report (such as in pdf or excel format), the subscription would fail to run. Looking in the SSRS I'd see error entries similar to the following:
ReportingServicesService!library!13!05/27/2008-14:00:16:: i INFO: Call to RenderFirst( '/Financial Reports/Virtual Bookstore Sales By Term' )
ReportingServicesService!library!13!05/27/2008-14:00:16:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.TypeInitializationException: The type initializer for 'Framework.SystemConfiguration' threw an exception. ---> System.ApplicationException: Can not find applicationConfigurationManagement section in app.config.
at Framework.ConfigurationManagement.ConfigurationManager.Read(String sectionName, String qualifierName)
at Framework.SystemConfiguration.LoadConnectionStrings()
at Framework.SystemConfiguration.Initialize()
at Framework.SystemConfiguration..cctor()
--- End of inner exception stack trace ---
at customReporting.Authorization.CheckAccess(String userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType catItemType, Byte[] secDesc, ReportOperation rptOper, String reportPath)
at Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32& executionOption, String& savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime& historyOrSnapshotDate, Byte[]& secDesc)
at Microsoft.ReportingServices.Library.RSService._GetReportParameters(ClientRequest session, String report, String historyID, Boolean forRendering, NameValueCollection values, DatasourceCredentialsCollection credentials)
at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
--- End of inner exception stack trace --
The error indicated that my custom security dll (customReporting) which utilizes a common library component for our data access and such (framework.dll) was not able to find a setting section in the app.config file. At first I thought it was something related to the web site configuration for custom security since that is where you have to set everything up to get your custom security extension to work. But, everything looked good there. Looking further I found that it is running the subscription process under the SQL Reporting Services windows service instance so it is actually not using the web configurations when executing the custom extension dll but it runs in the context of the service. So to fix this problem you need to add any configuration settings into the ReportServer\bin\ReportingServicesService.exe.config file. This is the configuration file that the SQL Reporting Services windows service is utilizing. Once I added my config information to that file subscriptions worked like a charm!