SQL Server – Reporting Services – Error – “Invalid or Expired Session”

Background

Reviewing collected “Extended Events” and noticed a recurring error.

Error

Image

Tabulated

  1. Message :- Invalid or Expired Session: [session id]
  2. Event Name :- error_reported
  3. Error Number :- 50000
  4. Severity :- 16
  5. session_server_principal_name :- Web Site’s Application Pool Identity
  6. server_principal_name :-
  7. is_system :- false
  8. database name :- ReportServer
  9. client_app_name :- Report Server

TroubleShooting

Database

ReportServerTempDB

Table

ReportServerTempDB.dbo.SessionData
Reviewing date of entries
Reviewing date of entries – SQL

SELECT

        [ExpirationMin] 
		  = min 
				(
					[Expiration]
				)
      
      , [ExpirationMax] 
		= max 
			(
				[Expiration]
			)
      
      , [durationBetweenMinAndMax]
			= datediff
				(
					  minute
					, min ([Expiration])
					, max ([Expiration]) 
				)

      , [durationBetweenMinAndCurrent] 
		  = datediff
				(
					  minute
					, min ([Expiration])
					, getdate() 
				)

      , [durationBetweenMaxAndCurrent] 
		= datediff
			(
				  minute
				, max ([Expiration])
				, getdate() 
			)

FROM [dbo].[SessionData] tblSD


Explanation
  1. The entries returned will have duration reflecting the settings set for session recycling.

Remediate

Configuration

Configuration Files

rsreportserver.config

Item :- Configuration \ CleanupCycleMinutes
Default

The default setting for CleanupCycleMinutes is 10.

This translates to entries in the ReportServerTempDB been pruned every 10 minutes.

Revised

Please increase timeout.

Doing so will mean that records are recycled less frequently.

Image – Default

 

Image – Revised

Explanation

Change CleanupCycleMinutes from 10 minutes to 180 minutes, 3 hours.

Reporting Services – v2016 – HTTP Error 500

Error

Error – Browser

500

Image

TroubleShooting

LogFiles

ReportServerServices

Image


appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:54:: i INFO: Appdomain STARTED: id='3'; name='ReportServer_MSSQLSERVER_0-1-131535149338364464'
appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:54:: e ERROR: AppDomain ReportServer_MSSQLSERVER_0 failed to start. Error: Unable to read the security policy file for trust level 'RosettaSrv'.
library!DefaultDomain!24e4!10/26/2017-11:08:54:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: Failed to create HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportServer_MSSQLSERVER_0.  ---> System.Configuration.ConfigurationErrorsException: Unable to read the security policy file for trust level 'RosettaSrv'.
   at System.Web.HttpRuntime.SetTrustLevel(TrustSection trustSection, SecurityPolicySection securityPolicySection)
   at System.Web.HttpRuntime.HostingInit(HostingEnvironmentFlags hostingFlags, PolicyLevel policyLevel, Exception appDomainCreationException)
   at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException)
   at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel)
   at System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel)
   at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironment(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)
   at System.Web.Hosting.ApplicationManager.CreateAppDomainWithHostingEnvironmentAndReportErrors(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)
   at System.Web.Hosting.ApplicationManager.GetAppDomainWithHostingEnvironment(String appId, IApplicationHost appHost, HostingEnvironmentParameters hostingParameters)
   at System.Web.Hosting.ApplicationManager.CreateObjectInternal(String appId, Type type, IApplicationHost appHost, Boolean failIfExists, HostingEnvironmentParameters hostingParameters)
   at System.Web.Hosting.ApplicationManager.CreateObject(String appId, Type type, String virtualPath, String physicalPath, Boolean failIfExists, Boolean throwOnError)
   at ReportingServicesHttpRuntime.RsHttpRuntime.Create(RsAppDomainType type, String vdir, String pdir, Int32& domainId)
   --- End of inner exception stack trace ---;
appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:54:: e ERROR: AppDomain ReportServer error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportServer_MSSQLSERVER_0.  ---> System.Configuration.ConfigurationErrorsException: Unable to read the security policy file for trust level 'RosettaSrv'..
appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:54:: e ERROR: AppDomain ReportServer id 3 ('ReportServer_MSSQLSERVER_0-1-131535149338364464') was created. Unloading it...
appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:54:: i INFO: UnloadAspDomain ReportServer: 3, 'ReportServer_MSSQLSERVER_0-1-131535149338364464'
appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:55:: i INFO: SetUnloadStarted AppDomain id '3': unloadStarted = 1
appdomainmanager!DefaultDomain!24e4!10/26/2017-11:08:55:: i INFO: Appdomain:3 ReportServer_MSSQLSERVER_0-1-131535149338364464 unloading.
library!DefaultDomain!24e4!10/26/2017-11:08:55:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerAppDomainManagerException: Failed to create Report Server HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerAppDomainManagerException: An error occurred when attempting to start the application domain ReportServer within the Report Server service. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportServer_MSSQLSERVER_0.  ---> System.Configuration.ConfigurationErrorsException: Unable to read the security policy file for trust level 'RosettaSrv'.

Textual

  1. Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: Failed to create HTTP Runtime, Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeInternalException: An internal or system error occurred in the HTTP Runtime object for application domain ReportServer_MSSQLSERVER_0. —> System.Configuration.ConfigurationErrorsException: Unable to read the security policy file for trust level ‘RosettaSrv’.
    at System.Web.HttpRuntime.SetTrustLevel(TrustSection trustSection, SecurityPolicySection securityPolicySection)

 

Remediation

Reporting Server Services

Configuration

Configuration File

web.config
web.config – Before Change

Here is what our configuration file looked like before the change.

Image

Code

    <securityPolicy>
      <trustLevel name="RosettaSrv" policyFile="rssrvpolicy.config" />
    </securityPolicy>
    <trust level="RosettaSrv" originUrl="" />

Explanation
  1. We can see that the RosettaSrv section is commented out

 

web.config – Revised

Re-enabled the commented out section

Image

Code
    <securityPolicy>
      <trustLevel name="RosettaSrv" policyFile="rssrvpolicy.config" />
    </securityPolicy>
	
	
	<trust level="RosettaSrv" originUrl="" legacyCasModel="true" />

Explanation
  1. We have re-enabled the RosettaSrv section

 

Compare Files

There are many online tools for comparing files.

Mergely

Here is what things look like from the fine mergely tool.

Summary

Almost a year ago commented out the Rosetta section.

That was SQL Server v2014.

Now we are v2016, that Rosetta section is required.

Dedicate

Dedicating to our internal Engineering Platform team.

Because of the VMWare backup they take was able to compare the before and after versions of the Reporting Services Configuration Files.

Office365 – Email Connectivity Testing through Mozilla Thunderbird

Background

Earlier this weekend got an email that analysts were no longer receiving emails that we had subscribed them to via Microsoft Reporting Services.

Troubleshooting

Log Files

Checked the various log files ( Reporting Services, IIS SMTP).

BTW, we are using IIS SMTP as a Smarthost Relay.

The logs were not illuminating.

 

Mozilla Thunderbird

Tried the same configuration with slight variations.  Actually many more times than I can remember.  And, definitely much more than I will admit.

 

Google

That chick named Google is your friend and so looked for how to configure email clients to use Office 365.

Found some good leads, but nothing worked out.

Configuration

Process

Here is the process we undertook to configure Mozilla Thunderbird to use Microsoft Office 365.

  1. Launch Mozilla Thunderbird
  2. Choose the Account
    • Right click on the Account you have selected
    • And, from the dropdown menu, choose “Settings
  3. Account Settings
    • The “Account Settings” window appears
    • At the bottom of the “Account Settings” window we will observe the “Account Actions” panel
    • One of the choices available in the “Account Actions” panel is the “Add Mail Account” option
    • Please choose the “Add Mail Account” option
  4. “Mail Account” Setup
    • The “Mail Account Setup” window appears
    • Initial Screen
      • It is prefilled with your current system’s full name
      • Please enter your full email address
      • And, password
      • Once entered, please press the Continue button
    • Suggested Configuration
      • Based on the email address entered, Mozilla retrieves the domain name
      • The registered DNS Provider for the domain name is contacted
      • And, asked for MX record
      • Using the MX record, the servers registered for Mail are then contacted
      • If the Mail providers are able to provide mail registration data, communication is started with them
    • Manual Configuration
      • Please click on the manual config button to review or adjust configuration data

