Introduction

Imagine that a database periodically needs to report something to the outside world: send an error notification, forward a report, announce the completion of a scheduled processing task, or send the result of a SQL query.

When there is a full-fledged application layer around the database, this is usually handled by the application itself. But if the automation logic has long been living inside SQL Server, maintaining a separate service just for a few notifications can be excessive. This is where Database Mail comes into play. It should not be treated as just a single procedure, but rather as a small subsystem with its own configuration, permissions, and diagnostics. Once you look at it that way, the entire configuration process becomes easier to understand.

Defining the task

At first glance, everything seems simple: to send an email from SQL Server, you add a call to sp_send_dbmail inside a stored procedure, a SQL Agent job, or a service routine.

In practice, however, the first attempt rarely results in a successfully delivered email. One of the most common symptoms looks like this:

Executed as user: NT SERVICE\SQLAgent.

SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component

'Database Mail XPs' because this component is turned off...

The problem can exist at several levels at once: the component is disabled, the profile has not been created, the user does not have the required permissions, or the email is placed in the queue but then fails on the SMTP side because of TLS, authentication, or network policy.

You need to validate not only the sp_send_dbmail call itself, but the entire chain around it.

What Database Mail is and how it works

In general terms, the process looks like this:

• You call msdb.dbo.sp_send_dbmail.
• SQL Server stores the send request in msdb.
• The email is then processed by the Database Mail component and an external mail process.
• Only after that does the actual SMTP interaction with the mail server take place.

So, a successful procedure call confirms that the email has been queued, but it does not guarantee delivery. This is important to keep in mind.

Before configuration, it makes sense to check several basic things:

• you have an SMTP server that allows sending for the required account;
• you know the sender address, username, password, port, and encryption requirements;
• the SQL Server host has network access to the SMTP host and the required port;
• Windows Firewall, network ACLs, proxies, and firewalls do not block the outgoing SMTP connection;
• you understand which login or SQL Agent job will be calling sp_send_dbmail;
• you are not going to store real SMTP passwords in source code, migration scripts, or a repository.

The last point may sound obvious, but this is exactly where security is often compromised. A configuration script with a real password committed to Git is no longer automation — it is a ready-made incident.

Next, to send an email, we will go through the following steps: enable the component, create an account and profile, grant permissions, and run a test.

Step 1. Enabling Database Mail XPs

If the component is disabled, sp_send_dbmail will not work. It is enabled in the standard way:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

After that, it is useful to immediately check the current state:

EXEC sp_configure 'Database Mail XPs';

If you use email notifications specifically from SQL Server Agent jobs, after enabling the component it is also useful to check the Agent settings and, if necessary, restart the SQL Server Agent service so that it correctly picks up the notification configuration.

Step 2. Creating an account and mail profile

The configuration can be done through the wizard in SSMS. This is the fastest and simplest path:

• Open SSMS.
• Go to Management -> Database Mail.
• Start the configuration wizard.
• Create an SMTP account: server, port, username, password, sender address.
• Create a mail profile and link it to the account.
• If necessary, make the profile public or assign it to specific users.

But you can also use T-SQL:

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = N'OperationsAccount',
@description = N'SMTP account for SQL Server notifications',
@email_address = N'sql-notify@example.com',
@display_name = N'SQL Server Notifications',
@replyto_address = N'support@example.com',
@mailserver_name = N'smtp.example.com',
@port = 587,
@enable_ssl = 1,
@username = N'sql-notify@example.com',
@password = N'<SECRET>';
GO

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = N'OperationsProfile',
@description = N'Profile for operational SQL notifications';
GO

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = N'OperationsProfile',
@account_name = N'OperationsAccount',
@sequence_number = 1;
GO

If the profile should be available to all msdb users, it can be made public:

EXEC msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = N'OperationsProfile',
@principal_name = N'public',
@is_default = 0;
GO

A public profile is convenient for quick starts. But if access to email sending needs to be restricted, it is better to use a private profile and explicitly assign it only to the logins and users that actually need it.

If @profile_name is not specified in the sp_send_dbmail call, SQL Server first tries to use the current user’s default private profile, and then the default public profile. This is convenient, but in production it is usually better to specify the profile explicitly in code so that behavior does not depend on hidden default settings.

Step 3. Access permissions for the profile

This is another area where confusion often occurs. The mere fact that a profile exists is not enough. The calling user must have permission to use Database Mail in msdb.

Usually, this is done by adding the user to the DatabaseMailUserRole role:

USE msdb;
GO
ALTER ROLE DatabaseMailUserRole ADD MEMBER [PlamarJobUser];
GO

If email is needed specifically for SQL Server Agent notifications, it also makes sense to check the Database Mail profile in the SQL Server Agent properties on the Alert System tab. Otherwise, a situation where sp_send_dbmail works manually but Agent notifications do not arrive is entirely realistic.

Step 4. Sending a test email

Next, let’s try sending a test email and save the mailitem_id for further diagnostics:

DECLARE @mailitem_id INT;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'OperationsProfile',
@recipients = N'recipient@example.com',
@subject = N'SQL Server Notification',
@body = N'<h1>Database Mail works</h1><p>This is a test email from SQL Server.</p>',
@body_format = 'HTML',
@mailitem_id = @mailitem_id OUTPUT;

SELECT @mailitem_id AS mailitem_id;

