How to Set Up Database Mail in SQL Server on Windows Server 2025

Database Mail is the preferred mechanism for sending email notifications directly from SQL Server on Windows Server 2025. It replaces the older SQL Mail feature (which relied on MAPI and Outlook) with a modern SMTP-based architecture that works reliably without a mail client installed on the server. Once configured, Database Mail is used by SQL Server Agent to notify operators about job failures, alerts, and maintenance results. It can also be called directly from T-SQL stored procedures to send custom email messages from within application logic. This tutorial walks through enabling Database Mail, creating a profile and account, testing delivery, monitoring the send queue, and troubleshooting common SMTP issues.

Prerequisites

Windows Server 2025 with SQL Server 2019 or SQL Server 2022 installed
Sysadmin role on the SQL Server instance
An accessible SMTP relay server or mail service (Office 365, Exchange, SendGrid, etc.)
TCP port 25 (or 587 for STARTTLS) open outbound from the Windows Server 2025 host
SQL Server Agent service running (for agent job notifications)
A valid “From” email address permitted by your SMTP relay

Step 1: Enable the Database Mail XPs Configuration Option

Database Mail is a surface area feature that must be enabled at the server level before it can be configured. By default it is disabled for security hardening. Enable it with sp_configure:

USE master;
GO

-- Enable the Database Mail extended stored procedures
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

-- Verify the setting
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name = 'Database Mail XPs';
GO

A value_in_use of 1 confirms Database Mail is active. This setting takes effect immediately and does not require a SQL Server service restart.

Step 2: Create a Database Mail Account

A Database Mail account holds the SMTP connection details — the mail server address, port, authentication, and the “From” address that recipients will see. Accounts are created with sysmail_add_account_sp in the msdb database:

USE msdb;
GO

EXEC sysmail_add_account_sp
    @account_name = 'SQLServer_Notifications',
    @description = 'Primary SMTP account for SQL Server Agent alerts',
    @email_address = '[email protected]',
    @display_name = 'SQL Server 2025 Alerts',
    @replyto_address = '[email protected]',
    @mailserver_name = 'smtp.yourdomain.com',
    @mailserver_type = 'SMTP',
    @port = 587,
    @username = '[email protected]',
    @password = 'SmtpPassword2025!',
    @use_default_credentials = 0,
    @enable_ssl = 1;    -- Enable STARTTLS/SSL for port 587
GO

For an internal unauthenticated SMTP relay (common in corporate environments), omit @username, @password, and set @port = 25 with @enable_ssl = 0:

EXEC sysmail_add_account_sp
    @account_name = 'InternalRelay',
    @email_address = '[email protected]',
    @display_name = 'SQL Server Alerts',
    @mailserver_name = 'mailrelay.yourdomain.local',
    @port = 25,
    @use_default_credentials = 0,
    @enable_ssl = 0;
GO

Step 3: Create a Database Mail Profile

A profile is a named container that groups one or more accounts. SQL Server Agent and sp_send_dbmail reference profiles by name, not accounts directly. Using profiles allows failover to a backup SMTP account if the primary fails:

USE msdb;
GO

-- Create the profile
EXEC sysmail_add_profile_sp
    @profile_name = 'DBAAlerts',
    @description = 'Profile used by SQL Server Agent and stored procedures for DBA notifications';
GO

-- Associate the account with the profile (sequence 1 = first attempt)
EXEC sysmail_add_profileaccount_sp
    @profile_name = 'DBAAlerts',
    @account_name = 'SQLServer_Notifications',
    @sequence_number = 1;
GO

Optional: Add a Failover Account

-- Add a backup SMTP account
EXEC sysmail_add_account_sp
    @account_name = 'BackupSMTP',
    @email_address = '[email protected]',
    @display_name = 'SQL Server Alerts (Backup)',
    @mailserver_name = 'smtp-backup.yourdomain.com',
    @port = 587,
    @enable_ssl = 1,
    @username = '[email protected]',
    @password = 'BackupSmtpPassword2025!',
    @use_default_credentials = 0;
GO

-- Add backup account at sequence 2 (tried if sequence 1 fails)
EXEC sysmail_add_profileaccount_sp
    @profile_name = 'DBAAlerts',
    @account_name = 'BackupSMTP',
    @sequence_number = 2;
GO

Step 4: Grant Profile Access to Logins

By default, only sysadmin members can use Database Mail. Grant access to the DatabaseMailUserRole in msdb or make the profile public so all users can send mail:

USE msdb;
GO

-- Make the profile public (accessible to all database users)
EXEC sysmail_add_principalprofile_sp
    @profile_name = 'DBAAlerts',
    @principal_name = 'public',
    @is_default = 1;     -- Set as the default profile
