Sql-server – SSRS 2016 subscriptions error – Something went wrong. Please try again later

sql serverssrsssrs-2016

I have a SSRS 2016 instance which gives me below error whenever I try to view my existing report subscriptions:

An error has occurred. Something went wrong. Please try again later.

Strange thing is subscriptions are working fine and I can even create new subscriptions. It only errors out while viewing subscriptions on report server. When checked in database subscriptions entries are there in the table. This is happening on my PROD environment but not on DEV environment. I tried to replicate this same error in DEV but didn't succeed. Report Server config, policy and web config are same on both PROD and DEV. I am wondering why it is happening only on PROD but not in DEV. Below errors are logged in Report Server logs when this error occurs:

Microsoft.ReportingServices.Portal.WebHost!extensionfactory!57!12/20/2019-13:39:30:: e ERROR: Exception caught instantiating Report Server Email report server extension: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.  ---> System.IO.FileNotFoundException: Could not load file or assembly 'ReportingServicesEmailDeliveryProvider' or one of its dependencies. The system cannot find the file specified..
Microsoft.ReportingServices.Portal.WebHost!library!57!12/20/2019-13:39:30:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Unable to load assembly ReportingServicesEmailDeliveryProvider, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.  ---> System.IO.FileNotFoundException: Could not load file or assembly 'ReportingServicesEmailDeliveryProvider' or one of its dependencies. The system cannot find the file specified.
   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.Assembly.Load(AssemblyName assemblyRef)
   at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssemblyFromFile(String name)
   --- End of inner exception stack trace ---;
Microsoft.ReportingServices.Portal.WebHost!extensionfactory!57!12/20/2019-13:39:30:: e ERROR: Exception caught instantiating Report Server Email report server extension: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.  ---> System.IO.FileNotFoundException: Could not load file or assembly 'ReportingServicesEmailDeliveryProvider' or one of its dependencies. The system cannot find the file specified..
Microsoft.ReportingServices.Portal.WebHost!reportserverwebapp!57!12/20/2019-13:39:30:: e ERROR: [4ti0n45o]: OData exception occurred: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Sequence contains no matching element
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source, Func`2 predicate)
   at Microsoft.ReportingServices.Portal.Repositories.SystemService.PopulateLocalizedExtensionNames(IPrincipal userPrincipal, List`1 subscriptions)
   at Microsoft.ReportingServices.Portal.Repositories.CatalogItemRepository.GetSubscriptions(IPrincipal userPrincipal, String itemPath)
   at Model.ReportRepository.LoadSubscriptions()
   at Model.Report.get_Subscriptions()
   at lambda_method(Closure , ReportRepository )
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Delegate.DynamicInvokeImpl(Object[] args)
   at System.Web.OData.Query.Expressions.SelectExpandBinder.Bind(Object entity)
   at System.Web.OData.Query.SelectExpandQueryOption.ApplyTo(Object entity, ODataQuerySettings settings, IAssembliesResolver assembliesResolver)
   at System.Web.OData.Query.ODataQueryOptions.ApplySelectExpand[T](T entity, ODataQuerySettings querySettings)
   at System.Web.OData.Query.ODataQueryOptions.ApplyTo(Object entity, ODataQuerySettings querySettings)
   at System.Web.OData.EnableQueryAttribute.ExecuteQuery(Object response, HttpRequestMessage request, HttpActionDescriptor actionDescriptor)
   at System.Web.OData.EnableQueryAttribute.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
   at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext().

The above errors tells me that it couldn't find ReportingServicesEmailDeliveryProvider assembly/dll. However I have checked in my Report Server's \bin directory and the ReportingServicesEmailDeliveryProvider.dll already exists and I have also copied it to the public and private assembly folders of Visual Studio. But still the error happens. Also, along with this error I get one more error at the same time in Event Viewer Security log:

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          20/12/2019 13:39:30
Event ID:      4673
Task Category: Sensitive Privilege Use
Level:         Information
Keywords:      Audit Failure
User:          N/A
Computer:      Test.corp.com
Description:
A privileged service was called.

Subject:
Security ID:  Corp\Test
Account Name:  svctest
Account Domain:
corp
Logon ID:  0x254342CE

Service:
Server:  Security
Service Name:  -

Process:
Process ID:  0x2544
Process Name:  C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\RSWebApp\Microsoft.ReportingServices.Portal.WebHost.exe

