SQL Server :: Why your Database Mail is not working and probably never will

Database MailI’ve passed the Microsoft Certification DP-300 (at the second try). I was surprised to see that every now and then the exam was bringing up the Database Mail feature in an exam mainly focused on Azure.

The fact that SQL Server could send e-mail it has always intrigued me and few years ago I had also created a tool aimed to send you e-mails.

But now a few years have passed, I tried to use it and “kaboom!”; nothing worked.

So let’s dive together into the meanders of this appealing feature.

First approach: using Outlook Office 365 e-mail

If I could have 1BTC for every time I saw Microsoft being incompatible with Microsoft I could be a millionaire.

And this is once again the case. If you try to setup the mail account this way:

Outlook SMTP Database Mail

You will end up with the error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 13 (2021-06-11T21:48:26). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.57 Client not authenticated to send mail. [ZR0P278CA0025.CHEP278.PROD.OUTLOOK.COM]).)

The problem is that your Outlook e-mail is using 2FA.

You can probably disable the 2FA for your Outlook e-mail if you want.

But if you want you can also detach your seat belt and expose half of your torso out of the car window while driving.

There is also the option Windows Authentication using Database Engine service credentials but if you want to go down that pattern you then have to go into the MSSQLSERVER service and change the login account from Local system account to your user.

C’mon, who could possibly setup a server this way on production?

Second attempt: using Gmail e-mail

If your company is not using Outlook there is a good chance that you are using Gmail. So I setup the e-mail account for Gmail:

Gmail SMTP Database Mail

And the result is slightly the same, 2FA is preventing us from sending e-mails:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1010 (2021-06-11T22:43:25). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication Required. Learn more at).)

Plus you might have received an alert on your phone saying : “Google stopped the sign-in attempt, but you should review your account’s activity.”

Third attempt: using my domain e-mail

Let’s use my domain e-mail. I don’t have 2FA for that.
My domain is stored on GreenGeeks and has SMTP port set to 465 by default:

SQL Server send e-mail port 465

At this point at least I have a new error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2021-06-15T23:52:08). Exception Message: Cannot send mails to mail server. (The operation has timed out.).)

This is because I’m using port 465 which has been lately deprecated.

But there’s a way out: your ISP or hosting might tell you that port 465 is the default one but that doesn’t mean that 25 or 587 aren’t available. That was my case and I was effectively able to send e-mails from both ports 25 and 587.

Conclusion

Setting up Database Mail in SQL Server is painful because you end up going back and forward thousand times. You keep creating, deleting, creating till it works but this is like playing naval battle for hours. The setup this feature is a maze, you get frustrated and is overall undignifying .

That why I created for you this T-SQL query that makes the job done at first try (if you don’t use Microsoft or Google e-mail):

DECLARE @YourEmail NVARCHAR(50) SET @YourEmail = '[email protected]' --Put here your E-mail
DECLARE @YourPassword NVARCHAR(50) SET @YourPassword = 'MyPassword' --Put here your Password
DECLARE @YourSMTPSserver NVARCHAR(50) SET @YourSMTPSserver = 'SMTP.account.com' --Put here your SMTP Server
DECLARE @YourPort int SET @YourPort = '587' --Put here your SMTP port

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'My_Account',  
    @description = 'Mail account for you.',  
    @email_address = @YourEmail,  
    @replyto_address = @YourEmail,
	@display_name = 'Your Mailer Account',  
    @mailserver_name = @YourSMTPSserver, 
	@port = @YourPort,
	@use_default_credentials = 0,
	@username = @YourEmail,
	@password = @YourPassword;


-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'My_Profile',  
    @description = 'Your profile used for your e-mail' ; 

-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'My_Profile',  
    @account_name = 'My_Account',  
    @sequence_number =1 ; 

-- Grant access to the profile to the DBMailUsers role  
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'My_Profile',  
    @principal_name = 'public',  
    @is_default = 0; 

EXEC msdb.dbo.sysmail_help_account_sp;

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
 
-- enable Database Mail XPs
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
 
-- check if it has been changed
EXEC sp_configure 'Database Mail XPs'
GO
 
-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

And if you want to delete it:

EXECUTE msdb.dbo.sysmail_delete_profile_sp  
    @profile_name = 'My_Profile' ;  

EXECUTE msdb.dbo.sysmail_delete_account_sp  
    @account_name = 'My_Account' ;

 

Related

Leave a Reply

You have to agree to the comment policy.