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