Images

Mail Account Setup
Access Account Configuration

Account Settings

Account Settings

Mail Account Setup – 01

Mail Account Setup – 02

Mail Account Setup – Looking up Configuration – Email Provider

Mail Account Setup – Looking up Configuration – Email Provider – Configuration found in Mozilla ISP database

Mail Account Setup – Manual Configuration

Configuration Results

Flow Protocol Server hostname Port SSL Authentication
Incoming IMAP imap-mail.outlook.com 993 SSL/TLS Normal password
Outgoing SMTP smtp-mail.outlook.com 587 STARTTLS Normal password

 

Network Monitoring

Microsoft Network Monitor

Once we were satisfied that we were able to establish communication between our host and the Mail Server, we used Microsoft Network Monitor to review the traffic.

Here is the filters we employed and what we captured via “Microsoft Network Monitor“.

Filter

Textual

ProcessName.Contains(“thunder”)

Image

Network Conversations

 

References

  1. Microsoft
    • Technet
      • Network Monitor Conversation Filtering
        Link

Summary

A good solution was so far away until we discovered that Mozilla Thunderbird is able to communicate with the Provider and request configuration data.

Reporting Services – Max Memory

Background

Reviewing one of our SQL Server Instances and noticed this entry in the SQL Server Error Log.

ErrorLog

Image

Textual

2017-10-02 09:23:00.29 Server Detected 16371 MB of RAM. This is an informational message; no user action is required.
2017-10-02 09:23:00.29 Server Using locked pages in the memory manager.
2017-10-02 09:23:01.42 Server Large Page Allocated: 32MB
2017-10-02 09:23:01.99 Server Cannot use Large Page Extensions: Failed to allocate 32MB

 

TroubleShooting

Resource Monitor

Image

Explanation

  1. Total
    • 97% Used Physical Memory
    • 428 MB available
  2. Processes
    • ReportingServicesService.exe
      • Commit :- 2,495,860 KB ( 2.495 GB )
      • Working Set :- 2,243,072  KB ( 2.495 GB )
      • Sharable :- 52,700 KB ( 50 MB )
      • Private :- 2,190,372 KB ( 2.190 GB )
    • sqlservr.exe
      • Commit :- 795,204 KB ( 795 MB )
      • Working Set :- 123,712  KB ( 123 MB )
      • Sharable :- 32,376 KB ( 32 MB )
      • Private :- 90,378 KB ( 90 MB )

Remediation

Overview

  1. Identify location where Reporting Services is located
    • We can use Control Panel, Services applet
      • “C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe”
        • MSRS12
          • SQL Server 2014
  2. Make backup of rsreportserver.config
  3. Edit rsreportserver.config
    • Add or amend
      • WorkingSetMaximum
        • Syntax
          • <WorkingSetMaximum>ValueInKB</WorkingSetMaximum>
        • Sample
          • <WorkingSetMaximum>1000000</WorkingSetMaximum>
            • 1000000 KB is 1 GB

 

Services Applet

 

rsreportserver.config

WorkingSetMaximum

Overview

Here we set maximum memory for Reporting Services to 1000000.

Value is in KB.

And, 1000000 KB translates to 1 GB.

 

Textual


<!-- Added by dadeniji on 2017-10-05 10:18 AM -->
<WorkingSetMaximum>1000000</WorkingSetMaximum> 

Image

 

Confirm

Restart MS Reporting Services and review through Log files and Resource Monitor.

ReportServerService*.log

Image

Textual

library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!a5c!10/05/2017-10:17:28:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.

Explanation

  1. MemorySafetyMagin
    • Initializing MemorySafetyMargin to ’80’ percent as specified in Configuration file.
  2. MemoryThreshold
    • Initializing MemoryThreshold to ’90’ percent as specified in Configuration file.

 

Resource Monitor

Image

