I have a SQL Server installation at home that I use as a sandbox and also for a small system I built for monitoring the stock market (more on that in another post to come). As part of that system I have a reporting services report that I’d like to email to myself on a set schedule during the day.
The SQL Server is on a Windows 10 machine which does not come with IIS and SMTP anymore, so I needed another solution to be able to get these reports emailed out. After Googling a bit, I found hMailServer, which is a free, open source email server for Windows. After you have downloaded and installed hMailServer, these are the steps to take to configure Reporting Services to email subscriptions through Gmail. This tutorial assumes that Reporting Services and hMailServer are on the same machine.
Configure Reporting Services
This is the easy part. Open Reporting services configuration manager, go to E-mail settings, and set them up as follows (replace example@gmail.com with the email address you are going to be using):
Configure hMailServer
This is the part I struggled with a little bit. I’m not really an SMTP/email guru. To setup the SMTP relay in hMailServer, go to Settings->Protocols->SMTP and select the Delivery of e-mail tab. Fill in your gmail settings here.
Remote host name: smtp.gmail.com
Remote TCP/IP port: 465
User name: example@gmail.com
Connection security: SSL/TLS
Now under Settings->Advanced->IP Ranges, click on My computer and make sure Require SSL/TLS for authentication is not checked, and make sure you do not require authentication for SMTP, as pictured below.
Gmail Settings
Gmail has a setting called “Allow access to less secure apps”. this needs to be enabled in order for this to work. If it is not enabled, you will probably get an email like this.
Debugging
If you are having trouble, these are some steps to take to get to the bottom of it.
- Enable logging in hMailServer. Go to Settings->Logging and enable logging for SMTP.
- Check the message on the Reporting Services subscription page.
- Check your spam folder – the reports might be going there.
