How to Set Up IIS URL Rewrite on Windows Server 2022

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

Database Mail is SQL Server’s built-in SMTP mail subsystem, enabling the database engine and SQL Server Agent to send email notifications directly without requiring an external mail client. On Windows Server 2022 with SQL Server 2019 or 2022, Database Mail is the standard mechanism for job failure alerts, performance threshold notifications, and operational reports. This article covers every configuration step from enabling the feature to diagnosing mail queue failures.

Enabling the Database Mail XPs Feature

Database Mail is disabled by default in fresh SQL Server installations. Enable it using sp_configure. This is a server-level setting stored in sys.configurations and does not require a service restart:

USE master;
GO

-- Enable advanced options first if they are not already enabled
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

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

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

A value_in_use of 1 confirms Database Mail is active. If you are locked into a hardened configuration that disables sp_configure for non-sysadmin logins, connect as a sysadmin login or use the Policy-Based Management surface area facet to enable it instead.

Creating a Mail Profile

A profile is a named collection of mail accounts. SQL Server Agent uses a profile (not an account directly) to send mail. A profile can have multiple accounts, tried in priority order for failover. Create the profile first:

USE msdb;
GO

-- Create a private profile (accessible only by explicitly-granted principals)
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = N'DBA Notification Profile',
    @description  = N'Primary SMTP profile for SQL Agent alerts and monitoring emails';
GO

Profiles are stored in msdb.dbo.sysmail_profile. Query this view to list existing profiles:

SELECT profile_id, name, description
FROM   msdb.dbo.sysmail_profile
ORDER  BY name;
GO

Creating an SMTP Account

An account holds the SMTP server connection details. Most environments use either an internal Exchange/SMTP relay or an external service such as SendGrid, Office 365, or Amazon SES. The example below uses an internal SMTP relay with Windows Authentication:

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name            = N'SQLServer SMTP Account',
    @description             = N'Internal SMTP relay for SQL Server mail',
    @email_address           = N'[email protected]',
    @display_name            = N'SQL Server WIN2022',
    @replyto_address         = N'[email protected]',
    @mailserver_name         = N'smtp.contoso.com',
    @mailserver_type         = N'SMTP',
    @port                    = 25,
    @username                = NULL,      -- NULL = anonymous/relay
    @password                = NULL,
    @use_default_credentials = 1,         -- Use SQL Agent service account credentials
    @enable_ssl              = 0;
GO

For Office 365 (SMTP AUTH with TLS on port 587):

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name            = N'O365 SMTP Account',
    @description             = N'Office 365 SMTP relay',
    @email_address           = N'[email protected]',
    @display_name            = N'SQL Server Alerts',
    @replyto_address         = N'[email protected]',
    @mailserver_name         = N'smtp.office365.com',
    @mailserver_type         = N'SMTP',
    @port                    = 587,
    @username                = N'[email protected]',
    @password                = N'AppPasswordOrClientSecret#123',
    @use_default_credentials = 0,
    @enable_ssl              = 1;          -- STARTTLS
GO

Account credentials stored in sysmail_add_account_sp are encrypted using SQL Server’s internal key. Store the password in a secure vault as well since it cannot be retrieved in plaintext after creation.

Associating the Account with the Profile

A profile is useless without at least one account. The sequence_number parameter controls account priority — Database Mail tries account 1 first, then account 2 if the first fails, and so on:

EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name   = N'DBA Notification Profile',
    @account_name   = N'SQLServer SMTP Account',
    @sequence_number = 1;
GO

-- If you have a backup SMTP server, add it as account 2
-- EXEC msdb.dbo.sysmail_add_profileaccount_sp
--     @profile_name   = N'DBA Notification Profile',
--     @account_name   = N'Backup SMTP Account',
--     @sequence_number = 2;
GO

Granting Access to the Profile

By default, only sysadmin members can use Database Mail. Grant specific logins or all users access to the profile using sysmail_add_principalprofile_sp. Setting @is_default = 1 marks this profile as the default when no profile is specified in sp_send_dbmail:

-- Grant access to a specific SQL login
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name   = N'DBA Notification Profile',
    @principal_name = N'SQLAgentUser',
    @is_default     = 1;
GO

-- Grant access to all database users (principal_id = 0 = public role)
EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name   = N'DBA Notification Profile',
    @principal_name = N'public',
    @is_default     = 1;
GO

Sending a Test Email

Verify the configuration by sending a test message. The email is queued in msdb and delivered asynchronously by the Database Mail external process (DatabaseMail.exe):

EXEC msdb.dbo.sp_send_dbmail
    @profile_name  = N'DBA Notification Profile',
    @recipients    = N'[email protected]',
    @subject       = N'Database Mail Test - SQL Server WIN2022',
    @body          = N'This is a test message from Database Mail on WIN2022. If you received this, Database Mail is configured correctly.',
    @body_format   = N'TEXT';
GO

Send an HTML-formatted email with file attachment:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name  = N'DBA Notification Profile',
    @recipients    = N'[email protected];[email protected]',
    @copy_recipients = N'[email protected]',
    @subject       = N'Nightly Backup Report - ' + CONVERT(VARCHAR, GETDATE(), 120),
    @body          = N'