Explanation

  • Total
    • 85% Used Physical Memory
    • 2031 MB available
  • Processes
    • ReportingServicesService.exe
      • Commit :- 271,732 KB ( 270 MB )
      • Working Set :- 138,360 KB ( 140 MB )
      • Sharable :- 32,512 KB ( 34 MB )
      • Private :- 104,168 KB ( 104 GB )

Summary

We are satisfied with the change we made.

 

References

  1. CSS SQL Server Engineers
    • PSSQL
      • SQL Server and Large Pages Explained….
        Link
    • Adam W. Saxton
      • Troubleshooting Memory Issues with Reporting Services
        Link

Sapien – WMI Explorer – Reporting Services Configuration Manager

Background

Wanted to utilize another WMI Query tool in addition to Microsoft’s own WMI tools.

 

Lineage

Posts

SQL Server Reporting Services (SSRS) – Error – “Invalid Class”

In the “SQL Server Reporting Services (SSRS) – Error – Invalid Class” post, we bemoaned the difficulty of fully espousing our wilderness experience based on screenshots from a single tool; that tool being Microsoft WMI Explorer.

The post is here.

Microsoft’s WMI Explorer is available at Codeplex and here is the specific URL.

 

Sapien

We wanted to try out one more tool and the one we chose is Sapien WMI Explorer.

Download

Please download WMI Explorer from here.

The current version is 2.2.74.

Requirements

  1. OS Version
    • Desktop :- Windows 7 / Windows 8 / Windows 8.1 / Windows 10
    • Server :- Windows Server 2008 R2 / Windows Server 2012 / Windows Server 2012 R2
  2. OS Bitness
    • 32 and 64 bit
  3. Powershell
    • Version :- Powershell Version 3.0
  4. Visual Studio 2012 Runtime

 

Install

Installation is straightforward.

Register

Connect to the Vendor’s web site and request a trial key.

 

Usage

Launch Sapien’s WMI Explorer and navigate the Class Browser tree.

As one chooses a specific Namespace, the classes and corresponding properties and methods for that class are shown in the right window.

We are interested in SQL Server Namespace and specifically the ReportServer space.

Please click on the Query button on the Ribbon Tab to view and edit queries.

Image

Pasted below are the images captured from our journey.

Hierarchy

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>
Instance – Default

Instance – DATACAP

 

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>\<Version>
Instance – {RS_DATACAP }\ Version {v12}

Instance – {RS_DATACAP }\ Version {v12} – Custom Query

Instance – {RS_DATACAP }\ Version {v12} – Query Results

Hierarchy – \\<Host>\ROOT\Microsoft\SqlServer\ReportServer\<Instance>\<Version>\Admin
Instance – {RS_DATACAP }\ Version {v12} \ Admin – Custom Query

Instance – {RS_DATACAP }\ Version {v12} \ Admin – Query Results

 

WMI – Reporting Services Configuration Manager – Event Viewer

Background

When troubleshooting Reporting Services Configuration Manager WMI calls it can be useful to see whether WMI activities are occurring.

 

TroubleShooting

Event Viewer

Enable Log

WMI calls are logable in the Event Viewer.

But, they are not logged by default.

To enable logging please follow the steps listed below:

Obtaining WMI Events Through Event Viewer
Link

  1. Open Event Viewer. On the View menu, click Show Analytic and Debug Logs
  2. Locate the Trace channel log for WMI under Applications and Service Logs | Microsoft | Windows | WMI Activity
  3. Right-click the Trace log and select Log Properties. Click the Enable Logging check box to start the WMI event tracing
  4. WMI events appear in the event window for WMI-Activity. Double-click an event in the list to see the detailed information. You can view an event in XML View or in Friendly View format.

Logged Events

Tabulate

