We at Bradford use Google Apps for our email. My computer not part of the domain (I don’t’ work from the corporate office) and we don’t have our own internal SMTP server in our domain because we don’t need one.
There are tons of apps that we do email notification with and using our nettrax.net SMTP server works great. Well, for security purposes because it’s a public (on the Internet) SMTP server we require SSL encryption and authentication to connect to the SMTP server.
Microsoft in all their wisdom made it so that SSRS cannot be setup to use even basic authentication!!! I still cannot believe this. Basically you have to give SSRS a wide open SMTP server that will just accept ANY EMAIL you throw at it! (reference: http://en.sql4you.info/?p=132 “You want to configure SSRS to use basic authentication with a SMTP Server – FORGET IT – it is not supported”)
So, what I had to do was install a tiny little application called E-MailRelay that basically is nothing but an SMTP relay.
What does E-MailRelay do?
You configure it to listen on a local port (I used default 25 so I didn’t have to fight with SSRS to change the port number although I saw tutorials on how to change the port for SSRS) on the machine where E-MailRelay is installed. In my development environment this is all on my local machine. Then you configure E-MailRelay as to what real SMTP server you want it to send things on to. In that setting I can specify smtp.server.com:### and then the username/password (for authentication) and the Use SSL Encryption so no passwords are sent over the Internet in plain text.
So, now I have E-MailRelay running on port 25 waiting for email to come in. So, in Reporting Services Configuration Manager my SMTP server is the machine name (or IP) of the computer where E-MailRelay is running. Again, in my development environment that is my local machine (localhost)
E-MailRelay is running in the background waiting for email to come in to it. So when my scheduled subscription fires off in SSRS it makes a connection to E-MailRelay (because that’s the SMTP server running on my ‘localhost’) and sends the email. At this point SSRS is done. After all, it thinks it just sent the email to the SMTP server and it accepted the incoming email. E-MailRelay then connects to my real SMTP server and authenticates using the SSL username and password. It then basically sends the email to the real SMTP server which takes it and delivers the email like normal.
While it may not be the most graceful solution, it does provide a way to get around SSRS not allowing you to configure a SMTP server with even basic authentication. Maybe SSRS with SQL Server 2014 will finally allow SMTP configuration with basic authentication?