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)

asked 22 Jan '21, 12:17

lferreira's gravatar image

lferreira
1017711
accept rate: 0%

[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)

(22 Jan '21, 12:18) lferreira

Any ideas?

(22 Jan '21, 12:19) lferreira
1

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

(22 Jan '21, 14:42) Chris Keating

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

(27 Jan '21, 09:03) lferreira

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

(27 Jan '21, 12:53) Chris Keating

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

Thanks for your comments.

(27 Jan '21, 12:58) lferreira

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 :)

(27 Jan '21, 14:56) Breck Carter
1

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!"

(02 Feb '21, 06:35) Justin Willey

Are you thinking of the Bot API?

(02 Feb '21, 06:37) Justin Willey

> 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.

(02 Feb '21, 13:24) Breck Carter
Comment Text Removed

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.

(08 Feb '21, 08:03) Justin Willey

> Slack channels as an an alert mechanism

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

(08 Feb '21, 09:32) Breck Carter
2

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:

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

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

(15 Feb '21, 12:24) Justin Willey
Comment Text Removed
Comment Text Removed

...thank you!

(15 Feb '21, 14:29) Breck Carter
1

Hm, does Slack also allow to hijack a thread? ;)

(16 Feb '21, 03:04) Volker Barth
1

It's something of a speciality :)

(16 Feb '21, 12:25) Justin Willey
More comments hidden
showing 5 of 16 show all flat view
Be the first one to answer this question!
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:

×28
×13

question asked: 22 Jan '21, 12:17

question was seen: 1,159 times

last updated: 10 Jan '22, 02:27