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:
|
1 |
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:
|
1 |
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:
|
1 |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
DECLARE @YourEmail NVARCHAR(50) SET @YourEmail = 'my-email@address.com' --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:
|
1 2 3 4 5 |
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'My_Profile' ; EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'My_Account' ; |
To setup Database Mail Using OFffice 365 Email user as the sender you need to us an application password and:
• As the SMTP relay used is : SMTP AUTH client submission
• This option is not compatible with Microsoft Security Defaults.
• Hence we Disabled Security Defaults :
Azure Active Directory > Click on Properties in left action pane > Scroll Down & Click on Manage Security Defaults > Toggle the button to Off
• Now make sure Authenticated client SMTP submission (SMTP AUTH) is enabled for user.
Go to Admin center ( https://admin.microsoft.com ) > Users > Active Users > Click on User who is sending emails via SMTP relay > Click on Mail > Manage email apps > If Authenticated SMTP is unchecked, select it and save OR If Authenticated SMTP is checked already, uncheck it save wait for 2 minutes and check it again and save it.
If Authenticated SMTP is checked already, uncheck it save wait for 2 minutes and check it again and save it <– and we are back in business. Thanks a lot!
Also to use Datbase Mail with Office 365 you need to be using TLS 1.2
Bonjour,
Il semblait que le sujet de départ concerné l’envoi de mail avec Office 365,
mais bizarre que ensuite vous présentez une solution parlant du smtp d’un hébergeur.
Avez vous une solution pour l’utilisation de office 365 avec Database Mail??
si oui je suis très intéressé, prière de nous la partager svp.
Cordialement.
Desabiliter le 2FA
That was very detailed, thank you Ivan
Hi
I had the same issue with my Microsoft365 account.
I change the Port from 587 to 25 – and I worked.
But it depends on your firewall settings
THIS WAS FREAKIN’ AWESOME … spun wheels for days attempting to configure mail in SQLExpress … perfect documentation … and I’ve been in this business since 1977!!! Thank You!!!
Mark C
Charlotte, NC
What was the result? Were you using Microsoft or Gmail?
In the end were you able to send that e-mail?
If Authenticated SMTP is checked already, uncheck it save wait for 2 minutes and check it again and save it <– and we are back in business. Thanks a lot!
My problem was my profile needed to grant sendmail permissions to my userid. I did this by creating a profile, ssms management/email/manage configuration/manage profile security and add the user name to both public and private.
Yep, Richard!
And the T-SQL script at the bottom of my post is going that too. (EXECUTE msdb.dbo.sysmail_add_principalprofile_sp)
Thanks a bunch. I could not get SQL Server email to work for the last hour or so. I ran your script and email worked the first time I tried it. Bizarrely it stopped working after that, but did give a different error message (bad authorization or TLS needed). So I manually set the TLS and it is working fine now.
Nice catch! I will check if there is a way to script out the TLS authorization
Hi Graeme,
Thanks for your feedback, How did you manually set the TLS ?
Thanks
Tick “This server requires a secure connection (SSL)” also use Nartac (https://www.nartac.com/Products/IISCrypto/) to check if the right TLS version is installed
sql server 2019 standard fully updated
used your script but still not working
Outlook 2016 installed on the server works normally (same email)
I have my own domain
i granted permission to everyone to use the profile
what can be the issue ?
Try to manually set the TLS as @Graeme said in the previous comment and let me know. Thank you
i have been working on it the whole day and could not resolve it. you have to add a regedit DWORD SchUseStrongCrypto with a value 1.at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.NETFramework\v4.0.30319 this will resolve it. i hope it helps
Thank you @Yasir, checking the SSL-checkbox and restarting Windows wouldn’t be enough?
Apparently this was caused by Microsoft enforcing TLS 1.2. I tried all of the fixes outlined here but this one did the tick for me. Windows Server 2012r2.
https://support.microsoft.com/en-gb/topic/support-for-tls-system-default-versions-included-in-the-net-framework-3-5-on-windows-8-1-and-windows-server-2012-r2-499ff5ef-a88a-128b-c639-ed038b7d2d5f
the reason why we had to add the registry key manually is because for some reason it does not add it after the kb update.
Brilliant, thank you for sharing that link
HI,
I’m getting similar error, but from using reporting services in native mode (using Office 365). I’m sharing this page with my team as I think your steps will point us in the right direction. Can you offer any SSRS specific suggestions?
I don’t know much about SSRS but as a first step I suggest you to use a domain e-mail and not Google/Outlook365. Debug with a normal e-mail without SSO and test if that works.
If it works now try with Outlook365.
Hello,
I used Nartac to manually set TLS 1.2 but still I cannot send an email to gmail account through SMTP server. I followed all of the other instructions and it does not work. Error: The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication Required. My Windows Update is fully updated and SQL Server is fully updated.
This because you haven’t removed the 2FA
You are right, thanks!
Boa tarde!
Gostaria de agradecer ao seu conteúdo, sua solução funcionou de forma precisa.
Obrigado!
Hi, I want to congrats for your solution, my problem was solved.
Thank you!
No problem!