1

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.

2

Sorry but SQL Server Express does not support database mail (check the "Additional Database Services" section):

https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

0

Mail Service is a Additional Database Services, which is Microsoft only Providing this services along with the SQL Server of version like :

  1. Enterprise
  2. Business Intelligence
  3. Standard
  4. Web

You shall not get mail services feature along with this edition of SQL Server version like :

  1. Express with Advanced Services
  2. Express with Tools
  3. Express

For Your Further Ref Features Supported by the Editions of SQL Server 2014

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.