1

I have been trying to setup sendgrid on SQL Database Mail but all the time it says Cannot send mail to mail server.

This is what i have done.

  1. I have sendgrid accound with abc@mydomain.com and created api key and SG.xxxx password.
  2. As this is prod setup instance, i had done domain level authentication and my webmaster updated CNAME as requested by sendgrid.
  3. Tested using TELNET smtp.sendgrid.net 587 and authenticated with SG.xxxx password Base64 encoded and this was able to send email from abc@mydomain.com to xyz@mydomain.com
  4. I setup profile with abc@mydomoain.com as SQL notification Profile and selected Basic Authentication and provided 'apikey' as username and 'SG.xxx' as password in Database Mail - 'Configure Database Mail' option in SQL SSMS. Unchecked - 'The server requires secure authentication (SSL) connection' Port 587
  5. I tried to 'Send Test Mail' with 'abc@domain.com' to 'xyz@mydomain.com' but the Database Mail reflects as below screenshot error

I have tried using abc@mydomain.com and sendgrid account password in Database Mail Config wizard for Basic authentication, but sendgrid suggested they don't accept Basic authentication. Please help how and where should i pass the api key if not under Basic Authentication ? I tried updating with msdb.dbo.sysmail_add_account_sp nad updating records but it too didn't resolve and send email from Database mail.

Any help/ guidance would be greatly appreciated or if you had faced this how have you resolved it.

Database Mail Log

Aj S
  • 21
  • 4
  • When SendGrid says it doesn't accept Basic Authentication, that means with your account username and password. When sending like this, you should use the username "apikey" and an API key with mail sending privileges as the password. Are you still base 64 encoding the auth credentials when you set them in SMSS? Is there a more detailed error message available? – philnash Sep 27 '21 at 01:08
  • i tried both username as "apikey" and base64 encode of "apikey" as YXBpa2V5. Password too as Base64 encoded using openssl from linux box. None works – Aj S Sep 27 '21 at 21:42
  • Sorry, I haven't set this up before. As far as I know, when using Basic Auth, the username should be "apikey" and the password your actual API key. I don't have any insight into what is happening in your DB config. – philnash Sep 28 '21 at 00:19

1 Answers1

0

Hi the solution is as follows

Create a dbmail profile

  • server name: smtp.sendgrid.net
  • port : 25

SMTP Authentication

  • Basic Auth
  • User name: apikey
  • Password: : {the api-key for your sendgrid account}

Thats it. Send a test email.

If it still doesn't work, if you are on AWS EC2 ensure that security group allows outbound port:25

Here is the link from sendgrid about apikey authentication. https://docs.sendgrid.com/for-developers/sending-email/upgrade-your-authentication-method-to-api-keys

jcrawfor74
  • 1,613
  • 2
  • 10
  • 8
  • Also if you are planning to use this to send notifications from failed sql agent jobs, ensure that you set this profile as the Database mail profile to use as the default for sending email. Properties on SQL Agent and then "Alert System" and "Enable mail profile" – jcrawfor74 Mar 04 '22 at 06:41