How to send SQL Server database mail through gmail

Question

I'm running SQL Server 2017 on a Windows 2012 R2 Server.

Database mail was working well connecting through a Gmail account for years but now I get the following error message:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2022-06-08T23:05:02). 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.

I believe this may have to do with Gmail shutting down their access to less secure apps but I'm not sure. I've gone into the Gmail account setup for this account but I can't figure out what to do to allow access now. They seem to want to force you to use two-step authentication and I have no clue how's that's supposed to work with an automated SQL server database mail.

Any suggestions or guidance? A google search didn't return anything useful specifically related to sending SQL Server database mail through Gmail. This does have to go through Gmail, unfortunately.

Nothing changed with our SMTP Server. The only thing I know that changed is Gmail.

Any help would be greatly appreciated. Thanks!

Answer 1

https://stackoverflow.com/questions/72532688/the-smtp-server-requires-secure-connection-or-the-client-was-not-authenticated

That stack overflow link solves the issue, although they are using C# I replicated the steps on my own database and emails are being sent out again. Basically you need to create an app password in Gmail, but below are the steps I followed to resume the mailing system. If you can't find the security tab. Search for "Gmail Security tab" on google itself.

Now you can manage Apps password:

1. Go to Gmail account settings , and click on security tab
2. Add 2 step verification (if you don't have)
3. Click on "App Password"
4. Select application device or add new one (Select 'Other' when it askes you to select an app you want to connect to, then enter in whatever name you want to give the app and generate a
   new password.)
5. You will get new password to use in your app

---

Additional Steps for SQL Server------------------------
6. Head to the database mailing system.

7. In your database mailing system you want to manage the existing account that previously was sending out mail and make sure you have "This server requires a secure connection (SSL)"
   checked.
8. For the SMTP Authentication select "Basic Authentication" then enter in your email address in the username box.
9. For the password paste in the one you generated earlier.
   10. Send out a test email/retry any previously failed email attempts.

Answer 2

Hi @Karen Grube ,

Did you configure Gmail to allow access to send an email?

Setting Gmail account to enable less secure apps to be accessed.In order to enable access to the less secure app in the Gmail account to follow the following steps.

1.Click on the “Account settings” option
2.Choose “Turn on” under “Allow for less secure apps”

Refer to below blog to get detail steps about setup Database Mail in SQL Server using Gmail.

How to Setup Database Mail in SQL Server using Gmail?

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Are you sending the mail through your local SMTP server, or do you connect directly to gmail? The normal thing would be connect through your local SMTP server. If Gmail requires two-factor authentication, you can of course through connect directly from SQL Server, which does not support this and have absolutely no reason to support this.