Last fall, I configured our AWS RDS SSRS server for email subscriptions using SES (Amazon Simple Email Service.) Last week, I was asked to switch the SMTP Server in that configuration to use my company’s Office 365 SMTP server. I started by following all the steps just as I had before with SES.
See the previous post about RDS SSRS Email at:
UPDATE JAN 2024
Setting up OFFICE 365 for AWS RDS SSRS credentials may not require any of the fixes described below. My manager had me move one of other accounts over to Office 365 for SMTP and I followed the steps in the article linked above. All that was needed was to white list the IP Address for the VPC in Office 365.
I am leaving the below troubleshooting tips for reference only. As I stated back then, I suspected AWS would have a fix for this, and indeed it appears they did.
LEGACY TROUBLESHOOTING Article Continued…
Confident all would be well, I tested sending my first subscription. When nothing arrived in my email box, I went back to the SSRS report and reviewed the subscription only to find this very non-descript error. “Failure sending mail: One or more errors occurred.Mail will not be resent.”
Naturally I decided to dig further. To do that, I opened up PowerShell (with the AWS CLI plugin installed) and ran this snippet to list the SSRS log files.
aws rds describe-db-log-files --db-instance-identifier MyRdsServerNameAnd then this snippet to actually get to the log file.
aws rds download-db-log-file-portion --db-instance-identifier MyRdsServerName--log-file-name ssrs/ReportingServicesService_THE_DATE_AND_TIME.logInside the log I found a more useful error message:
“System.Net.Mail.SmtpException: The server committed a protocol violation.”
Mmmm. Really? I was stumped. Not knowing what to do, I contacted AWS Support.
The first step Support had me complete, was to set up database mail on the RDS database server using the same SMTP server credentials. Since that functioned correctly, the RDS team began to investigate.
The next thing we did was to force RDS to use TLS 1.2. I did this by disabling the TLS 1.0 and TLS 1.1 in the Parameter Group associated with the RDS instance. Since I already had created a custom parameter group all I had to do was edit the values there. Our company also has a policy to use TLS 1.2+ so I didn’t have to worry about other servers or applications not being able to connect if I made this change.
That still did not fix issue.
Next, Support pointed me to this article regarding an “interesting interaction” with SSRS. Since we were testing this on a development RDS server first, Support went ahead and updated the registry value as suggested in the article.
That seemed to fix the issue. I suspect that AWS may roll out a fix for this across the board in the near future. In the mean time, I am having to put in support requests for my other AWS RDS Database Servers that also run SSRS.
Once Support made the change, I needed to reboot the SSRS Server. To do this, I went to my RDS Option Group associated with the RDS Server and updated the SSRS values there. Selected to the MAKE CHANGES IMMEDIATE, and SAVE. That caused a reconfiguration, restart, and new SSRS log to be created.
My test SSRS Report email subscription sent as expected. Success!