# Event
1 GroupOperationId = 159602; OperationId = 159602; Operation = IWbemServices::Connect; ClientMachine = QADB; User = dadeniji.adeniji; ClientProcessId = 5024; NamespaceName = \\QA\root\Microsoft\SqlServer\ReportServer
2 GroupOperationId = 159603; OperationId = 159604; Operation = Start IWbemServices::CreateInstanceEnum – __NAMESPACE; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer
3 GroupOperationId = 159605; OperationId = 159605; Operation = IWbemServices::Connect; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\QADB\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
4 GroupOperationId = 159606; OperationId = 159607; Operation = Start IWbemServices::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ClientMachine = QADB; User = daniel.adeniji; ClientProcessId = 5024; NamespaceName = \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
5 ProviderInfo for GroupOperationId = 159606; Operation = Provider::CreateInstanceEnum – MSReportServer_ConfigurationSetting; ProviderName = ReportingServicesWMIProvider; ProviderGuid = {0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF}; Path = C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Explanation

  1. Event #1 :- IWebServices Create Instance Enum
    • NamespaceName :- \\QA\root\Microsoft\SqlServer\ReportServer
  2. Event #2 :- IWebServices Create Instance Enum
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer
  3. Event #3 :- IWebServices Connect
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  4. Event #4 :- IWebServices Instance Enumerate
    • NamespaceName :- \\.\root\Microsoft\SqlServer\ReportServer\RS_DATACAP\v12\Admin
  5. Event #5 :- Com Object Instantiated
    • COM Object ID :- 0A0B6A3E-DAA2-4ED9-A603-B1C4ED9515FF
    • COM File :- C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll

 

Summary

From logging WMI Calls we are able to see the inner workings of the WMI Provider class and IWbemServices interface.

In the case of Sql Server Reporting Services (SSRS) it is an version specific dll ( C:\Program Files (x86)\Microsoft SQL Server\120\Shared\reportingserviceswmiprovider.dll ).

SQL Server Reporting Services (SSRS) – Error – “Invalid Class”

Background

As part of a much larger work done that we are undertaken, got swallowed into a Windows Management Instrumentation ( WMI ) quicksand.

 

Manifestation

The errors stands up upon launching SQL Server Reporting Services ( SSRS ) and trying to connect to one of the local instances.

 

Error

Report Services Configuration Connection

Launch

Image

SSRS_Connect_20170816_0744PM

 

Explanation

  1. Report Server Instance dropdown
    • empty & greyed out
  2. Connect button
    • disabled

Find

Upon clicking the find button

Image

InvalidClass_20170816_0307PM

 

Explanation

  1. When we clicked the find button to “discover” SSRS Instances on the entered host, we received the error message “Invalid class

 

Troubleshooting

 

WMI Explorer

WMI Explorer allows us to simulate the same WMI queries made by the Application.

 

Clarification

  1. We have 2 SQL Server Instances on the box
    • A named instance, Datacap
    • A default instance
  2. Instances
    • Instance – Datacap
      • Upgrade History :- Recently upgraded
      • Edition :- Enterprise Edition :- Core-Based Licensing
    • Instance – Default
      • Upgrade History – Left as is

 

Images

The screen shot below is what we captured as we navigated the Namespace.

 

Version – DATACAP – v10

 

wmiExplorer_DataCap_v10__20170816_0711PM

Explanation
  1. Tab :- Instances ( 0 )

 

Version – DATACAP – v12

wmiExplorer_DataCap_20170816_0705PM

 

Explanation
  1. Tab :- Instances ( 1 )
    • Instances :- MSReportServer_Instance.Instance Name=’DATACAP’
    • Edition :- Enterprise Edition : Core-Base Licensing
    • InstanceID  :- MSR12.DATACAP
    • IsSharePointIntegrated :- False

 

Version – DEFAULT – v10

wmiExplorer_DefaultInstance_v10DOT15_20170816_0711PM [BrushedUp]

Explanation
  1. Tab :- Instances ( 1 )
    • Instances :- MSReportServer_Instance.Instance Name=’MSSQLSERVER’
    • Edition :- Enterprise Edition
    • InstanceID  :- MSR10_15.MSSQLSERVER
    • IsSharePointIntegrated :- False

 

Summary

Admittedly it is difficult to fully tell of our experiences from a single tool along with a couple of screenshots.

Yet in summary it seems Report Services Configuration Manager is crippled due to one missing instance.

Later we will come back and talk about other diagnostics tools one can use to debug WMI difficulties.