SQL Server Blog

How Database Mail XPs Can Become a SQL Server Security Liability

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

If you ever review the Center for Internet Security (CIS) benchmarks for SQL Server security, it might strike you as odd that one requirement is to ensure that Database Mail extended procedures (XPs) is disabled.

I am totally not making that up. Although it is unlikely that having Database Mail XPs will result in a security breach in your environment, you should be aware of how it can be used by those with malicious intent.

The first potential security risk to be aware of is that Database mail allows for a convenient way to leak your data. Since the results of a query can be sent in an email, anyone who has gained access to your data can send mail from the SQL Server to whatever email address they like. Lots of email addresses, in fact. And this doesn’t even require a hack, as disgruntled and well-meaning employees could be able to send data like personally identifiable information (PII) outside of your organization.

The second potential security risk is that this opportunity to send mail directly from SQL Server lends itself to the possibility of a Denial of Service (DOS) attack, where a zillion emails get sent to a location and overload the servers so much they can’t handle all the connections and appear to be offline. Admittedly, this is very rare, but since email can be sent automatically from your SQL Server instance the opportunity for using Database Mail XPs in a DOS attack does exist.

Now, either of these things can be stopped by having restrictions on your mail server related to size of emails and their attachments, and the quantity of emails that can be sent. But…well, sometimes the administrators who set up your mail server make mistakes or overlook things. People aren’t perfect.

They can also be stopped by limiting who has access to send email from your SQL Server instance. In order to use the Database Mail XPs, a user would need to be added to the “DatabaseMailUserRole” in the msdb system database. The good news is many folks don’t know this, so the only other folks that can use this feature are members of the all-powerful sysadmin role.

To be clear, I’m not saying you need to have Database Mail XPs disabled, although the Center for Internet Security is. In practice, lots of Database Administrators use this feature as part of creative monitoring solutions, often in a job that polls for conditions and sends an email alert to them or a distribution list for awareness of these conditions. Hey, not everyone is given the budget for monitoring software.

But I would suggest you consider who has access to this feature. You can see if anyone was granted explicit permissions to this role with the following query.

USE msdb;
SELECT u.name AS UserName
FROM sys.database_principals u
JOIN sys.database_role_members drm
    ON u.principal_id = drm.member_principal_id
JOIN sys.database_principals r
    ON drm.role_principal_id = r.principal_id
WHERE r.name = 'DatabaseMailUserRole';

Beyond that, and stop me if you think you’ve heard us say this before, be sure to verify that you trust all members of the sysadmin role.

Article by Jeff Iannucci
Jeff loves to help others save time. Whether improving query performance or reducing downtime for maintenance tasks or migrations, he’s not only passionate about discovering ways to save our most valuable resource, but also about sharing what he’s learned. Starting as an accidental DBA in 1998, Jeff has worked extensively with SQL Server database design, administration, and T-SQL development. He appreciates any opportunity to share his knowledge with the community, and has been presenting at user groups and writing blog posts since 2018. He was chosen as an IDERA ACE for 2020, and in 2021 became an author for Pluralsight. When not resolving database issues, he enjoys watching Indycar racing, attending Arizona Diamondbacks and Phoenix Suns games, and cooking Italian food for his wife and four children.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This