Database Mail Setup

Overview

Mail is set up once, by somebody who has left, and is then never looked at again until the night it matters.

The Database Mail Setup page: the profiles, the accounts and what joins them
The whole page. One profile and one account, with the line under the header naming the three things that will stop mail here.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Mail Setup.

Four things break it, and none of them raises an error at the time

A profile with no account

It exists, it can be named, and every message sent through it fails.

No default public profile

sp_send_dbmail called without a profile name is what almost every alerting script in the world does. With no public default it fails for everyone except the handful of principals somebody mapped by hand.

SQL Server Agent pointed at nothing

Agent does not use the default profile. It uses the one named in its own settings, and an Agent with mail switched off never sends a job failure notification no matter how healthy Database Mail is.

This page reads that setting and says so. Agent Settings is where it lives.

Database Mail XPs switched off at the instance

Everything below it is then decoration.

Reading the chart

The bar: mail items sent through each profile
One profile, with the 380 mail items msdb still holds for it. The findings are in the line under the header: no default public profile, Agent sending no mail, and an account sending without SSL.

One bar per profile, drawn from the mail items msdb still holds rather than from a window, because a profile that has never sent anything is itself the finding.

A profile with no account is called out rather than drawn as a short bar. It can be named, it can be selected, and every message sent through it fails.

The grid is the reach

The grid: one row per profile and account pair
One row per profile and account pair, which is the shape that makes a profile with no account visible. The verdict counts the messages that have failed on this account.

One row per profile and account pair, plus a row for each half that is joined to nothing.

That shape is the point. A list of profiles and a list of accounts read separately both look perfectly healthy in exactly the case where mail does not work.

Column What it is
Profile The profile, or blank for an account no profile uses.
Tried How many messages have been sent through this profile.
Account The account, or blank for a profile with none.
Sends from The from address and display name on that account.
Mail server The SMTP host.
Port The port.
SSL Whether the connection is encrypted.
Signs in The authentication: Windows, basic with a stored credential, or anonymous.
Who can use it Public, or the principals the profile is mapped to.
Last used The most recent message through this profile.
Verdict Whether this row can send anything, and what is missing when it cannot.

Nothing here shows a password

The SMTP credential is stored encrypted and is not readable from T-SQL at all. What the page shows is whether one is set, which is the part that answers the question somebody is asking.

The toolbar

Control What it does
Mail history Opens Database Mail History.
Alerts and operators Opens Alerts and Operators.

Where the data comes from

Source What it gives
dbo.sysmail_profile The profiles.
dbo.sysmail_account The accounts, their from address and display name.
dbo.sysmail_profileaccount The join, and the sequence number that decides failover order.
dbo.sysmail_server The SMTP host, port, SSL setting and authentication type.
dbo.sysmail_principalprofile Who may use each profile, and which is the default.
dbo.sysmail_mailitems The Tried and Last used figures.
sys.configurations Whether Database Mail XPs are enabled.
xp_instance_regread Agent’s own mail profile setting.

Messages you may see

Database Mail is not set up on this instance. No profiles and no accounts. Nothing on this instance can send a message, including job failure notifications and alerts.

Report Why you would go there
Database Mail History Whether anything has actually gone out, and what happened to it.
Mail Recipients Who is on the receiving end.
Mail Content What is being sent, including the queries attached to it.
Alerts and Operators The other half of the notification chain.
Agent Settings Agent’s own mail profile, which is separate from the default.

Frequently asked questions

Test mail works from SSMS and job failures still send nothing. Because SSMS sends through the profile you pick and Agent sends through the profile in its own settings. Those are different settings and this page shows both.

We have a default profile. Why does the page say there is none? Check whether it is a public default. A private default applies only to the principal it is mapped to, and a script running as somebody else gets nothing.

Why is there a row with a profile and no account? That is the finding. That profile can be named by sp_send_dbmail and every message through it will fail.

Can I see the SMTP password? No, and neither can anybody else from T-SQL. It is stored encrypted. The page says whether one is configured.