If you need not just a text email, but, for example, the result of a SQL query, sp_send_dbmail can do that as well:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'OperationsProfile',
@recipients = N'report@example.com',
@subject = N'Nightly SQL report',
@body = N'The result of the nightly query is attached.',
@query = N'SELECT TOP (10) Id, Status, CreatedAt FROM dbo.Orders ORDER BY CreatedAt DESC;',
@attach_query_result_as_file = 1,
@query_attachment_filename = N'orders.csv',
@query_result_separator = N';';

How to verify that the email was actually sent

This is the most important practical layer. You need to check not only that the procedure was called, but also the state of the email in msdb.

SELECT TOP (20)
mailitem_id,
sent_status,
send_request_date,
sent_date,
recipients,
subject
FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC;

The Database Mail log is also useful for troubleshooting:

SELECT TOP (50)
log_date,
event_type,
description
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC;

If you are specifically interested in failed sends, sysmail_faileditems is also useful:

SELECT TOP (20)
mailitem_id,
subject,
recipients,
last_mod_date,
last_mod_user
FROM msdb.dbo.sysmail_faileditems
ORDER BY mailitem_id DESC;

Common errors and how to troubleshoot them

Let’s go from simple to more complex. We will first check whether the component is enabled and whether permissions exist, then look at the queue status, and only after that move on to SMTP, TLS, and the network layer.

Database Mail XPs is disabled

This is the most obvious case. It is exactly what leads to the message saying that Database Mail XPs is turned off. The solution is simple: enable the component through sp_configure and verify the result.

The profile is not found or is not available to the calling user

If the profile exists but the calling login does not have permissions, sp_send_dbmail will not be able to use it. Here, you need to check:

• whether the profile exists;
• whether it is public or private;
• whether the user has the DatabaseMailUserRole role in msdb;
• under which identity the SQL Agent job or procedure is actually being executed.

The last point is often decisive. Everything works manually under an administrator account, but fails inside a job.

The email is queued but not sent

This is already a more interesting situation. Here, you should check:

• the Database Mail status;
• the queue in sysmail_allitems;
• the sysmail_event_log;
• whether Service Broker is enabled in msdb;
• whether the mail queue is stopped.

You can check the status like this:

EXEC msdb.dbo.sysmail_help_status_sp;

If Database Mail is stopped, it can be started:

EXEC msdb.dbo.sysmail_start_sp;

Check Service Broker in msdb:

SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';

If is_broker_enabled = 0, that is already a separate administrative problem. It should be fixed carefully and usually only during a database maintenance window.

SMTP, authentication, and TLS errors

Here you need to look at the error description in sysmail_event_log. Usually, that is where you can see what went wrong: incorrect username or password, relay denied, TLS handshake error, inability to connect to the host, or a timeout.

Attachment errors

If you attach a file through @file_attachments, SQL Server must have real file-system-level access to it. Here, the important context is the SQL Server service context, not the current interactive administrator session. When the file “exists” locally but Database Mail cannot see it, the problem is usually permissions or the path.

TLS 1.2 and modern security restrictions

This is the part that older instructions often describe too simplistically. A modern SMTP server usually requires not just “some encryption,” but specific TLS support, a compatible .NET version, and correct Windows system settings.

If the SMTP server expects TLS 1.2, it makes sense to check the following:

• current Windows and SQL Server updates are installed;
• TLS 1.2 support is available on the server;
• .NET Framework and system crypto settings do not block the required handshake;
• a secure connection is enabled in the Database Mail settings.

A typical example of registry settings for enabling strong crypto looks like this:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

After changing these settings, a restart of the SQL Server service is usually required, and sometimes a full server restart, so that Schannel and .NET reliably pick up the new parameters.

There are three practical nuances here.

First, for Database Mail it is usually better to target port 587 with TLS/STARTTLS. Port 465 is often mentioned in older instructions as an option, but in practice it often proves problematic because Database Mail relies on the .NET SMTP stack and works best with STARTTLS rather than implicit SMTPS.

Second, if you use Microsoft 365 or another modern cloud email service, check not only TLS but also the authentication model. Database Mail does not support a modern OAuth flow. If the provider has disabled basic SMTP AUTH, the email will not be sent even if the T-SQL is correct. In such cases, organizations usually use either a dedicated SMTP relay or a mailbox for which SMTP AUTH is allowed by policy.

Third, the SSL checkbox in the SSMS wizard effectively means that the server side must support a correct SMTP-over-TLS scenario. So you should look not only at the UI, but also at the actual compatibility between your SMTP endpoint and Database Mail.

Limitations and pitfalls

• sp_send_dbmail confirms that the message has been queued, not that it has been delivered to the user’s mailbox.
• Database Mail depends on SMTP infrastructure, network connectivity, DNS, TLS policy, and authentication rules outside SQL Server.
• It does not support modern OAuth scenarios for cloud email, so not every corporate mail service will work without a relay or special configuration.
• For attachments, the relevant permissions are those of the SQL Server service account, not the administrator account under which you opened SSMS.
• Attachment size and the list of prohibited extensions are limited by Database Mail settings and can unexpectedly break working scenarios.
• Database Mail is available in SQL Server Database Engine and Azure SQL Managed Instance, but not in Azure SQL Database or elastic pools.

Summary

In summary, Database Mail is best suited for service notifications, scheduled reports, and technical mailings. I would not stretch it toward complex email integration with business logic, templating, and modern cloud-auth scenarios. In those cases, it very quickly stops being convenient.

Useful links

• Official Database Mail documentation
• sp_send_dbmail documentation
• General Database Mail troubleshooting steps