How do I configure SQL Server 2005 Reporting Services (SSRS) to email a report via a remote SMTP server?

Question

How do I configure SSRS/Windows Server 2003, so that I can setup email delivery via a remote SMTP server that requires username and password.

I can configure SSRS with an SMTP address and other parameters, but nowhere is it possible to configure it with smtp username and password.

I have hunted around, but can only find vague reference to setting up some sort of relay, to cover up the bizarre lack of smtp functionality that SSRS has out of the box.

Any ideas?

Answer 1

Here are the steps to set this up using only Microsoft SSRS/Windows Server 2003 components.

1) Install SMTP server in Add/Remove programs / App Server / IIS
In IIS Manager:
2) add the domain (as a remote type) you will be sending as such as yourwebsite.com
3) Under Default SMTP Virtual Server properties / delivery tab / advaced button, add your remote smtp server in the smart host field.
4) Under Default SMTP Virtual Server properties / delivery tab / Outbound security button, choose Basic Authentication, user name = SMTP user name, password = SMTP password

In the rsreportserver.config file (sql drive:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer):
5) Populate <SMTPServer>x.x.x.x</SMTPServer> with the IP of the machine where you just setup the SMTP server.
6) Populate <From>[email protected]</From>
7) Change this one to false <SendEmailToUserAlias>False</SendEmailToUserAlias>
8) Lastly, make sure you setup the domain as a permitted host such as this:
<PermittedHosts>
<HostName>yourwebsite.com</HostName>
</PermittedHosts>

As far as why SMTP basic authenication isn't supported directly in SSRS seems to be by design. The best explaination I could find was here on this MSDN forum:

As one poster here mentions, there is a Microsoft Connect ticket open for people who are requesting this functionality.

Answer 2

For sure you have already solved this issue, but let me put here an additional information so others that have this same problem, like me, can solve it by following this how to that Tom Willwerth post.

To solve this issue of SMTP relay just follow the steps above, but be sure to do an additional step, that can be called: 4-a) On Access tab, choose Relay and select "All except the below" on Select which computer may relay through this virtual server:

This will allow all connections to send through this smtp, but be aware that the smtp server will relay anyone who connects to it. If you want to restrict this relay, than you choose "Only the list below" and add the machine you want to relay on.

For more detailed information on this, you can see the source where i found this information on: http://businessintelligencechronicles.blogspot.com/2010/08/configure-reporting-services-to-use.html

And another thing, the step 8) isn't necessary .

Hope it can be helpful to others and thank you for the information, it helps and now i think one can solve this issue just by following this steps on this link.

Regards

Ps: sorry for the mistakes