0

I'm having issues sending Database Mail with "PLAIN" authentication on two different versions MSSQL Server.

  • SQL Server 2012
  • SQL Server 2019

Context

I have created backup jobs which regularly backup some databases to files and report success or errors via email.

I have managed to configure Database Mail to send out the respective messages, and confirmed via Wireshark and by studying the logs of the SMTP server that the emails are getting to the SMTP server.

Problem

However, I have a problem which is a show stopper. A user must log in into the SMTP server before the latter accepts the user's messages. Database Mail indeed tries to log in with the correct credentials, but uses LOGIN authentication, while the SMTP server only accepts PLAIN authentication.

That is, when establishing the communication, Database Mail at some point issues AUTH LOGIN ... when it should issue AUTH PLAIN .... Subsequently, it sends the credentials in the wrong form. Therefore, it is currently impossible to let Database Mail send messages using the SMTP server in question.

How can I make Database Mail use PLAIN authentication?

Improve this question

1 Answer 1

Reset to default
1

You are looking for Basic authentication:

enter image description here

But if the Database mail was working and suddenly stopped there is the chance that 2FA has been enabled.

You don't need Wireshark to check the SMTP server delivery success, you can check that on the Database Mail Log.

enter image description here

Improve this answer
9
  • Thanks for your answer, and +1. However, I already have used "Basic authentication" exactly as shown in your screenshot. The problem is that SQL server wants to use the "LOGIN" authentication method with the SMTP server instead of the "PLAIN" authentication method; this is shown by the SMTP server logs. The Database Mail logs are not very helpful, because they do not show the SMTP communication. Furthermore, I am 100% sure that 2FA is not the problem (it is not enabled at the SMTP server). The SMTP server log shows that the wrong authentication method is the problem.
    – Binarus
    Commented Feb 16, 2022 at 18:58
  • 1
    @Binarus, SQL Server Database Mail offers Windows Authentication, Basic Authentication and Anonymous Authentication. I searched on Google for sql server mail "plain" authentication and I found nothing. I don't know what Plain Authentication means and I don't find documentation about that. Where did you fin the words Plain Authentication and what they stand for?
    – Francesco Mantovani
    Commented Feb 16, 2022 at 20:11
  • It's a matter of SMTP. Extremely oversimplified, when a piece of software talks to a SMTP server which requires authentication, there are generally different methods to authenticate, like PLAIN, LOGIN, CRAM-MD5 and so on. Of course, only methods can be used which are supported by both parties. samlogic.net/articles/smtp-commands-reference-auth.htm may be a good starting point and roughly explains the three methods I mentioned, but there are more. I'm afraid that we can't configure SQL server in this respect (as we both couldn't find appropriate hints).
    – Binarus
    Commented Feb 16, 2022 at 21:18
  • As for the options in the screenshot: I don't know exactly how Windows Authentication works, but it would be the wrong option for us, because the SMTP server is not running under Windows. Anonymous authentication is probably for SMTP servers which don't require authentication at all, which does not apply to our SMTP server. Basic authentication is the right thing, but leads to the problem described above. In other words, when choosing Basic authentication, there should be an additional dropdown list for the authentication method (PLAIN, LOGIN etc.).
    – Binarus
    Commented Feb 16, 2022 at 21:39
  • 1
    Well, as I wrote in my question, that specific SMTP server only supports PLAIN, which is probably very rare. But I guess we are now at the point where it's better to use a different SMTP server, or to reconfigure that specific one to handle LOGIN as well. We would still prefer Database Mail using PLAIN, but since that doesn't seem to be possible, we'll go another route. Thanks for all your support and effort!
    – Binarus
    Commented Feb 17, 2022 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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