Skip to content

DB Mail and Email Alerts with SQL Agent on Linux

    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