Unanswered: reporting services (SSRS) with SMTP authentication
My company is moving to sql server reporting services 2008 (SSRS) in order to utilize email report subscriptions. However, there seems to be a mind-boggling limitation that SSRS does not support basic SMTP authentication. Since this would seem to invalidate SSRS as a solution for many offices, I'm thinking there MUST be a workaround somewhere.
I've been attempting to configure the smtp server on the sql server's host to throw our email provider's smtp authentication creds. This has stopped the "550 - no relaying allowed" errors, but the mail is still not being delivered internally or externally.
Has anyone built a successful workaround to SSRS's lack of basic SMTP authentication support?
Also, has anyone heard a compelling reason as to why MS placed such a silly limitation on SSRS in the first place? It was an annoyance in SSIS's sendmail task - but the workaround was simple (use a sql task with a sp_send_dbmail statement).
Finally got this working, so I'm posting here in case anyone else has issues:
I started the SMTP service under IIS on a local server (I just used the sql server machine for testing). Then, I configured an SMTP virtual server, "[SMTP Virtual Server #1]" with the following properties:
Access | Authentication : Anonymous Access
Access | Relay restrictions : Only the sql server machine (optional)
Delivery | Outbound Security : Basic Authentication (username, password of external smtp)
Delivery | Outbound connections : TCP port: (whatever port external smtp uses)
Delivery | Advanced: Smart host: (address of external smtp)
Then, in the Reporting Services Configuration Manager under E-mail Settings, enter the LOCAL IP ADDRESS of the virtual SMTP you just configured and the sender as a valid user on your external SMTP.
One side benefit: this should also enable the crippled SendMail task in SSIS if you set the local ip address as your smtp server in the smtp connection for the package.