sp_send_dbmail fails: Cannot send mails to mail server

1 vote
3 answers
12518 views
sql-server sql-server-2014 database-mail
                          I am trying to set up email from my SQL Server 2014 installation. As I have the Express Edition, I do not have the Database mail wizard, but I seem to have all the stored procedures in the msdb database.

The error message is:
*The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account X (date). Exception Message: Cannot send mails to mail server. (Failure sending mail.).*

It seems to me that the error indicates a problem with the email connection, rather than SQL Server, so I tried to send from a different email address – but with the same error. Both of these outgoing email addresses work reliably for my normal work (using Thunderbird). The set-up data was copied from Thunderbird.

I have used the set-up SQL from https://www.codeproject.com/articles/485124/configuring-database-mail-in-sql-server 

    -- Enable service broker in the MSDB database.
    USE [master]
    GO
    ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
    GO
    
    --Enabling Database Mail
    sp_configure 'show advanced options',1
    reconfigure;
    GO
    
    -- Enable the db mail feature at server level
    sp_configure 'Database Mail XPs',1
    reconfigure;
    
    --Creating a Profile
    EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SQLProfile',
    @description = 'Mail Service for SQL Server' ;
    
    -- Create a Mail account 
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL_Email_Account',
    @email_address = 'somebody@gmail.com',
    @mailserver_name = 'smtp.googlemail.com',
    @port=465,
    @enable_ssl=1,
    @username='somebody@gmail.com',
    @password='Emailid password'
    -- TODO ENSURE VALID PASSWORD FOR THE ACCOUNT IS ENTERED ABOVE
    
    -- Adding the account to the profile
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SQLProfile',
    @account_name = 'SQL_Email_Account',
    @sequence_number =1 ;
    
    -- Granting access to the profile to the DatabaseMailUserRole of MSDB
    EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'SQLProfile',
    @principal_id = 0,
    @is_default = 1;

The above is not intended to be run as a single script; each step was performed separately and each step worked.

Email was tested with:

    --Sending Test Mail
    -- See https://msdn.microsoft.com/en-us/library/ms190307.aspx  for all arguments
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLProfile',
    @recipients = 'somebodyelse@yahoo.co.uk',
    @body = 'Database Mail Testing',
    @subject = 'Database Mail from SQL Server';
    
    select * from msdb.dbo.sysmail_event_log


Then try a different email address:

    USE [master]
    GO
    
    -- Create a Database Mail account 2
    EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL_Email_Account 2',
    @description = 'Alternative Mail account',
    @email_address = 'somebody3@gmx.co.uk',
    @display_name = 'Alternate GMX',
    @port=465,
    @enable_ssl=1,
    @username='somebody3@gmx.co.uk',
    @password='ValidPassword',
    @mailserver_name = 'mail.gmx.com';
    GO
    
    -- Add the account 2 to the profile
    EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SQLProfile',
    @account_name = 'SQL_Email_Account 2',
    @sequence_number = 2 ;
    
    -- Make one not the default
    EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
    @profile_name = 'SQLProfile',
    @principal_name = 'public',
    @is_default = 0 ;
     
    -- Show the new default profile
    EXEC msdb.dbo.sysmail_help_principalprofile_sp

But this also failed with the same error. There are no messages in the SQL Server logs nor my firewall log.

I have tried some diagnostics that web searches have suggested:

    Use msdb
    Go
    
    select * from sysmail_profile
    select * from sysmail_account
    select * from sysmail_profileaccount where profile_id=1
    select * from sysmail_server
    EXEC msdb.dbo.sysmail_help_account_sp;
    EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'SQLProfile';
    EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
    EXEC msdb.dbo.sysmail_help_principalprofile_sp;
    EXEC msdb.dbo.sysmail_help_status_sp;
    exec [dbo].[sysmail_configure_sp] 'LoggingLevel', 3

All the return values are normal, so I’ve not posted a duplicate of the set-up data. My only problem is that setting LoggingLevel to verbose does not seem to have any effect – is that data stored elsewhere that I have not looked?

Any hints would be appreciated, either to fix the problem or for more diagnostics. Thank you.




                        
Asked by Peter Bill (153 rep)
Jan 26, 2017, 09:48 AM
Last activity: Sep 29, 2023, 09:38 AM
sp_send_dbmail fails: Cannot send mails to mail server

Related Questions
