Sending email in SSRS 2016 on port 25 with authentication and SendGrid

Recently we moved a client from their aging on-prem environment into Azure. As part of this there was a lot of system u

pgrading being done to get everything onto current versions. Part of this included upgrading SSRS 2008 R2 to SSRS 2016. As a part of the migration we needed to ensure that email delivery of reports from SSRS was working as expected. This required setting up SMTP with authentication as the only way to send emails out of Azure is to use some sort of authenticated SMTP service such as SendGrid or Office 365. We opted for SendGrid as it includes some nice reporting as part of the email sending.

We setup our nice new SQL 2016 SSRS servers to authenticate using the new SQL 2016 GUI which just supports it now. One problem with the GUI is that it requires that you use encryption. No big deal, SendGrid supports encrypted SMTP.

The problem with SendGrids encrypted SMTP is that it has a funky certificate chain which isn’t trusted by default. And it’s a convoluted process to get the correct certificate chain installed on a few servers (we have an SSRS farm as there’s thousands of reports being delivered every morning).

The easier option was to simply setup SMTP to not use encryption. Now the GUI doesn’t support this, but thankfully the XML Config file does. Simply go find your rsreportserver.config file (it’ll be in C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer by default) and open that puppy up in notepad. There’s a couple of settings you’ll want to change in here. First you’ll want to change the port number to port 25. You’ll find that in the setting SMTPServerPort in the XML Document. The second setting is to disable XML which is the SMTPUseSSL setting, so change that from True to False (yes it’s spelled out not using 1/2 or 0/1 like any sensible setting would).

You’ll probably also want to change out the email address that’s in the from field. By default SSRS will put the username in as the from email. Now if using a corporate system to send email that’s fine. With sendgrid your email address is some massive long string, so that isn’t helpful. Directly under the encrypted username and password you’ll find a From tag. Just change that email address to what you want the from field to be and off you go.

In a scaleout configuration like we have you’ll have to edit these settings on each server. I have no idea why these settings aren’t written to the database, but they aren’t.

Denny

The post Sending email in SSRS 2016 on port 25 with authentication and SendGrid appeared first on SQL Server with Mr. Denny.