为SQL Server设置数据库邮件时的注意事项和注意事项

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.

数据库邮件已被全世界的DBA和公司广泛使用，并且是SQL Server的功能之一，对初创企业而言可能非常重要。 这是因为它是一种廉价的解决方案，可从SQL Server获取有关潜在硬件问题，损坏的预警迹象以及潜在资源限制的警报。 但是，它可能被滥用或引起潜在的安全问题。 在开始之前，我个人清楚我赞成在正确完成后为DBA团队配置数据库邮件。 在本文的其余部分，我们将介绍人们在使用数据库邮件及其SQL Server时常犯的一些错误。 我们不讨论这个功能的设置，因为它是有据可查的博扬罗维奇上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.

由于每个内核SQL Server的成本很高 ，因此您要确保将这些周期用于数据库而不是作为邮件服务器。 即使公司没有专门的团队来兴建新的基础架构，也最好实施替代方案来为应用程序发送电子邮件。

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.

尽管SQL Server可以发送电子邮件，但这样做并不是最有效的，因为它尚未针对我们现代肝脏所建立的日常通信进行优化。 例如，当您发送大量电子邮件时，所有这些电子邮件都需要记录到MSDB数据库中，这将导致数据库在硬件预算中使用更多的资源来增长。 许多流行的语言都有一个经过多年优化的SMTP库，可以快速或并行发送电子邮件，这应该首先进行调查。

安全问题 (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.

在开始任何项目中的新功能或启用现有功能之前，应将其记录为代码，作为DSC脚本或在变更管理过程中。 这样一来，就可以最大限度地减少问题，并考虑许多有关该功能如何导致团队出现问题的观点。 这对于维护安全稳定的基础架构至关重要。

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.

现在我们在同一页面上，通过启用数据库邮件，您正在扩展SQL Server的攻击面。 这意味着您现在必须监视和跟踪数据库邮件可能带来的潜在问题或漏洞，尽管这种情况或漏洞很少发生。

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.

此外，尽管它确实需要特定角色“ DatabaseMailUserRole”来发送数据库邮件，但使用TSQL不仅可以通过代理中的警报或事件来发送电子邮件，还可以。 这意味着您SQL Server可能成为大量电子邮件骗局中的一个节点，您可能会将服务器IP /电子邮件帐户列入黑名单。

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.

这使我们进入下一步，启用数据库邮件时，重要的是锁定服务器，以便它们仅将电子邮件发送到特定来源，同时仅允许SMTP设置所需的出站端口。 这样，通过不允许任何不必要的流量离开SQL Server，这有助于维护数据库的完整性，这是质量数据丢失保护（DLP）可以防止的第一件事。 我已经看到一些SQL Server，它们要么允许每个端口都离开SQL Server的出站端口，要么直接关闭Windows防火墙。 这些不是最佳做法，因为在这种情况下，您可能不会及时发现问题，这将使您容易遭受公司数据的潜在危害。 有许多方法可以破坏应用程序并泄露数据，不要让您的电子邮件配置成为数据泄露的主要原因。

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.

这可以通过创建新的出站规则并选择仅在安全的情况下允许连接来完成。 此对话框将添加一个计算机部分，以允许您定义用于邮件服务器的IP范围。

Or, this can be done by modifying an existing rule and selecting the scope tab of the dialog to add the IPs.

或者，可以通过修改现有规则并选择对话框的作用域选项卡来添加IP来完成此操作。

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.

通常，只有在拥有自己的邮件服务器的情况下，才可能配置这种理想方法，因为许多在线托管公司已经适应了扩展基础结构模型，因此不再发布IP。 但是，通过Amazon的简单电子邮件服务（SES），您可以设置专用IP来发送电子邮件，而只需支付少量额外费用，即可在此处找到更多详细信息。

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“.

最后，数据库邮件通常被认为不太安全，无法将信息发送到客户端。 这是因为此数据库邮件不支持Google邮件套件中使用的许多现代安全功能。 配置数据库邮件时，您可能需要选中“ 允许安全性较低的客户端发送邮件 ”选项。

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.

Microsoft的文档中通过在数据库邮件中发送测试电子邮件来简要介绍了这一点。 对于其他SMTP站点，这可能不是一个选择，因为Google做得很好，可以确保所有使用其凭据的应用程序都被MFA或其他形式的安全性锁定。

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.

明确地说，我并不是说数据库邮件本身并不安全，而是需要此设置意味着您可能会禁用Google套件中的安全功能。 但是，在构建应用程序时，可以将应用程序配置为符合SMTP服务器的标准，并使用更新的身份验证方法。

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.

总之，我相信数据库邮件是DBA的绝佳工具，应用于跟踪SQL Server上的严重性高的问题。 我也相信DBA应该启用此功能，但是，应该以每个人都同意的方式进行，并且只有在解决并讨论了所有安全性问题之后才能这样做。

常见问题 ( 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.

不，应在应用程序中放置其他SMTP库，以实现更有效的电子邮件功能。

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.

第一步之一是仅允许SMTP设置的特定端口和IP作为防火墙中的出站规则离开服务器。 上面讨论了更多细节。

翻译自: https://www.sqlshack.com/considerations-and-concerns-when-setting-up-database-mail-for-sql-server/