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.

SSRS – Validating Smart Host – Using Mozilla Thunderbird

Background

A few posts ago, we provisioned a Smart Host.

Let us quickly test it out.

 

Lineage

Here are other posts in this multi-series:

  1. SSRS – Setting up Smart Host
    Link

Scenario

To make sure that we understand the specificity of Microsoft’s Office 365 SMTP service, we will install a free, durable email client; specifically Mozilla Thunderbird.

 

Wireshark

Filter

  1. tcp.port == 25 || tcp.port == 465 || tcp.port == 587

Mozilla Thunderbird

Download

Download Mozilla Thunderbird from here.

Installation

Installation is straightforward.

Configuration

Images

Session – 1

Configuration
SMTP Server Settings

 

Output
Netstat

WireShark

 

Session – 2

Configuration
SMTP Server Settings

Output
Netstat

WireShark

WireShark – SMTP ( Destination Port 587 ) – 01
WireShark – Explanation
  1. Connection to SMTP Host
WireShark – SMTP ( Destination Port 587 ) – 02

WireShark – Explanation
  1. Reply from SMTP Host

 

Session – 3

Configuration
SMTP Server Settings

WireShark

WireShark – Explanation
  1. We see a fuller conversation

 

Item Port :- 465 /
Connection :- SSL/TLS
Port :- 587 /
Connection Start :- SSL/TLS
Port :- 587 /
Connection Start :- STARTSSL
Server Name smtp.office365.com smtp.office365.com smtp.office365.com
Port 465 587 587
Connection Security SSL/TLS SSL/TLS STARTTLS
Authentication Password Normal Password Normal Password Normal Password
Results SYN/SENT  Truncated Conversation  Full Conversation

 

Network Ports

  1. Port :- 25
    • SMTP
      • Server to Server email
        • Message relay port
      • (-)
        • Not Authenticated
        • Blocked by a lot of firewall
  2. Port :- 465
    • SMTP
      • (-)
        • Not widely adopted
  3. Port 587
    • SMTP
      • Mail Submission Port
      •  (+)
        • Authenticated emails

 

References

  1. Microsoft
    • Office
      • Support
        • POP and IMAP settings for Outlook Office 365 for business
          Link
        • How to set up a multifunction device or application to send email using Office 365
          Link
    • technet
      • blogs.technet.com
        • Andrew Stobart – Useful Wireshark Filters for Mail Flow Troubleshooting
          Link
  2. Mozilla.Org
    • Mozilla Support
      • cannot send mail. Connected to smtp.office365.com but times out.
        Link
  3. Stanford University
    • Stanford | University IT
      • How to Configure Thunderbird for Office 365 Using IMAP
        Link
  4. StackOverflow
    • Network Ports
      • What is the difference between ports 465 and 587?
        Link
  5. JSCAPE
    • Managed File Transfer and Network Solutions
      • John Carl Villanueva
        • Still Confused With SMTP Ports? Read This
          Link
  6. FastMail
    • SSL vs TLS vs STARTTLS
      Link

 

SSRS – Setting up Smart Host

 

Background

A year or so ago we setup subscriptions to a couple of Reports that we are providing through SQL Server Reporting Services.

The subscriptions go out through daily email.

Ever so often things just break.

I was recently informed that Emails have not been going for over a week now.

Last time I blamed it on other processes that are using that same host.

Hoping today I can do same and go on about my business.

But, no such luck.

 

Environment

Here is our topology

  1. Reporting Services
    • Reporting Services is running on a local server in our intranet.
  2. Database Server
    • Database Server is running in our Colocation’s Data Center
  3. Email Server
    • The email server is Microsoft’s Office365.com

 

Troubleshooting

Thinking out loud

As always don’t have a clue what changed.

Could it be…

  1. Tightened Security
    • Can emails only go out from certain hosts
    • Do I need an actual username and password combination
    • Firewall
      • Local
        • Is it Windows Firewall
      • Corporate
        • Is it a Corporate Firewall
    • Is it Antivirus Configuration

 

Remediation

Proposal

Not sure what is getting in the way of SSRS getting the emails out.

But, a likely workaround is use a local functional SMTP server as a bridge.

 

Local SMTP Server

Installation

Launch “Server Manager” and we will choose to add “SMTP Server Tools” as a Feature.

 

Step

  1. Tab – Features
    • If “SMTP Server” feature is not checked, please place a check mark next to it
    • Dependencies
      • The “Add role services and features required for SMTP Server” window appear
        • The features listed are “Web Server (IIS)” and “Remote Server Administrative Tools”
  2. Tab – Web Server ( IIS )
    • Shows Web Server literature
  3. Tab – Confirmation
    • Confirmation that IIS and Remote Server Administrative Tools will be augmented
  4. Tab – Progress
    • As installation is proceeding each step is chronicled
  5. Tab – Results
    • The status of each component installed is noted

Images

Add Features Wizard – Select Features
Initial Screen

Before Adding “SMTP Server….

SelectFeatures_SMTP_201708087_0420PM

 

Post Checking “SMTP Server”