GO

Step 5: Send a Test Email

Verify the configuration works end-to-end by sending a test message with sp_send_dbmail:

USE msdb;
GO

EXEC sp_send_dbmail
    @profile_name = 'DBAAlerts',
    @recipients = '[email protected]',
    @subject = 'Database Mail Test - WS2025-SQL',
    @body = 'This is a test message from Database Mail on SQL Server 2025 running on Windows Server 2025. If you received this, Database Mail is configured correctly.',
    @body_format = 'TEXT';
GO

Sending HTML Email

EXEC sp_send_dbmail
    @profile_name = 'DBAAlerts',
    @recipients = '[email protected]',
    @subject = 'SQL Server Maintenance Report',
    @body = N'
<html>
<body>
<h3>Maintenance Report - WS2025-SQL</h3>
<p>All maintenance tasks completed successfully.</p>
<table border="1">
<tr><th>Task</th><th>Status</th></tr>
<tr><td>Index Rebuild</td><td>Success</td></tr>
<tr><td>Statistics Update</td><td>Success</td></tr>
</table>
</body>
</html>',
    @body_format = 'HTML';
GO

Step 6: Check Email Send Status

Database Mail processes messages asynchronously via a Service Broker queue. Use the mail system views to inspect message status:

USE msdb;
GO

-- View all sent/failed/retrying messages
SELECT
    mailitem_id,
    profile_name,
    recipients,
    subject,
    sent_status,    -- 'sent', 'failed', 'retrying', 'unsent'
    sent_date,
    last_mod_date
FROM sysmail_allitems
ORDER BY last_mod_date DESC;
GO

-- View only failed messages
SELECT *
FROM sysmail_faileditems
ORDER BY last_mod_date DESC;
GO

-- View event log for SMTP errors and diagnostic messages
SELECT *
FROM sysmail_event_log
WHERE event_type IN ('error', 'warning')
ORDER BY log_date DESC;
GO

Step 7: Configure SQL Server Agent to Use the Database Mail Profile

SQL Server Agent must be told which Database Mail profile to use for sending operator notifications:

USE msdb;
GO

-- Configure Agent to use Database Mail (not SQL Mail)
EXEC sp_set_sqlagent_properties
    @email_save_in_sent_folder = 1,
    @databasemail_profile = N'DBAAlerts',
    @use_databasemail = 1;
GO

Alternatively, configure this in SSMS: right-click SQL Server Agent → Properties → Alert System tab → check Enable mail profile → select DBAAlerts → set mail system to Database Mail.

Restart SQL Server Agent after changing mail profile settings:

Restart-Service -Name SQLSERVERAGENT -Force

Step 8: Troubleshoot DNS and SMTP Relay Issues

Test SMTP Connectivity from Windows Server 2025

# Test TCP connectivity to SMTP server on port 587
Test-NetConnection -ComputerName smtp.yourdomain.com -Port 587

# Check DNS resolution of the mail server
Resolve-DnsName smtp.yourdomain.com

# Test SMTP manually with telnet (install feature first)
Install-WindowsFeature Telnet-Client
telnet smtp.yourdomain.com 587

Check Windows Firewall for Outbound SMTP

# Verify no outbound block on port 587 or 25
Get-NetFirewallRule | Where-Object { $_.Direction -eq 'Outbound' -and $_.Action -eq 'Block' } |
    Select-Object DisplayName, Profile, Enabled

Reset the Database Mail Queue

If the mail queue appears stuck, restart the Database Mail external program:

USE msdb;
GO

-- Stop and restart the Database Mail service
EXEC sysmail_stop_sp;
GO

WAITFOR DELAY '00:00:05';

EXEC sysmail_start_sp;
GO

Conclusion

Database Mail on SQL Server running under Windows Server 2025 provides a reliable, configurable, and modern SMTP-based email delivery system that integrates directly with SQL Server Agent for automated alerting. By creating a properly configured profile and account, granting access to the public role, and verifying end-to-end delivery with sp_send_dbmail, you establish the notification foundation that all other SQL Server automation — job alerts, severity notifications, maintenance reports — depends upon. The sysmail_allitems and sysmail_event_log views are your first stop when diagnosing delivery failures, and simple PowerShell network tests quickly confirm whether the problem lies with SQL Server configuration or the underlying SMTP infrastructure. Once Database Mail is working, connecting it to SQL Server Agent operators unlocks automated alerting across your entire Windows Server 2025 SQL environment.

How to Set Up Database Mail in SQL Server on Windows Server 2025