Sql-server – Database Mail not sending

You are running into the dreaded 'sandboxed' mode of the EXECUTE AS context, as described in Extending Database Impersonation by Using EXECUTE AS. In short code running under EXECUTE AS USER ... is trusted only inside the database context, not at the instance context.

There are three ways out:

• the easy way: mark the current database as TRUSTWORTHY ALTER DATABASE [...] SET TRUSTWORTHY ON;
• the correct way out: use code signing
• the cheat: use EXECUTE AS LOGIN

If in your environment the dbo of the current database is trusted then you can go with TRUSTWORTHY. It will work, but if this property is set then any db_owner in the current DB can elevate himself to server admin.

If you want a 'correct' solution then:

1. move this code in an stored proc
2. sign the stored proc with a certificate
3. drop the private key (so that it can never be used again to sign anything)
4. export the public key, import it in [msdb]
5. create user in [msdb] derived from this certificate
6. grant necessary permissions (AUTHENTICATE, EXECUTE on sp_send_mail) to the certificate derived user

Trivial, heh? BTW, every time you modify the signed stored proc the signature is lost and the procedure has to be repeated. See Call a procedure in another database from an activated procedure for an example.

I totally don't recommend using EXECUTE AS LOGIN instead.

see what happens when you try to send an email using powershell instead.

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql" 
$body = "test email from ccatsql " 
$smtp.Send($emailFrom, $emailTo, $subject, $body)

and checked which .Net versions you have installed

the problem might be in the .NET. Check the windows\applications error log. I had a similar problem (see the question here) then I installed .NET 3.5 and all started to work nicely.

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

How can I see the current Database Mail configuration?

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

Have a look at this question and answer: Comprehensive troubleshooting of DatabaseMail