Database mail is widely used by DBAs and companies around the world and is one of the features of SQL Server that can be very important for startups. That is because it is a cheap solution for getting alerts from your SQL Server for potential hardware issues, early warning signs of corruption, along with potential resource constraints. However, it can be misused or pose potential security issues. Before we start, to my myself clear I am in favor of configuring Database mail for the DBA team when done properly. In the rest of the article, we will touch on some of the common mistakes people make with database mail and their SQL Servers. We will not discuss the setup of this feature as it was well documented by Bojan Petrovic on SQLShack.
Misuse of database mail
As a company starts out, it is important to get the job done in whatever way you can. However, there are some things you should slow down to talk about, setting database mail to send application related emails can be one of those. This conversation often comes up when you first configure database mail as an alerting solution for high severity issues for the database team. Once learned, some developers might want to use this as a solution to send emails out as an email blast to alert clients their reports are ready or confirm their scheduled tasks have finished.
Due to the high cost of SQL Server per core, you want to make sure you are using those cycles for your Database not as a mail server. Even when a company does not have a dedicated team to spin up new infrastructure, it is always a good idea to implement alternative solutions for sending emails for applications.
While SQL Server can send email, it is not the most efficient at doing so because it has not been optimized for the everyday communications that our modern livers have been built around. For example, when you send mass amount of emails all these emails need to be logged to the MSDB database which will cause the database to grow using more of your resources in the hardware budget. Many popular languages have a SMTP library that has been optimized through the years to send emails quickly or in parallel which should be investigated first.
Security issues
Prior to starting on a new feature or enabling existing features in any project you should have it documented either in code as a DSC script or in a change management process. This allows for minimal issues down the line along with many viewpoints of consideration on how the feature can cause problems for the team. This is critical to maintaining a secure and stable infrastructure.
Now that we are on the same page, by enabling database mail, you are expanding the attack surface of SQL Server. This means that you now must monitor and track potential issues or vulnerabilities that may come up with database mail, rare as they may be.
Additionally, while it does require a specific role “DatabaseMailUserRole” to send database mail it is possible to send emails out using TSQL not just through alerts or events in the agent. This means it is possible for your SQL Server to become a node in a mass email scam where you could have your server IP/ email account blacklisted.
This leads us to our next point, when enabling database mail, it is important to lock down your server so that they only send email to a specific source along with only allowing the outbound port that is required for your SMTP settings. In doing so, you are helping to maintain the integrity of your database by not allowing any unnecessary traffic to leave your SQL Server which is one of the first things a quality Data loss protection (DLP) can protect against. I have seen some SQL Servers that either allowed every port to leave outbound on SQL Servers to simply turning off the windows firewall. These are not best practices as you are likely not going to catch a problem in time if this is the case and it will open you up to potential compromises of your company data. There are many ways for an application to be breached and leak data, do not let your email configuration be the leading cause in a data breach.
This can be done by creating a new outbound rule and selecting to allow the connection only if it is secure. This dialog will add a computers section to allow you define the IP ranges you use for a mail server.
Or, this can be done by modifying an existing rule and selecting the scope tab of the dialog to add the IPs.
This is ideal method was typically only possible to configure if you had your own mail server since with many online hosting companies they have adapted to a scaling infrastructure model and thus do not have published IPs anymore. However, with Amazon’s Simple email Service (SES) you can set up a dedicated IP to send email for only a small extra fee, more details can be found here.
Finally, database mail is often considered less secure for sending information out to clients. This is because this database mail does not support many of the modern security features that are used in the Google mail suite. When configuring database mail, you may need to check the option to “allow less secure clients to send mail“.
This is touched on briefly in Microsoft’s documentation for sending a test email on Linux through database mail. This may not be the option for other SMTP sites as Google does a decent job of making sure all application using their credentials are locked down with either MFA or other forms of security.
To be clear, I am not saying database mail is unsecure by itself, but requiring this setting means that there are security features in Google’s suite that you might be disabling. However, when building an application, you could have your application configured in such a way that it can be compliant with the standards of the SMTP server and use newer methods of authentication.
In conclusion, I believe that Database mail is a great tool for DBAs and should be used to keep track of high severity issues on SQL Server. I also believe that DBAs should have this enabled, however, this should be done in a fashion that everyone agrees on and only once all the security concerns have been addressed and discussed.
FAQs
What should you consider before enabling database mail?
There are a few things to consider discussed below, but it is always good to discuss with your team to understand environment specific issues.
Should you use database mail for applications?
No, there are other SMTP libraries that should be put in the application that allow for more efficient email capabilities.
How do I lock down database mail?
One of the first steps is to only allow specific ports and IPs for your SMTP settings to leave the server as an outbound rule in the firewall. More details are discussed above.
My curious nature leads to me learn about different methodologies for accomplishing tasks more efficiently without compromising on the quality. This does tend to lead one down the rabbit hole, but it often ends in valuable experience that I enjoy sharing with anyone willing to take the time. You can find me on LinkedIn
- Preparing for SQL Server Database Corruption; initial reaction and analysis - February 6, 2019
- Considerations and concerns when setting up Database mail for SQL Server - August 31, 2018
- SQL Server Setup – Instant File Initialization (IFI) - August 31, 2018