asked on

MSsqlserver TLS

Hi,

We are getting below error in our MS SQL server 2016 when sending database emails. And when tried sending email through telnet, getting attached error. (Use SSL is already tick marked in SQL db profile)

‘The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-02-11T15:29:24). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )’

I have come across below link which might have solution on it but I am not sure about it and its a production database server so don't want to risk it.

https://dba.stackexchange.com/questions/213608/enable-tls-1-2-for-sql-server-2016-database-mail

It seems issue is related to TLS settings on Windows server 2016 or SQL server 2016. The registry mentioned in above link is not present in server. Can you suggest how can we resolve this issue? Also recently we have implemented Office 365 federation with Okta and we have tried sending the email from database using onmicrosoft.com email address to bypass okta but its still failing - still not sure if Okta federation has to do anything with the issue.

gr8gonzo

No, it's not related to Okta.

Sending through telnet will definitely fail since you probably can't manually replicate the SSL handshake.

I would first try to make sure that your port in your mail account configuration is set to 587 (the typical port supporting STARTTLS). Then triple check your credentials.

Amit

ASKER

@gr8gonzo Hi - Thanks for the response. Yes port in the DB profile config is set to 587 and I have tried using a few email accounts but it still gives the same error in the database logs. So I started thinking that it might not be related to how I have configured the DB email profile or which credentials I am using - error is still same.

David Favor

A @gr8gonzo mentioned, if you're submitting mail on port 587, this is almost surely an authenticated TLS connection.

Telnet != TLS

Telnet can't + never will be able to speak TLS.

This type of debugging is best done with a debugging tool, not a database.

The way I do this type of debugging... which is dirt simple...

1) Start with SWAKS first, to verify you understand what's required to send your email.

This will verify your user/pass for authentication

2) Next you'll require a working relay, like https://MailGun.com on an in-house MTA.

Best to start with MailGun or something similar, rather than debugging your in-house MTA.

Then after your mail delivery is working, switch to your in-house MTA... fixing any problems you encounter...

3) Example syntax to use... where MailGun is your relay... which will change if you're using another relay...

swaks -s smtp.mailgun.org:587 -tlsp tlsv1_3 -auth -au $user -ap $pass --from=$from --header "Test Message: $(date)" --to=$to

Open in new window

4) You can experiment with the TLS settings to correctly target highest (most secure) TLS protocol your relay supports.

David Favor

Aside: If you have no way to send TLS secured email on your database instance, you can always route your email through SWAKS for delivery as SWAKS speaks TLS.

Amit

ASKER

The setup has been properly working until last month so this is not something which hasn't been tried and tested on current environment. I am wondering if there could be anything fundamentally have went wrong on SQL server or windows server which I can check or if there could be any known issues which could stop sending database emails over Office 365 that I can try identify. The link I have copied with the question shows something about the registry entry and claims that the issue was resolved upon adding Registry entries. Is it worth trying to add registry values? Its production database so do not want to risk it if there is no benefit of adding that registry at all.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.