on 2024 Feb 09 3:12 PM
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!
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you are talking about the certificate, then this will generally be a .pfx file.
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.
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.
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...
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...
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/
User | Count |
---|---|
74 | |
30 | |
9 | |
7 | |
7 | |
6 | |
6 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.