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 = 'email@example.com', @mailserver_name = 'smtp.googlemail.com', @port=465, @enable_ssl=1, @firstname.lastname@example.org', @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 = 'email@example.com', @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 = 'firstname.lastname@example.org', @display_name = 'Alternate GMX', @port=465, @enable_ssl=1, @email@example.com', @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.