Database Mail 101 Troubleshooting
Shawn Meyers (@
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.
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.
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:
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 = 'email@example.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.