title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | ms.custom | |
---|---|---|---|---|---|---|---|---|---|
Database Mail and email alerts with SQL Server Agent on Linux |
Learn how to use Database Mail and how to set up Email Alerts with SQL Server Agent (mssql-server-agent) on Linux. |
rwestMSFT |
randolphwest |
11/30/2023 |
sql |
linux |
conceptual |
|
[!INCLUDE SQL Server - Linux]
This article shows how to set up Database Mail and use it with SQL Server Agent (mssql-server-agent) on Linux.
USE master;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLAlerts',
@description = 'Account for Automated DBA Notifications',
@email_address = 'sqlagenttest@example.com',
@replyto_address = 'sqlagenttest@example.com',
@display_name = 'SQL Agent',
@mailserver_name = 'smtp.example.com',
@port = 587,
@enable_ssl = 1,
@username = 'sqlagenttest@example.com',
@password = '<password>';
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'default',
@description = 'Profile for sending Automated DBA Notifications';
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'default',
@principal_name = 'public',
@is_default = 1;
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'default',
@account_name = 'SQLAlerts',
@sequence_number = 1;
GO
You might have to go to your email client and enable the allow less secure clients to send mail option. Not all clients recognize Database Mail as an email daemon.
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'recipient-email@example.com',
@subject = 'Testing DBMail',
@body = 'This message is a test for DBMail'
GO
You can use the mssql-conf utility, or environment variables, to register your Database Mail profile. In this case, let's call our profile default
.
-
Set via mssql-conf:
sudo /opt/mssql/bin/mssql-conf set sqlagent.databasemailprofile default
-
Set via environment variable:
MSSQL_AGENT_EMAIL_PROFILE=default
EXEC msdb.dbo.sp_add_operator
@name = N'JobAdmins',
@enabled = 1,
@email_address = N'recipient-email@example.com',
@category_name = N'[Uncategorized]';
GO
EXEC msdb.dbo.sp_update_job
@job_name = 'Agent Test Job',
@notify_level_email = 1,
@notify_email_operator_name = N'JobAdmins'
GO