Subscribe to SCCM Reports with Office 365

Service Level Agreements (SLAs) are the driving force behind the need for more reporting with SCCM. Why? In many organizations, SCCM report results are used to validate SLAs. Whether you use the built-in SCCM reports or a custom report, wouldn’t it be nice to automatically create and deliver these reports? It’s all possible with SQL Server Reporting Services (SSRS) subscriptions. You can subscribe to SCCM reports by file or email, and in this post I will show you how to subscribe to SCCM reports using Office 365. Your Manager and security team will thank you once you set-up a rule to automatically email them status reports. On a regular basis they can see reports about patch management and compliance settings or any other reports they require.

SQL Server and Office 365

Prior to the release of SQL Server 2016 SSRS, you could not subscribe to SCCM reports using Office 365.

In case you weren’t aware, Office 365 presents a unique challenge to earlier versions of SSRS because SSRS requires a Simple Mail Transfer Protocol (SMTP) connection to the email server. In order to use a SMTP connection, Office 365 requires the authentication of a user’s account. Up until the release of SQL Server 2016 SSRS, however, you couldn’t authenticate an account with SSRS. There is a way around this, but it is frowned upon by most security and Exchange teams. You can use SMTP relays to relay email from SSRS to Office 365. It means that anyone or any application can send an email to the SMTP relay and the relay will forward the email to Office 365. As I said earlier, most security and Exchange teams seriously frown on this because it is considered unsecure, so in nearly all cases this method is not used.

Fortunately, with the release of SQL Server 2016 SSRS, you can now add a username and password to connect to your SMTP server settings.

Below, I will show you how to subscribe to SCCM reports using Office 365.

Subscribe to SCCM Reports Using Office 365

Start by opening Reporting Services Configuration Manager and connecting to your SSRS server.

Tip: Use a dedicated account, with no other rights within Active Directory (AD) or Azure Active Directory (AAD), to send emails in Office 365. This will help reduce your security exposure by only allowing this account, with no rights, to send emails. As an example, I use reporting@www.enhansoft.com for my dedicated account.

Next, on the E-mail Settings node, enter the sender’s email address. In my case it is reporting@www.enhansoft.com. Move to the SMTP textbox, configure your Office 365 SMTP location. In my case it is Outlook.Office365.com.

Change the Authentication drop down to Username and password (Basic).

Once the authentication drop down is changed, you can then configure the username and password.

Ensure that the Use secure connection checkbox is selected.

Don’t forget to check Apply.

Your final screen will look similar to the one below.

With the final steps completed, you can now email SCCM reports/dashboards to your team in a secure manner. This simple setup will ensure that both the Exchange Manager and the security team are happy as there will be no SMTP relays needed in to order to leverage SCCM reporting. Your team will also be happy once they start receiving regular report status updates.

For more information about how to setup an email subscription, please see my blog post, How to Set-up an Email Subscription in SSRS.

If you have any questions about how to subscribe to SCCM reports, please feel free to contact me @GarthMJ.