cannot create new subscription

Question

I get rsInternalError...

 

It used to work, but I noticed that my existing email subscriptions stopped working around November...I only have a few...no one complained until now. We also just finished migrating to Exchagne 2k7. I changed the smtp settings in SSRS and the existing subscriptions seem to be mailing again.(one came today to a user who commented on it)(The email subscription issue is probably a totally separate issue related to major config changes in our mail routing etc.)

 

However, I cannot create any new subscriptions without getting an error. I'm not sure when it started(maybe after SP2?)...a user complained that they could run reports but couldn't generate a PDF for a long running report >15 mins at least and I started investigating the issue.

 

I looked on some lists and did notice that the SSRS account was not a in the MASTER and MSDB RSExecRole. The role existed in both dbs, but the account wasn't added...not sure if it ever was or if something changed...it is added now. I've restarted the server, etc.

 

The log entries include(what's kind of weird is that the security log is clean, using windows auth only on sql server, audit failed logins, but tracing the errors below says something about an access denied on 'xp_sqlagent_notify'

 

Application Log:

Event ID: 108 Report Server (MSSQLSERVER) cannot load the Report Server DocumentLibrary extension.

 

ReportServer____...Log

w3wp!library!1!04/18/2008-14:53:21:: Call to GetSystemPermissionsAction().
w3wp!library!6!04/18/2008-14:53:21:: Call to GetPermissionsAction(/Microsoft Operations Manager Reporting/Microsoft Windows Base Operating System/Disk Performance Analysis).
w3wp!library!6!04/18/2008-14:53:21:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation  is not supported on a report server that runs in native mode., ;
 Info: Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation  is not supported on a report server that runs in native mode.
w3wp!extensionfactory!6!04/18/2008-14:53:21:: e ERROR: Exception caught instantiating Report Server DocumentLibrary report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.SharePoint.SharePointDeliveryExtension.DocumentLibraryProvider' threw an exception. ---> Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation  is not supported on a report server that runs in native mode.
   --- End of inner exception stack trace ---
   --- End of inner exception stack trace ---
   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
   at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
   at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.CreateExtensionObject(Extension extConfig).
w3wp!library!5!04/18/2008-14:53:21:: Call to GetReportParametersAction(/Microsoft Operations Manager Reporting/Microsoft Windows Base Operating System/Disk Performance Analysis).
w3wp!library!5!04/18/2008-14:53:22:: Call to ListSchedulesAction().
w3wp!library!5!04/18/2008-14:53:22:: 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.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

 

ReportServerWebApp____...log

 

w3wp!library!5!4/18/2008-14:52:53:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'  because it was not specified in Configuration file.
w3wp!library!5!4/18/2008-14:52:53:: i INFO: Initializing SecureConnectionLevel to default value of '1'  because it was not specified in Configuration file.
w3wp!library!5!4/18/2008-14:52:53:: i INFO: Initializing DisplayErrorLink to 'True'  as specified in Configuration file.
w3wp!library!5!4/18/2008-14:52:53:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False'  because it was not specified in Configuration file.
w3wp!extensionfactory!7!4/18/2008-14:53:22:: w WARN: The extension Report Server Email does not have a LocalizedNameAttribute.
w3wp!extensionfactory!7!4/18/2008-14:53:22:: w WARN: The extension Report Server FileShare does not have a LocalizedNameAttribute.
w3wp!ui!7!4/18/2008-14:53:22:: e ERROR: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListSchedules(String Site, Schedule[]& Schedules)
   at Microsoft.ReportingServices.WebServer.ReportingService2005.ListSchedules(Schedule[]& Schedules)
w3wp!ui!7!4/18/2008-14:53:22:: e ERROR: HTTP status code --> 200
-------Details--------
System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ListSchedules(String Site, Schedule[]& Schedules)
   at Microsoft.ReportingServices.WebServer.ReportingService2005.ListSchedules(Schedule[]& Schedules)

   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

   at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSchedules()

   at Microsoft.SqlServer.ReportingServices2005.RSConnection.ListSchedules()

   at Microsoft.ReportingServices.UI.SharedScheduleDropDown.EnsureSchedulesAreLoaded()

   at Microsoft.ReportingServices.UI.SharedScheduleDropDown.SharedScheduleDropDown_Load(Object sender, EventArgs e)

   at System.Web.UI.Control.OnLoad(EventArgs e)

   at System.Web.UI.Control.LoadRecursive()

   at System.Web.UI.Control.LoadRecursive()

   at System.Web.UI.Control.LoadRecursive()

   at System.Web.UI.Control.LoadRecursive()

Answer 1

this may be one of those cases where as soon as you admit to yourself that you need help, you figure it out...anyway, I followed the lists, reading them again, and adding NETWORK SERVICE to the RSExecRole in MASTER and MSDB seems to have allowed me to create a subscription again...it used to work so I'm really wondering why or what changed...maybe SP2 or another hotfix...only other issue is that installing Visual Studio SP1 (for SQL tools) has always been problematic...not sure what else would have had anything to do with these roles, etc.?

 

Is giving NETWORK SERVICE these rights safe? Is that the correct answer as someone on the list in another thread suggested?

 

Forrest