Deliver reports via email using an email server outside your network

Today’s post is from Sundeep Meda, an engineer who’s been working on Reporting Services and other BI products here at Microsoft since 2010.

As part of SQL Server 2016 we introduced a new feature to support delivering reports via email using an SMTP server outside your network. Previously, the only way you could authenticate to your SMTP server was as the Reporting Services service account, which often meant setting up an IIS SMTP server inside your network to relay to your actual SMTP server. With this new feature you can specify an external SMTP server – whether Exchange Online, Gmail, or even services like SendGrid – along with a username and password to send emails. This has been a popular customer ask for a while and we are excited that this feature is available in this release.

Connect your report server to your email server

1. Open Reporting Services Configuration Manager and go to the "Email Settings" tab.
2. Select the Authentication type as "Username and password (Basic)."
3. Type in Sender Address, SMTP Server, Username, and Password info.
4. Always make sure you check the "Use Secure Connection" checkbox.
5. Once you click Apply we encrypt this data and drop it in the RSReportServer.config file under the "Report Server Email" extension.

Key Point: If you have a scale-out instance you need to repeat these steps in each of the nodes on your farm. It is similar to setting an Unattended Execution Account on each of your scale-out nodes.

Connect to Office 365/Exchange Online email

Below is an example of the settings you would need for Office 365/Exchange Online:

Connect to Gmail

Below is an example of the settings you would need for Gmail:

Key Point: Gmail requires you to turn on this setting to enable SMTP access with basic username and password authentication: https://www.google.com/settings/security/lesssecureapps. You can read more about this setting here.

WMI command

You can also use the following WMI command to configure email settings on your report server.

From an admin PowerShell command prompt on your report server node run these commands:

$rsEmailconfig = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\RS_MSSQLSERVER\v13\Admin" -class MSReportServer_ConfigurationSetting -ComputerName localhost

$rsEmailconfig.SetAuthenticatedEmailConfiguration($true, "outlook.office365.com", "johndoe@microsoft.com", "johndoe@microsoft.com", "*****", 1, $true)

Please let us know what you think in the comments.

Try it now and send us your feedback

• Download SQL Server 2016
• Check out the documentation: What's new in Reporting Services (SQL Server 2016)
• Post in the Reporting Services forum (or if you prefer, send us an email)
• Join the conversation on Twitter: @SQLServerBI, #SSRS

Comments

• Anonymous
  July 28, 2016
  This is a great idea, previously to make it functional it was very boring.
• Anonymous
  August 07, 2016
  Hi again!I tested this resource, and it worked very well!