SharePoint 2016, SSRS and the Native Mode Report Viewer Web Part
A common SharePoint deployment scenario involves the use of the SQL Reporting Services Add-In to host “integrated mode” reports, with the report definition and data source files stored in document libraries and the SSRS services running on one or more application servers in the farm. This scenario provides an effective layer of integration between completely separate but complimentary products that often run side by side. Sometimes, however, SharePoint users need access to SSRS reports that exist on a server running in “native mode” somewhere in the customer’s environment. Rather than just putting a link to the report server on a page and forcing users to navigate away from SharePoint to view it, Microsoft provides a report viewer web part that allows remote reports to be embedded into site pages. A nice addition, to be sure, but one that can also cause a tremendous amount of problems in SharePoint 2016 if not implemented in a very precise manner.
The trouble often begins after the RVWP is deployed in an environment that is already running the RS add-in with provisioned SSRS services and service applications. After deploying the web part, attempts to access the system settings pages in Central Administration often result in the following error:
[A]Microsoft.ReportingServices.SharePoint.SharedService.Service.ReportingWebServiceApplication cannot be cast to [B]Microsoft.ReportingServices.SharePoint.SharedService.Service.ReportingWebServiceApplication. Type A originates from ‘Microsoft.ReportingServices.SharePoint.SharedService, Version=22.214.171.124, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ in the context ‘Default’
at location ‘C:\Windows\assembly\GAC_MSIL\Microsoft.ReportingServices.SharePoint.SharedService\126.96.36.199__89845dcd8080cc91\Microsoft.ReportingServices.SharePoint.SharedService.dll’. Type B originates from ‘Microsoft.ReportingServices.SharePoint.SharedService, Version=188.8.131.52, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ in the context ‘Default’
at location ‘C:\Windows\assembly\GAC_MSIL\Microsoft.ReportingServices.SharePoint.SharedService\184.108.40.206__89845dcd8080cc91\Microsoft.ReportingServices.SharePoint.SharedService.dll’.. (Correlation=978f5d9e-d8d7-3033-5573-8326f85371f2)
The above error will also be encountered when attempting to use any of the SSRS PowerShell commands, such as Get-SPRSServiceApplicationServers or Install-SPRSService. When this error occurs, the farm will no longer be able to serve SSRS reports in integrated mode. Users will receive a generic report server error when attempting to render any reports. A different (but ultimately related) System.IO.FileNotFoundException error may also be encountered in the ULS logs when the SQL Reporting Services Service is de-provisioned from Central Administration referencing the Microsoft.ReportingServices.SharePoint.NativeCrypto assembly. Any attempts to run the Configuration Wizard will result in the following error in the upgrade log:
An exception of type System.Reflection.TargetInvocationException was thrown. Additional exception information: Exception has been thrown by the target of an invocation.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.ReportingServices.Alerting.ServiceContract, Version=220.127.116.11, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
— End of inner exception stack trace —
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.Reflection.ConstructorInfo.Invoke(Object parameters)
at Microsoft.SharePoint.Administration.SPAutoSerializingObject.GetInstanceFromType(Type type, String typename)
at Microsoft.SharePoint.Administration.SPConfigurationDatabase.GetObject(Guid id, Guid parentId, Guid type, String name, SPObjectStatus status, Byte versionBuffer, String xml)
at Microsoft.SharePoint.Administration.SPConfigurationDatabase.FetchObject(Guid id)
at Microsoft.SharePoint.Administration.SPConfigurationDatabase.GetObject(Guid id, Boolean checkInMemoryCache, Boolean checkFileSystemCache, Boolean checkDatabase)
at Microsoft.SharePoint.Administration.SPConfigurationDatabase.ResolveObjectAndClassVersions(SPLog log)
at Microsoft.SharePoint.Upgrade.SPManager.BootStrap(Guid sessionId, SPUpgradeOperationFlags flags)
The end result is a partially corrupted SSRS implementation in which the native-mode RVWP functions correctly but all integrated mode SSRS services in the farm are non-functional. In this state, attempts to remove the reportviewerewebpart.wsp solution and return to the original pre-deployment state are also likely to fail, with a range of errors including “Cannot uninstall the LanguagePack 0 because it is not deployed” or a scheduled deploy/retract process that never completes. The RS add-in component can be successfully uninstalled; however, attempts to re-install it do not resolve the errors and services cannot be stopped on servers where they were previously running.
The root cause of these problems is a set of Microsoft.ReportingServices.* DLL’s included with the RVWP solution that are incompatible with the versions deployed when the RS add-in is installed. In a clean environment prior to installation of the RVWP the version number of assemblies such as Microsoft.ReportingServices.ServiceContract, Microsoft.ReportingServices.SharePoint.Common, and Microsoft.ReportingServices.SharePoint.SharedService in the Global Assembly Cache will be listed as 18.104.22.168. After the RVWP has been installed, duplicate entries will exist in the GAC with a version number of 22.214.171.124. This causes a conflict within the integrated mode services, leading to the errors listed above. In the official Microsoft documentation for the 2016 version of the Report Viewer Web Part, the Troubleshoot section specifically calls out these errors and prescribes the following remediation steps:
1. Remove the Report Viewer web part
2. Uninstall SSRS
3. Reinstall the Report Viewer web part
This guidance leaves a lot to be desired. For one, it does not indicate how to get the web part and the SSRS integrated mode services to co-exist. It also fails to describe the real scope of the problem, which is corruption of SSRS farm services. There is no mention of the fact that the mere existence of the RVWP assemblies will block the Configuration Wizard from running. Furthermore, it completely omits what steps to take in the (very likely) event that the reportviewerwebpart.wsp solution cannot be removed, thereby causing step one to fail with no instruction on where to go from there. Should you find yourself in this situation, there is a way out but unfortunately it requires a good deal of manual intervention.
To begin with, assuming that the first problem to solve is the inability to complete installation of a cumulative update or feature pack, the RVWP needs to be removed in order for the Configuration Wizard to run successfully. If it will not retract cleanly, the files and assemblies will have to be removed manually from each server in the farm that runs SSRS services and/or Central Administration. The first and easiest step is to remove the feature files using the Uninstall-SPFeature command (the Feature ID of the Report Viewer Web part is 07a12416-496a-4b0d-ad01-fc2f95c591c2):
Uninstall-SPFeature -Identity 07a12416-496a-4b0d-ad01-fc2f95c591c2
The 126.96.36.199 assemblies then need to be removed manually. There are several ways to do this – PowerShell, gacutil.exe, or via the file explorer. The latter is the most direct and least prone to error as the other methods run the risk of removing the wrong assembly version if not executed carefully; however, UAC prevents a simple “right-click and uninstall” operation. A simple workaround is to set the Run all administrators in Admin Approval Mode security setting to Disabled in the local security policy (assuming group policy does not block this change on the server). This method is described in the following document: https://docs.microsoft.com/en-us/windows/security/threat-protection/security-policy-settings/user-account-control-run-all-administrators-in-admin-approval-mode (NOTE: a reboot is required to put this change into effect and it should be reversed before putting the server back into production). Regardless of which method is used, the list of assemblies to remove is as follows:
Microsoft.ReportingServices.SharePoint.Common.resources (all language-specific instances)
Microsoft.ReportingServices.SharePoint.SharedService.resources (all language-specific instances)
Microsoft.ReportingServices.SharePoint.UI.DataVisualization.resources (all language-specific instances)
NOTE: Be sure to only remove the 188.8.131.52 versions of these assemblies. A reboot is not required but certainly won’t hurt.
Once the conflicting DLL’s have been removed, the RS add-in can be uninstalled. This is a necessary step for co-existence as the RS add-in must be installed AFTER the RVWP. Removal of the add-in will not remove the SSRS service application or associated databases – all data sources and associated credentials will remain intact (if this is a major concern, backup the encryption key and SSRS SA databases before removing the RS add-in). Once all of the affected servers in the farm are “clean” the Configuration Wizard (or, if you prefer, psconfig.exe) can be run to completion and the farm upgrade finalized.
To restore SSRS functionality, first re-deploy the reportviewerwebpart.wsp solution. This may take some time depending on the number of web applications and servers in the farm. This should be done with the RS add-in removed; however, if an older version of the RS add-in is installed (such as 13.0.1601.5) and will not uninstall cleanly, it can be left in place so long as an updated version (such as 13.1.4001.0) is installed later. Once the RVWP is installed, then install the latest version of the RS add-in – do not attempt this before a full deployment of the RVWP completes. If for some reason the RVWP solution deployment will not finish, then more drastic measures may be required (see below); until it does, do not attempt to install/re-install the RS add-in or the corrupted state will persist. Assuming that both the RVWP deployment and the RS add-in install are successful then the SSRS services, service application configuration pages and associated PowerShell commands should all be functional again.
If the RVWP deployment does not complete, then there is latent corruption in the farm that cannot be resolved by manually removing the assemblies and feature files. This is worst-case scenario for which the only resolution is removing and rejoining servers to the farm. First, manually remove all the conflicting assemblies as outlined above and uninstall the RS add-in. Then remove each server that hosts the Microsoft Foundation Web Application service except for one using PSCONFIG or the Configuration Wizard (if the farm follows MinRole deployment guidelines with separate Front-End and Application roles, then the one remaining server should be an Application server hosting Central Administration – not a Front-End server – in order to preserve the SSRS service application configuration). Deploy the reportviewerwebpart.wsp solution to a single web application; if it deploys successfully, proceed with deployment to the remaining web applications that require it. If it still will not deploy fully – STOP – then open a support case with Microsoft. This is an indication of deeper problems that must be resolved before continuing. Assuming a successful deployment, add servers back to the farm one at a time. After each one is added, check the solution store for deployment errors and check each server to ensure the Microsoft.ReportingServices.* assemblies are in the GAC. Once all affected servers are back in the farm, the RS add-in can be installed on them and the PowerShell commands (Install-SPRSService and Install-SPRSServiceProxy) can be executed to re-enable the SSRS services, returning the farm to a baseline functional state.
It is possible (though not yet certain) that future updates will also require the same removal-reinstallation process, which begs the question “Is it worth it?”. Or, more accurately, “Which is more important – native mode web parts or integrated mode reports?”. That question must be answered on a case by case basis; what is absolutely certain is that Microsoft has done a poor job of making the two compatible in 2016. Additional documentation would be helpful but even more helpful would be ensuring (and testing in real-world scenarios) that the output of the two different teams which produce each component work together before releasing them to customers. Alas, if that were so, much of the long-suffering SharePoint administrator’s tasks would be a whole lot easier across the board (User Profiles, PerformancePoint, Workflow, Distributed Cache, just to name a few) and good platform hygiene, as one oft-quoted voice of sanity in the SharePoint maelstrom often refers to it, might be the norm as opposed to the exception. Hope springs eternal.