Send email issue

[former_SQLA_member1694912]

We moved one of our databases to an AWS EC2 instance, we are testing sending email through maintenance plan, but it is not working. The AWS SMTP server requires authentication but does not need a certificate, but I keep getting the message "must issue a STARTTLS command first". (Error attached)

[former_SQLA_member1694912]

[SAP][ODBC Driver][SQL Anywhere]RAISERROR executed: dbo.xp_startsmtp() failed: Unknown error (return code = -1) Must issue a STARTTLS command first (mail error code = 530)

[former_SQLA_member1694912]

Any ideas?

[chris_keating]

I have not tried this and my suggestion is based on google. Is your xp_startsmtp using secure=1 or trusted_certificate='*'?

[former_SQLA_member1694912]

I was not using certificate in the beginning because it was supposedly not necessary but now based on some tests, I followed the steps documented in: https://aws.amazon.com/premiumsupport/knowledge-center/smtp-connectivity-timeout-issues-ses/ looks like I need one.

So, according to this: "Be sure that the Amazon SES certificate is installed on your server "

I followed this link: https://aws.amazon.com/blogs/messaging-and-targeting/669-2/ I download a certificate from https://www.amazontrust.com/repositoryand added it to the store.

When I try to use it, selecting "This is a secure SMTP server" in SQL Central and the certificate file, I got the error below:

[SAP][ODBC Driver][SQL Anywhere]RAISERROR executed: dbo.xp_startsmtp() failed: Socket error (return code = 6)  (mail error code = 0)

SQLCODE: -20001
SQLSTATE: HY000
SQL Statement: email: BEGIN
    DECLARE @SMTP_RETURN_CODE INT;
    @SMTP_RETURN_CODE = CALL dbo.xp_startsmtp( smtp_sender = 'no-reply@ascendertxalerts.com', smtp_server = 'email-smtp.us-east-1.amazonaws.com', smtp_port = 587, smtp_sender_name = 'Test', smtp_auth_username = '***', smtp_auth_password = '***', trusted_certificates = 'file=C:\\\\File\\\\certificate.crt' );
    IF @SMTP_RETURN_CODE <> 0 THEN
        RAISERROR 20001 'dbo.xp_startsmtp() failed: ' || get_mail_error_message( @SMTP_RETURN_CODE );
        LEAVE email;
    END IF;
    @SMTP_RETURN_CODE = CALL dbo.xp_sendmail( recipient = 'name@gmail.com', subject = 'Maintenance plan test', "message" = 'Maintenance plan Full Backup test');
    IF @SMTP_RETURN_CODE <> 0 THEN
        RAISERROR 20001 'dbo.xp_sendmail() failed: ' || get_mail_error_message( @SMTP_RETURN_CODE );
    END IF;
    @SMTP_RETURN_CODE = CALL dbo.xp_stopsmtp();
    IF @SMTP_RETURN_CODE <> 0 THEN
        RAISERROR 20001 'dbo.xp_stopsmtp() failed: ' || get_mail_error_message( @SMTP_RETURN_CODE );
    END IF;
END;

I also tried "In the operating system certificate store" option, leaving the optional company, unit and name blank.

Received the message: "The test email was sent successfully. Please check your email now", but I never got the email.

Any ideas would be great!!!

Thanks

[chris_keating]

Did you check whether the email was treated as "spam"?

Is your AWS account new and in the Amazon SES sandbox? if so, have you verified addresses and domains or sent to the Amazon SES mailbox simulator? See https://docs.aws.amazon.com/ses/latest/DeveloperGuide/request-production-access.html

[former_SQLA_member1694912]

Yes, I checked the "spam" folder. Amazon SES is working through the application.

Thanks for your comments.

[Breck_Carter]

This is (almost) completely off topic, but has anyone experimented with sending application alert messages via telegram instead of email? (yes I know how silly that would have sounded a few years ago 🙂

[justin_willey]

You are in danger of sounding like an elderly relation of mine who, having had the SMS features of her new mobile phone explained to her, said "Ah, I understand, it's a machine for sending telegrams!"

[justin_willey]

Are you thinking of the Bot API?

[Breck_Carter]

> Are you thinking of the Bot API?

Maybe, maybe not... what I was hoping that someone else had done the legwork to determine if telegram could be used as a path for delivering messages from inside a SQL Anywhere database to human users in meatspace :)... there are three telegram APIs, of which the Bot API is one.

FWIW telegram.org claims 500 million users.

[justin_willey]

Not tried Telegram, but we have used Slack channels as an an alert mechanism - users can then control the notifications / emails etc they get through Slack.

[Breck_Carter]

> Slack channels as an an alert mechanism

Have you sent alerts via Slack from code inside SQL Anywhere stored procedures?

[justin_willey]

We are currently using our own endpoint, which does a load of processing, and then posts to Slack using their API and our own Slack App.

However I suspect a much more straight forward way would be to use Slack's "incoming web hooks" https://api.slack.com/messaging/webhooks You could then post (with JSON content) directly from the stored procedure.

After setting up the Slack account, creating the channel and the webhook, you get given a unique URL. Then posting to it is easy:

POST https://hooks.slack.com/services/T00000000/B00000000/XXXXXXXXXXXXXXXXXXXXXXXX

Content-type: application/json { "text": "Hello, world." }

You can do fancy stuff in the JSON including interactive thingies, such as links back into Foxhound

[Breck_Carter]

...thank you!

[VolkerBarth]

Hm, does Slack also allow to hijack a thread? 😉

[justin_willey]

It's something of a speciality 🙂