Service Request Information:
Privileges:  SeTcbPrivilege

Not sure if these errors are related. Can someone please help in finding the root cause and fix for this issue?

Answers to John's questions:

1) Timings does match and I get the same error in Microsoft.ReportingServices.Portal.WebHost_%M_%D_%Y_%H_%M_%S.log file of could not load file or assembly whenever I click on subscriptions in SSRS portal.

2) I did that already. Everything is same between PROD and DEV as far as rsreportserver.config is concerned. In addition to this, I did compare web.config and rssvrpolicy.config as well and both of them do match as well.

Also to reproduce the scenario as a test, I replaced entire contents of PROD SSRS bin directory to DEV SSRS bin directory but it was still working in DEV and there were no errors.

Please let me know if any further details are needed.

Best Answer

Note: My initial response is basically just a couple of questions, so for the time being, treat this answer as a comment too large to fit into the comments section. As a result, it will likely change over time.

Questions I'd appreciate you answering if you have the time/capacity, are as follows:

Can you confirm that the time you are seeing the error on the site and the errors you're seeing in the logs are occurring at or around the same time?
- It's not that I don't believe you, but SSRS is good at throwing out useless error messages, so it's always best to confirm the timing matches up instead of just looking for the first error that pops in the logs. If the times are not matching up, can you check the Microsoft.ReportingServices.Portal.WebHost_%M_%D_%Y_%H_%M_%S.log logs around those times and see if those logs are capturing any errors around the appropriate time?
Can you cross-check your rsreportserver.config files between your DEV and PROD environments?
- The error popping in your logs, Exception caught instantiating Report Server Email report server extension makes me suspect you've got something wrong with your rsreportserver.config file as that is the location of the Report Server Email extension (this will be buried within an <Extension /> element within the file). If Dev works and Prod doesn't the quickest thing I can suggest is make sure the settings match between these two environments for this extension. It's possible you've got an extra or malformed element/value in the Prod .config file that is throwing things off.

In my experience, SSRS likes to throw a lot of red herrings into the error logs, so before you start digging into .dlls, check to make sure you're just not running up against an improper config setting first.

Let me know what you find.

Related Solutions

Sql-server – .NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

An error in the form of:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id #####. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly '{_assembly_name_}, Version=#.#.#.#, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxxx' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

means that the Assembly, which is currently marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE is not allowed to make use of that permission level until the second part of the SQLCLR permissions setup is taken care of. That second part is to do one of the following:

The very much preferred approach
1. Sign the Assembly when compiling it (and give it a password!). This is sometimes referred to as giving it a Strong Name.
  
  If the Assembly isn't signed, and you don't have the source code to recompile it, and it doesn't have any other Assemblies that reference it, then you can still sign it by following the instructions in the first half of this blog post: http://ianpicknell.blogspot.com/2009/12/adding-strong-name-to-third-party.html
2. Create an Asymmetric Key in [master] from the DLL of that Assembly
3. Create a Login from that Asymmetric Key
4. Grant that Login one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (the UNSAFE ASSEMBLY permission also allows Assemblies to be marked as EXTERNAL_ACCESS, so you don't need both permissions)
5. Create the Assembly in whichever Database(s) it should exist in
6. Do NOT set the TRUSTWORTHY property of the Database(s) containing this Assembly to ON. TRUSTWORTHY can remain as OFF.
The NOT preferred approach
1. Alter the Database(s) where the Assembly should exist to be TRUSTWORTHY ON
2. Make sure that the Login of the owner of the Database(s) containing this Assembly have one of these two permissions: EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY.
  
  If the owner of the Database(s) is sa, or any other Login that is in the sysadmin fixed server Role, then you don't need to worry about having one of these two permissions explicitly set as they are implied by the sysadmin Role.
3. Try to avoid this approach, if at all possible :-)

Sql-server – SSRS and Managed Service Account – impossible to configure

Yes you can run SSRS with a Managed Service Account.

I would double check that your MSA is installed on your SSRS server. You can do so with Powershell.

Get-ADServiceAccount -identity msaname -properties hostcomputers |select hostcomputers

If your SSRS server is not listed, you will need to install the MSA on the server. This needs to be run locally (on SSRS server). You MUST be domain admin to run Install-ADServiceAccount successfully.

Install-ADServiceAccount msaname

Best Answer

Related Solutions

Sql-server – .NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

Sql-server – SSRS and Managed Service Account – impossible to configure

Related Question