I am trying configure SQL database mail send email Gmail to Gmail but I got: Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2021-04-14T09:21:14). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )
I do not know what I am doing wrong.
---- create the database account ----
Use MSDB
go
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'SQLServer Express')
BEGIN
--CREATE Account [SQLServer Express]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLServer Express',
@email_address = 'sangitar23@gmail.com',
@display_name = 'Audit Partnership',
@replyto_address = '',
@description = '',
@mailserver_name = 'smtp.gmail.com',
@mailserver_type = 'SMTP',
@port = '587',
@username ='sangitar23@gmail.com',
@password = 'Atlanta2020!',
@use_default_credentials = 0 ,
@enable_ssl = 1 ;
END --IF EXISTS account
------ create the database mail profile --------
Use MSDB
go
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'SQLServer Express Edition')
BEGIN
--CREATE Profile [SQLServer Express Edition]
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLServer Express Edition',
@description = 'This db mail account is used by SQL Server Express edition.';
END --IF EXISTS profile
------------ To assign a database mail account to the database mail profile--------
Use MSDB
go
IF NOT EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = 'SQLServer Express Edition'
AND a.name = 'SQLServer Express')
BEGIN
-- Associate Account [SQLServer Express] to Profile [SQLServer Express Edition]
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLServer Express Edition',
@account_name = 'SQLServer Express',
@sequence_number = 1 ;
END
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServer Express Edition',
@recipients = 'sangitarai023@gmail.com',
@body = 'Email notification for Audit Partnership SFTP',
@subject = ' This email has been sent from SQL Server to notify, client uploaded data on the SFTP cite' ;
