The following steps show you how to set up DB Mail and use it with SQL Server Agent (mssql-server-agent) on Linux.
1. Enable DB Mail
SQL
USE master
GO
sp_configure ‘show advanced options’,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO
2. Create a new account
SQL
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQLAlerts’,
@description = ‘Account for Automated DBA Notifications’,
@email_address = ‘[email protected]’,
@replyto_address = ‘[email protected]’,
@display_name = ‘SQL Agent’,
@mailserver_name = ‘smtp.gmail.com’,
@port = 587,
@enable_ssl = 1,
@username = ‘[email protected]’,
@password = ‘<password>’
GO
3. Create a default profile
SQL
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘default’,
@description = ‘Profile for sending Automated DBA Notifications’
GO
4. Add the Database Mail account to a Database Mail profile
SQL
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘default’,
@principal_name = ‘public’,
@is_default = 1 ;
5. Add account to profile
SQL
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘default’,
@account_name = ‘SQLAlerts’,
@sequence_number = 1;
6. Send test email
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = ‘default’,
@recipients = ‘[email protected]’,
@Subject = ‘Testing DBMail’,
@Body = ‘This message is a test for DBMail’
GO
7. Set DB Mail Profile using mssql-conf or environment variable
You can use the mssql-conf utility or environment variables to register your DB Mail profile. In this case, let’s call our profile default.
Bash
# via mssql-conf
sudo /opt/mssql/bin/mssql-conf set sqlagent.databasemailprofile default
# via environment variable
MSSQL_AGENT_EMAIL_PROFILE=default
8. Set up an operator for SQLAgent job notifications
SQL
EXEC msdb.dbo.sp_add_operator
@name=N’JobAdmins’,
@enabled=1,
@email_address=N’[email protected]’,
@category_name=N'[Uncategorized]’
GO
9. Send email when ‘Agent Test Job’ succeeds
EXEC msdb.dbo.sp_update_job
@job_name=’Agent Test Job’,
@notify_level_email=1,
@notify_email_operator_name=N’JobAdmins’
GO