Adding “SMTP Server “….

SelectFeatures_SMTP_201708087_0421PM

Add Features Wizard – Add role services and features required for SMTP Server?

Dependencies are listed.

And, they include Web Server ( IIS ) and Remote Server Administrator Tools.

SelectFeatures_AddFeaturesWizard_201708087_0420PM

 

Web Server ( IIS)

Components :-

  1. Internet Information Services ( IIS ) 7.0
    • ASP.Net
    • Windows Communication Foundation

 SelectFeatures_SMTP_WebServer_IIS_201708087_0421PM

 

Confirm Installation Selections

Confirm Installation.

In our case:

  1. Web Server ( IIS )
    • Health and Diagnostics
      • ODBC Logging
    • Remote Server Administrator Tools
      • SMTP Server Tools

 

SelectFeatures_SMTP_WebServer_IIS_RoleServices_Confirmation_201708087_0423PM

Installation Progress

Installation is progress…

 

SelectFeatures_SMTP_InstallationProgress_201708087_0424PM

 

 

Installation Results

Installation Succeeded.

SelectFeatures_SMTP_WebServer_IIS_RoleServices_InstallationResults_201708087_0435PM

 

 

Configuration

Customization

  1. Tab – General
    • Enable Logging
      • It is most useful to turn on logging during initial setup and follow-up troubleshooting sessions
  2. Tab – Access
    • Group – Connection
      • Select which computers may access this session
        • All, except the list below
    • Group – Relay Restrictions
      • Only the list below
        • Self ( for now )
          • 127.0.0.1
  3. Tab – Messages
    • Send copy of non-delivery report to
      • Mail Administrator
        • Hopefully a monitored distribution list
    • Bad mail directory
      • Default
        • C:\Bad Mail
      • Non-system drive folder
        • Hopefully, you take the opportunity to change the folder to a non-system drive
  4. Tab – Delivery
    • Group box – Outbound Security
      • Authentication Choices
        • Anonymous
        • Basic Authentication
        • Windows Integration
      • In our case :-
        • Anonymous ( NO )
          • Are trying to get away from Anonymous as our hosting platform, Microsoft Office, requires user authentication
        • Integrated Windows Authentication ( NO )
          • We do not have cross-domain relationship between us and Microsoft’s Hosted Outlook
        • Basic Authentication ( YES )
      • TLS
        • We enabled TLS
    • Group box – Outbound Connections
      • TCP Port
        • 587
          • This is the default mail submission port. When a mail client or server is submitting an email to be routed by a proper mail server, it should always use this port.
            Unless you’re explicitly blocked by your upstream network or hosting provider.
            This port, coupled with TLS encryption, will ensure that email is submitted securely and following the guidelines set out by the IETF”

            John Carl Villanueva ( Link )
    • Group box – Advanced Delivery
      • Fully Qualified Domain Name
        • Especially for domains that have SPFs set up
      • Smart Host
        • smtp.office365.com
      • Attempt direct delivery before sending to smart host
        • Unchecked
      • Perform reverse DNS lookup on incoming messages
        • Unchecked
  5. LDAP Routing
    • Not going to need to use LDAP Routing for user authentication
  6. Grant Operator permissions to these Windows User Accounts

 

Screenshot

SMTP Virtual Server – Properties – General

General_20170808_0840AM

 

 

SMTP Virtual Server – Properties – Access

Access_20170807_0752PM

 

SMTP Virtual Server – Properties – Access – Connection
Initial

Access_ConnectionControl_20170807_0753PM

 

SMTP Virtual Server – Properties – Access – Relay Restrictions
Initial

Access_RelayRestrictions_20170807_0754PM

 

Add Computer

List

  1. Single Computer
    • IP address:- 127.0.0.1

 

Access_RelayRestrictions_AddComputer_20170807_0755PM

Completed

Access_RelayRestrictions_Computer_20170809_0114PM

 

SMTP Virtual Server – Properties – Messages
Initial

Messages_20170807_0756PM

 

Completed

Messages_20170807_0757PM (BrushedUp)

 

 

SMTP Virtual Server – Properties – Delivery
Initial

Delivery_20170807_0757PM

 

SMTP Virtual Server – Properties – Delivery – Outbound Security
Initial

Delivery_OutboundConnections_20170807_0444PM

 

 

Complete

Delivery_OutboundSecurity_20170807_0758PM (BrushedUp)

 

SMTP Virtual Server – Properties – Delivery – Outbound Connections
Initial

Delivery_OutboundConnections_20170807_0445PM

 

Completed

Delivery_OutboundConnections_20170807_0758PM

 

 

SMTP Virtual Server – Properties – Delivery – Advanced Delivery
Initial
Completed

AdvancedDelivery_20170807_0759PM [BrushedUp]

 

Conclusion

We have an SMTP Server setup.

We will come back and unit test it out and once verified, we will point Sql Server Reporting Services ( SSRS ) to route emails through it.

 

References

  1. jscape
    • John Carl Villanueva
      • Still Confused With SMTP Ports? Read This
        Link