Sql-server – Use SQL Server Database Mail with Amazon SNS

database-mailsql serversql-server-2012

Is it possible to configure SQL Server 2012 Database Mail (hosted on EC2) with Amazon SNS?

I'd love a single place to manage all alert notifications and their subscribers in SNS, but Database Mail requires SMTP account information which SNS doesn't seem to provide, but I'm not sure.

Best Answer

I have been away from SQL Server for over a decade but will try to answer this based on my familiarity with what SNS is(n't).

SNS is able to fan-out a notification to multiple destinations over a number of outbound transports:

  • email (raw, message body only)
  • email (json, with metadata about the SNS message in addition to the body)
  • http or https to a web endpoint
  • SMS to a mobile device
  • SQS, publishing the message to Amazon Simple Queue Service queue(s)

However, it only has one inbound transport: a web API, so it isn't exactly an email distribution management platform though it does have portions of that functionality. As such, you wouldn't be able to submit messages directly into SNS from an SMTP client.

If there is a mechanism by which you could publish the notifications with an http client within SQL Server, then you could potentially wire this in... but otherwise, you'd need to find or write an SMTP-to-SNS gateway.

Such a "gateway" would emulate an SMTP server on the front-end and an SNS API client on the back-end, to bridge this gap. I've written one of these in Perl as a proof-of-concept, but it's not in production yet and it has a lot of hard-coded proprietary assumptions, so wouldn't be particularly useful outside of my specific environment -- but is was not particularly difficult to implement. The message ID returned by SNS is handed back to the SMTP client as the SMTP transaction id. (This implementation assumes the message is being submitted by a smart host, and puts the burden on the SMTP client, returning a 4XX error if for any reason the SNS API interaction fails -- accepting emails from SQL Server, you might need to shift that burden onto the gateway.)

(SNS can also send addressible push notifications to mobile applications, but this is a very different application of SNS, and uses a different API, but also doesn't accept inbound SMTP).

Related Question