Shawn Meyers (@1dizzygoose), Principal Architect 

This blog covers the basics of troubleshooting Database Mail, and is more tailored to issues related to setup rather than installations that have been working for a while and all of a sudden have stopped. In the coming weeks, there will be two more blog posts from House of Brick regarding more advanced troubleshooting for Database Mail, but this blog gets things started with an introduction to troubleshooting Database Mail.

If you have many SQL Server instances you may already have a script to setup Database Mail, and if you use a script, you probably don’t have too many setup issues. However, if you are new, or just have a few servers, you may be setting it up manually. At the end of this blog, there is a script we use to set Database Mail defaults to anonymous access.

Is it Enabled?

First to check to make sure that Database Mail is enabled. Look in ‘sys.configurations’ for the setting Database Mail XPs, and if it is set to 0, Database Mail is not enabled.

The value column shows ‘0’, so Database Mail is off. To enable it, you can run a simple script to change the value to ‘1’, as shown below.

EXEC sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE 

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

An alternative for enabling Database Mail is under Management => Database Mail => Configure Database Mail. Select any of the configuration options, and you will see a message stating that Database Mail is not available and asking if you want to enable it (as shown in the figure below).

Next, the most common error we run into is that Database Mail has stopped for some reason. To start Database Mail, run the following script.

EXEC msdb.dbo.sysmail_start_sp ;

If there are configuration errors, the script will show you an error, which points you in the direction of the issue.

Now check the log. Database Mail provides a decent logging system, but there are things it isn’t good at, which will be discussed in more detail in future posts describing its more advanced features. However, most basic items are logged properly as shown below.

Profile and Accounts
Next, check to ensure the profile and users are setup correctly. Since I have never seen a profile setup incorrectly, I’m not going to cover it in this blog.

On the other hand, I see issues with accounts all the time. Besides obvious typos in the email address or SMTP server fields, most issues revolve around authentication. By default, Database Mail assumes you are sending emails to a SMTP server, which accepts anonymous messages. Inside an organization’s network this used to be a very common practice, but today it is becoming less and less so. Some organizations no longer have email servers locally, and the SMTP relay is in the cloud somewhere, and as such requires authentication or whitelisting.

Authentication
For information about authentication, you will need to talk to your email admin who should be able to explain how to setup SMTP. If you are using a mail server, and can connect with AD credentials, then Database Mail will connect with SQL Server service account when you select ‘Windows Authentication’. In the screen shot below, you can see what information is needed for a mail service like Gmail to connect. Notice using the port number and Gmail requires a secure connection. While the SSL label is not accurate, as Gmail requires TLS connections, the concept is similar and everyone understands SSL.

Whitelisting
Many IT devices can send SMTP emails when something goes wrong, but some can only be used with anonymous messages. Organizations need to secure their email servers, so they use a process called whitelisting. This is basically a network rule, or rule on the email server, that only allows anonymous SMTP connections from certain addresses or hostnames. Your email administrators will know if this is the case for your organization, so you will most likely need to open a ticket with them to add your SQL Servers to the whitelist. If all of your servers are on a dedicated network subnet, you may be able to get the whole subnet listed, but expect to have to add each server individually. If this is the case at your organization, you need to add a whitelisting step to your build checklist, or you may forget about it at the worst possible time.

To validate that things are working properly and are not being blocked, a simple Telnet test can confirm if your SQL Server can reach the SMTP server.

TELNET SMTP.CLIENT.COM 25

This command should connect to the mail server. If it times out or doesn’t respond, something is blocking your access to the email server, and nothing you do inside of SQL Server will fix the issue. You will need to work with your network and email administrators to resolve the issue.

If it does connect, then type the following command:

ELHO SMTP.CLIENT.COM

You should receive feedback from the mail server, which will tell you everything is working correctly and the problem isn’t in communications with the mail server.

SQL Agent Enabled

If Database Mail is working, but your SQL Agent jobs are not alerting you via email, the first thing you need to do is verify that the SQL Agent has a mail profile enabled. If you removed a profile recently, and if it was the one SQL Agent was using, SQL Agent jobs will stop sending emails.

To verify, the step is as simple as ensuring the ‘Enable mail profile’ check box is checked, and confirming a valid mail profile is selected.

