By googling, I found a way to send secure email from SQL Anywhere code by calling xp_startsmtp passing parameter trusted_certificates='SMTPS=Yes;Secure=1'. I send using port 587.

This works on my computer, but did not work on a customer's server. On the customer's server xp_get_mail_error_text returned message, 'Unable to open certificate file 'SMTPS=Yes;Secure=1''.

What does 'SMTPS=Yes;Secure=1' mean and where does it come from? I don't see anything about it in the SQL Anywhere documentation. Certificates are not my area of expertise.

Can anyone help me with why this works in my environment and not my customer's?

Most importantly, any guidelines for sending secure email from SQL Anywhere code?

For what it's worth, I've sent email using other SMTP APIs, and it's pretty easy. Nothing to do with 'trusted certificates'.

Thank you!

asked 09 Feb, 15:12

dharrel's gravatar image

dharrel
280111123
accept rate: 0%


trusted_certificates is a Long VarChar and should contain 'file=' the full path (relative to the Sybase server) and the file name, otherwise secure=1 will default to the OS certificate store. I also believe that you can simply use the file path as the trusted_certificates single parameter.

Mail servers are now getting very picky about authentication. Most will accept without a certificate, but will insist upon encryption, and will look for TLS 1.2 or TLS 1.3 as the standard. If the test one from your PC works, it is probably through an ISP that has not caught up to the latest standards, or it found a valid certificate in your store and used a later encryption method. If you try connecting to a mail server like o365 it will be rejected if your customer has outdated SSL and/or TLS. But the error indicates that it could not find a default file to open.

When you send a test email check the headers to see the encryption type - you should see something like this...

(using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 256/256 bits) (Client did not present a certificate) by CMGW with ESMTP

Also, bear in mind that now mail will either not be accepted, or end up in spam, if DMARC, SPF and DKIM are not set.

Additional edit

For what it's worth, I've sent email using other SMTP APIs, and it's pretty easy. Nothing to do with 'trusted certificates'.

To clarify - trustedcertificates has an option not use one - you don't generally need a certificate to connect to an ISP mail server, but if you want a secure connection then the encryption method must be a later type. Specifying trusted_certificate=none will make a secure TLS connection without rendering a certificate.

As another edit

Simply leaving out trusted_certificates, or setting it to null, with 'secure=1' it will connect with TLS. I just tried it with only these parameters.

BEGIN
DECLARE return_Code INTEGER ;
return_code = CALL xp_startsmtp(
smtp_sender = 'You@YourDomain.com',
smtp_server = 'smtp.ionos.com',
smtp_port = '587',
smtp_sender_name = 'My Name',
smtp_auth_username = 'MyUserName',
smtp_auth_password = 'MySecretPassword',
secure = 1
);

@return_code = CALL xp_sendmail ( 
recipient     = 'Test@SomeOtherDomain.com',  
subject       = 'Test Subject',  
"message"     = 'Test message' );

CALL xp_stopsmtp();

select STRING('smtp return code = ', return_code, ' ', 'Error Code = ',  xp_get_mail_error_code(), ' ', 'Return Text = "', xp_get_mail_error_text(), '"') as 'SMTP returned values'

END
permanent link

answered 10 Feb, 03:51

gchq's gravatar image

gchq
366262937
accept rate: 22%

edited 10 Feb, 09:19

This looks very interesting, do you know how the target of the file parameter has to be encoded? Like PEM or DER or Java Certificate Storage or some PKCS#? Or is this OS dependent?

(10 Feb, 14:20) tedfroehlich
1

If you are talking about the certificate, then this will generally be a .pfx file.

(10 Feb, 16:01) gchq

Thank you for this detailed explanation. I was not aware of the "secure" parameter to xp_startsmtp(...) - it looks it was introduced in SQL Anywhere version 17. This customer runs version 16, and it's not available. Comparing documentation, it seems there are other secure email improvements introduced in SQL Anywhere 17. I'd be grateful for suggestions on what I can do to send secure email in SQL Anywhere 17. Is there something I can pass through trusted_certificates, and if so, how would I determine what that would be. Thanks again.

(12 Feb, 09:06) dharrel
Replies hidden

I'm not totally sure when 'secure' was introduced, but certainly older versions did not support encrypted authentication, just plain text. If that is the case, I would suggest building an app (my poison is .NET) to query the DB and pull the relevant data - that way you build it with a secure connection. If the intent is bulk mail, you can add a method to time them so that hundreds at once don't hit the ISP and it ends up as spam.

(12 Feb, 09:21) gchq

As per above I would recommend creating a simple Console App that can be triggered manually or at a specific time every day. That way as things change to OAuth, it's going to be easier to update.

There is a detailed description about connecting just using a certificate below, if you still want to go down that route - but I have no idea if this method still works as normally the certificate should be issued to the business and represent the domain name and be part of a chain that can be verified...

https://community.sap.com/t5/technology-blogs-by-sap/using-sql-anywhere-quot-innsbruck-quot-to-send-emails-through-gmail/ba-p/12977287

It's an old post and uses GMail, but is the same concept for most mail servers. The beauty of the 'secure' parameter in v17 is that it forces a TLS connection without a certificate.

If you don't have the documentation for v16 (and it seems to be rarer than the proverbial hen's teeth) it can be downloaded as a PDF here...

https://help.sap.com/docs/SAP_SQL_Anywhere?version=16.00.0

(13 Feb, 05:20) gchq

Thank you. This is great information.

If you're inclined, could you please clarify what you mean by, "That way as things change to OAuth, it's going to be easier to update." This is also not an area of my expertise.

(13 Feb, 09:16) dharrel
Replies hidden

Here are a few links - a lot of people are getting frustrated at the changes. Even if you got SMTP working there is a good chance that the mail server you use will catch up to 365 and Google and drop it, unless your customer uses their own Domino server. There is currently a workaround for 365 and SMTP, but it does involve dropping security levels on Exchange and that will probably be blocked very soon. However, POP3 and IMAP have now been totally disabled even with TLS in 365. Google are doing the same this summer.

https://stackoverflow.com/questions/71886205/switching-from-smtp-to-oauth2

https://www.thecodehubs.com/send-mail-using-oauth-2-0-in-net-core-6-0-part-2/

https://learn.microsoft.com/en-us/exchange/clients-and-mobile-in-exchange-online/deprecation-of-basic-authentication-exchange-online

https://support.google.com/a/answer/14114704?hl=en

(13 Feb, 09:43) gchq
showing 4 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×10
×4

question asked: 09 Feb, 15:12

question was seen: 194 times

last updated: 13 Feb, 09:43