Configuring Reporting Services Mail Delivery Using hMailServer SMTP To Gmail Relay

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.

  1. Enable logging in hMailServer. Go to Settings->Logging and enable logging for SMTP.
  2. Check the message on the Reporting Services subscription page.
  3. Check your spam folder – the reports might be going there.

 

 

SSIS Error: The component is missing, not registered, not upgradeable, or missing required interfaces.

I got a weird error the other day in SSIS that said “Error: The component is missing, not registered, not upgradeable, or missing required interfaces.” It was referring to an XML source component in a data flow. I am not sure if this error is specific to XML sources, or it just occurred because this happened to be the first component that got initialized. I am using Visual Studio 2015 Community and SQL Server 2016. After much wailing and gnashing of teeth I figured out how to fix the the problem. You need to change the “Target Server Version” property of the SSIS project (right click on the SSIS project and select properties to get there) to “SQL Server 2016”. Originally it was set to “SQL Server vNext”.

Moving An Email To A Folder In Gmail Using Python

I receive daily activity statements from my broker and I wanted to download them, extract the data into a MySQL database and then archive the statement on the hard drive. The first part is downloading the attachment, and then moving the email to another imap folder, which is something I had trouble with in Gmail. I use Python to do it, and the email and imaplib packages. Here’s a look at the code that moves all the messages in the inbox from a single recipient to another imap folder.

 

import Messaging, Util, Const
import email, imaplib, os

m = imaplib.IMAP4_SSL(Const.IMAP_SERVER)
m.login(Const.IMAP_USER, Const.IMAP_PWD)
m.select()

resp, items = m.search(None, 'FROM', '"donotreply@interactivebrokers.com"')
items = items[0].split()

for emailid in items:
    resp, data = m.fetch(emailid, "(RFC822)")
    if resp == 'OK':
        email_body = data[0][1].decode('utf-8')
        mail = email.message_from_string(email_body)

        if mail.get_content_maintype() != 'multipart':
            continue

        if mail["Subject"].find("Activity Statement") > 0:
            print("Moving message " + mail["Subject"] + " to " + Const.IMAP_DEST_FOLDER)
            result = m.store(emailid, '+X-GM-LABELS', Const.IMAP_DEST_FOLDER)
            #result = m.store(emailid, '+FLAGS', '\\Deleted')
            mov, data = m.uid('STORE', emailid , '+FLAGS', '(\Deleted)')
            m.expunge()

m.close()

I use another file called Const.Py to store all my configurations, so change those to whatever you need. The key here is to change the label with the line “result = m.store(emailid, ‘+X-GM-LABELS’, Const.IMAP_DEST_FOLDER)”, and then add the “Deleted” flag, before expunging the inbox.

 

Installing Plex Media Server on Centos 7

I’ve been running Plex Media Server on an old desktop machine that runs in my closet for a few years now. It was running on Centos 5 and has performed beautifully, with the family using it constantly. Unfortunately, as it goes, hardware fails, and I lost two hard drives and the optical drive at the same time. With the new hardware in place, these are the steps I took to get the family fun center up and running.

Plex Media Server Step-by-step Installation Guide for Centos 7

  1. Download and install Centos 7. This is the latest version of the OS. I have done a minimal install. The install goes great, but Red Hat have removed support for 100mbps NIC’s in this latest version, so be sure your hardware is running a gigabit network card, or else Centos will not pick it up. I bought a Trendnet card from Amazon for $10, and Centos picked it up perfectly during the install.
  2. Once installed, log in and install a few packages that I find useful, namely
    1. net-tools
    2. nano
    3. wget
  3. Perform a system update – yum update
  4. Secure your server. Linode has got some great articles & tutorials, and their one on securing your server is a must. Follow their instructions for a secure Linux server. Just remember to check your permissions and ensure 0700 for ~/.ssh and 0644 for the authorized_key file in that folder.
  5. Disable SELinux by editing the file /etc/sysconfig/selinux I have found that SELinux messes with the media server, and in all honesty is not necessary for a media server.
  6. My media is on a NAS that I will be accessing via NFS, so I will install the NFS client – yum install nfs-utils nfs-utils-lib
  7. Connect to my NAS. I created a folder called /mnt/NAS and have added the following line to the /etc/fstab file: 192.168.1.110:/mnt/HD/HD_a2/media /mnt/NAS nfs defaults 0 0. To test it run mount -a
  8. Download Plex using wget
  9. Install Plex by running yum localinstall
  10. Open the appropriate firewall ports for PMS. See the following article for the list of ports. The command to open a port in Centos 7 looks like this: firewall-cmd --add-port=32400/tcp --permanent. (Those are double dashes in front of add and permanent) You can use nmap to check the open ports (sudo nmap -sT -O localhost) or firewall-cmd --query-port 32400/tcp
  11. Make sure Plex starts on system boot. You can check this with systemctl is-enabled plexmediaserver.service. If it returns “disabled” then turn it on with sudo systemctl enable plexmediaserver.service
  12. Now go to your Plex media server via a web browser on another machine and start adding libraries – http://192.168.1.231:32400/web/index.html

Troubleshooting

If you are having trouble, there are a few resources that may help.

  • DigitalOcean also have great articles on setting up a Centos 7 server.
    • https://www.digitalocean.com/community/tutorials/initial-server-setup-with-centos-7
    • https://www.digitalocean.com/community/tutorials/additional-recommended-steps-for-new-centos-7-servers
  • If you’re having permission issues – check out this page: https://support.plex.tv/hc/en-us/articles/200288596-Linux-Permissions-Guide

Plex Media Server

Centos 7