Testing Database Mail

Make sure you test Database Mail from both of the ‘Sent Test Email’ options under the Database Mail configuration as well as send one via SQL Server Agent from a job result. The Database Mail script at the end of this post creates a test job for just this purpose.

Migrating Database Mail

Another common issue is migrating a working Database Mail configuration from an old server to a new server. The good news is that someone has already written a blog post about this, and you can read it here.

Database Mail Setup Script

USE msdb

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @display_name NVARCHAR(128),
        @String NVARCHAR(2000);

SET @profile_name = 'Default'; -- select profile name
SET @account_name = 'SQLAdmin'; -- select account and operator name
SET @display_name = @@ServerName; --set servname to be used for display
SET @SMTP_servername = 'smtp.client.com'; -- INSERT SMTP ADDRESS HERE
SET @email_address = 'noreply@company.com'; -- INSERT EMAIL ACCT HERE

--enable DBmail
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

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

EXEC sp_configure 'Agent XPs',1
RECONFIGURE WITH OVERRIDE

--service broker is required for dbmail to work
IF (SELECT Is_Broker_Enabled FROM master.sys.databases WHERE name = 'msdb') = 0
BEGIN
      ALTER DATABASE msdb set restricted_user with rollback immediate
      ALTER DATABASE msdb SET ENABLE_BROKER
      ALTER DATABASE msdb set multi_user with rollback immediate
END
--create the account and profile
IF (SELECT 1 FROM [msdb].[dbo].[sysmail_account] WHERE name = @account_name) IS NULL
BEGIN
      EXECUTE msdb.dbo.sysmail_add_account_sp
              @account_name = @account_name,
              @description = 'Default Mail Account',
              @email_address = @email_address,
              @display_name = 'DBA Alerts',
              @replyto_address = @email_address,
              @mailserver_name = @SMTP_servername

      EXECUTE msdb.dbo.sysmail_add_profile_sp
              @profile_name = @profile_name,
              @description = 'Default Profile'

      EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
              @profile_name = @profile_name,
              @account_name = @account_name,
              @sequence_number = 1

      EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
              @profile_name = @profile_name,
              @principal_name = 'public',
              @is_default = 1
END

--create an operator with same name as the account
IF NOT EXISTS(SELECT name FROM msdb.dbo.sysoperators WHERE name = @account_name)
BEGIN
      EXEC msdb.dbo.sp_add_operator @name=@account_name,
             @enabled=1,
             @weekday_pager_start_time=0,
             @weekday_pager_end_time=235959,
             @saturday_pager_start_time=0,
             @saturday_pager_end_time=235959,
             @sunday_pager_start_time=0,
             @sunday_pager_end_time=235959,
             @pager_days=127,
             @email_address=@email_address,
             @pager_address=@email_address,
             @netsend_address=N''
END
--enable dbmail for SQL Server Agent alerts
IF (SELECT 1 WHERE @@version LIKE ('%SQL Server 201%')) = 1
      BEGIN
             EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=@profile_name
      END
ELSE
      BEGIN
             EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
             EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @profile_name
             EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
      END
--set failsafe operator
EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=@account_name, @notificationmethod=1

--Create test job to test dbmail
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT [name] FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

IF EXISTS(SELECT [name] FROM msdb.[dbo].[sysjobs] WHERE [name] = 'TestJob')
EXEC msdb.dbo.sp_delete_job @job_name=N'TestJob'

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestJob',
             @enabled=1,
             @notify_level_eventlog=0,
             @notify_level_email=3,
             @notify_level_netsend=0,
             @notify_level_page=0,
             @delete_level=0,
             @description=N'No description available.',
             @category_name=N'Database Maintenance',
             @owner_login_name=N'sa',
             @notify_email_operator_name=@account_name, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'One',
             @step_id=1,
             @cmdexec_success_code=0,
             @on_success_action=1,
             @on_success_step_id=0,
             @on_fail_action=2,
             @on_fail_step_id=0,
             @retry_attempts=0,
             @retry_interval=0,
             @os_run_priority=0, @subsystem=N'TSQL',
             @command=N'SELECT GETDATE()',
             @database_name=N'master',
             @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
-- run test job
EXEC msdb..sp_start_job 'TestJob'

Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.