I’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:
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:
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:
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.
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' ;