0

SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2017-04-14T21:34:10). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )

I have tried several ways of setting up Database Mail, a mixture of precreated scripts and following the interface in SSMS.

I have also tried two different mail server credentials (Google and a Web Hosting Company), and both fail with the same error.

The Web Hosting Credentials are used on a PHP Script on another Server which works perfectly fine.

What should I check to get this working?

Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300
DARKOCEAN
  • 47
  • 1
  • 8
  • I would check the validity of your smtp server and email address. – SqlWorldWide Apr 14 '17 at 21:40
  • There's nothing wrong with it - as I said, it's used for other things perfectly fine. Plus, I've tried two different SMTP Servers - both issues are the same. – DARKOCEAN Apr 14 '17 at 21:40
  • If you know the SMTP servers work, then the next most likely issue is a problem with the Database Mail configuration – RDFozz Apr 14 '17 at 22:29
  • Yes, I'm expecting it to be something server related, but what should I check? I've followed all sorts of guides (which for whatever reason say to do different things when configuring). – DARKOCEAN Apr 14 '17 at 22:42
  • Do you have any firewall rules that are blocking outbound traffic for the smtp port you are using? – Matt McDonald Apr 15 '17 at 03:58
  • Nope, I even added the port to the firewall to make sure it works. – DARKOCEAN Apr 15 '17 at 08:47
  • I'd temporarily remove sql server from the equation and try sending an email via powershell to see if there is something outside of sql server that is causing the problem- here's a post in powershell email - https://practical365.com/exchange-server/powershell-how-to-send-email/ – Scott Hodgin - Retired Apr 15 '17 at 10:35
  • Make sure you have the correct port number and SSL checkbox checked if required. I know for Google the port number is 587 and the SSL checkbox must be checked. – Jason Apr 15 '17 at 13:24
  • Now that's interesting as Google state it should be 465 for SSL? I have just managed to get it working using my web hosting credentials using the non-secure method. Can't seem to get it working with SSL but I guess that doesn't matter - there isn't any sensitive information. – DARKOCEAN Apr 15 '17 at 14:39

1 Answers1

3

I was having a different issue about a month ago. I came across this script that should help troubleshooting.

 USE msdb
 GO

-- Check that the service broker is enabled on MSDB. 
-- Is_broker_enabled must be 1 to use database mail.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Check that Database mail is turned on. 
-- Run_value must be 1 to use database mail.
-- If you need to change it this option does not require
-- a server restart to take effect.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';

-- Check the Mail queues
-- This system stored procedure lists the two Database Mail queues.  
-- The optional @queue_type parameter tells it to only list that queue.
-- The list contains the length of the queue (number of emails waiting),
-- the state of the queue (INACTIVE, NOTIFIED, RECEIVES_OCCURRING, the 
-- last time the queue was empty and the last time the queue was active.
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'Mail' ;

-- Check the status (STARTED or STOPPED) of the sysmail database queues
-- EXEC msdb.dbo.sysmail_start_sp -- Start the queue
-- EXEC msdb.dbo.sysmail_stop_sp -- Stop the queue
EXEC msdb.dbo.sysmail_help_status_sp;

-- Check the different database mail settings.  
-- These are system stored procedures that list the general 
-- settings, accounts, profiles, links between the accounts
-- and profiles and the link between database principles and 
-- database mail profiles.
-- These are generally controlled by the database mail wizard.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
--  Check that your server name and server type are correct in the
--      account you are using.
--  Check that your email_address is correct in the account you are
--      using.
EXEC msdb.dbo.sysmail_help_profile_sp;
--  Check that you are using a valid profile in your dbmail command.
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
--  Check that your account and profile are joined together
--      correctly in sysmail_help_profileaccount_sp.
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_event_log 
ORDER BY last_mod_date DESC;

-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_allitems 
ORDER BY last_mod_date DESC;

-- Check the emails that actually got sent. 
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_sentitems 
ORDER BY last_mod_date DESC;

-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
SELECT TOP 100 * 
FROM msdb.dbo.sysmail_faileditems 
ORDER BY last_mod_date DESC

-- Clean out unsent emails
-- Usually I do this before releasing the queue again after fixing the 
problem.
-- Assuming of course that I don't want to send out potentially thousands of 
-- emails that are who knows how old.
-- Obviously can be used to clean out emails of any status.
EXEC msdb.dbo.sysmail_delete_mailitems_sp  
  @sent_before =  '2017-03-16',
  @sent_status = 'failed';
Jason
  • 339
  • 1
  • 4
  • 14