Backup Summary
All backups completed successfully.
',
    @body_format   = N'HTML',
    @file_attachments = N'C:SQLLogsbackup_report.csv',
    @query         = N'SELECT name, backup_start_date, backup_finish_date, type FROM msdb.dbo.backupset WHERE backup_start_date >= DATEADD(day,-1,GETDATE()) ORDER BY backup_start_date DESC',
    @attach_query_result_as_file = 1,
    @query_result_header = 1,
    @query_attachment_filename = N'recent_backups.csv';
GO

Configuring SQL Server Agent to Use the Profile

SQL Server Agent must be told which Database Mail profile to use for its own notifications. In SSMS, right-click SQL Server Agent > Properties > Alert System tab > check Enable mail profile > select Mail system: Database Mail > choose the Mail Profile from the dropdown > click OK. Via T-SQL:

USE msdb;
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
    @email_save_in_sent_folder = 1,
    @databasemail_profile      = N'DBA Notification Profile';
GO

-- Restart SQL Server Agent service for the change to take effect
-- Use PowerShell (run from the server, not within SQL Server):
-- Restart-Service -Name SQLSERVERAGENT

After restarting the Agent, open the Agent properties again and confirm the Mail Profile is still selected — it should persist after restart.

Configuring an Operator for Email Notifications

EXEC msdb.dbo.sp_add_operator
    @name                         = N'DBA Team',
    @enabled                      = 1,
    @email_address                = N'[email protected]',
    @weekday_pager_start_time     = 90000,
    @weekday_pager_end_time       = 180000,
    @saturday_pager_start_time    = 90000,
    @saturday_pager_end_time      = 180000,
    @sunday_pager_start_time      = 90000,
    @sunday_pager_end_time        = 180000,
    @pager_days                   = 0;
GO

-- Add notification on job failure
EXEC msdb.dbo.sp_update_job
    @job_name                  = N'Weekly Index Maintenance',
    @notify_level_email        = 2,           -- 1=success, 2=failure, 3=completion
    @notify_email_operator_name = N'DBA Team';
GO

Monitoring the Mail Queue

Database Mail uses three views in msdb to expose queue state:

-- All sent and unsent items (last 30 days by default)
SELECT TOP 20
    mailitem_id,
    send_request_date,
    sent_date,
    sent_status,
    recipients,
    subject,
    body
FROM   msdb.dbo.sysmail_allitems
ORDER BY send_request_date DESC;
GO

-- Only failed items
SELECT mailitem_id, send_request_date, sent_date,
       sent_status, recipients, subject, last_mod_date
FROM   msdb.dbo.sysmail_faileditems
ORDER BY send_request_date DESC;
GO

-- Event log for the DatabaseMail.exe external process
SELECT TOP 50
    log_id,
    event_type,
    log_time,
    description
FROM   msdb.dbo.sysmail_event_log
ORDER BY log_time DESC;
GO

The sent_status column in sysmail_allitems will be sent, failed, or unsent. Messages stuck in unsent after several minutes indicate the Database Mail external process is not running — stop and restart it:

-- Stop the Database Mail queue processor
EXEC msdb.dbo.sysmail_stop_sp;
GO

-- Start it again
EXEC msdb.dbo.sysmail_start_sp;
GO

-- Check the current queue status
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
GO

Troubleshooting Common Mail Failures

Authentication failure (SMTP AUTH): Check the account credentials in msdb.dbo.sysmail_account. You cannot read the stored password, but you can update it: EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'O365 SMTP Account', @password = N'NewPassword#456'. For Office 365, verify the account has SMTP AUTH enabled in the Microsoft 365 admin centre (Settings > Mail > SMTP AUTH).

Connection timeout / SMTP relay not reachable: Test TCP connectivity from the SQL Server host to the SMTP server using Test-NetConnection in PowerShell:

Test-NetConnection -ComputerName smtp.office365.com -Port 587

If TcpTestSucceeded is False, check Windows Defender Firewall outbound rules and verify the network route to the SMTP server.

SSL/TLS certificate errors: When @enable_ssl = 1, the SQL Server host must trust the certificate presented by the SMTP server. If the mail server uses an internal PKI certificate, install the root CA certificate into the Windows Trusted Root Certification Authorities store on WIN2022 using certlm.msc.

Attachment encoding issues: Large attachments can cause timeouts. The default maximum attachment size is controlled by sysmail_configure_sp:

-- Increase the maximum attachment size to 10 MB
EXEC msdb.dbo.sysmail_configure_sp
    @parameter_name  = N'MaxFileSize',
    @parameter_value = N'10485760';  -- bytes
GO

-- View all Database Mail configuration parameters
EXEC msdb.dbo.sysmail_help_configure_sp;
GO

Cleaning Up Old Mail Items

The mail log grows indefinitely unless pruned. Schedule a weekly cleanup job:

-- Delete all sent mail items older than 30 days
EXEC msdb.dbo.sysmail_delete_mailitems_sp
    @sent_before = DATEADD(day, -30, GETDATE()),
    @sent_status = N'sent';
GO

-- Delete all log entries older than 30 days
EXEC msdb.dbo.sysmail_delete_log_sp
    @logged_before  = DATEADD(day, -30, GETDATE()),
    @event_type     = N'success';
GO

Database Mail on Windows Server 2022 provides a reliable, queue-based email delivery mechanism that is resilient to temporary SMTP outages and requires no additional dependencies beyond a reachable SMTP relay. By combining it with SQL Server Agent operators, alerts, and job notifications, you can build a comprehensive alerting system that immediately notifies your team of critical database events without any third-party notification tooling.