CODEWITHSUNDEEP
sql-serversql-server-2014sql-server-2014-expresssp-send-dbmaildbmail
Harsh Shankar
Harsh Shankar

Reputation: 526

SQL Server Mail senging error using sp_send_dbmail on gmail server

I am setting up a trigger based mail notification and while using it, I am also getting error.

The Profile setup is as bellow:

EXECUTE msdb.dbo.sysmail_add_account_sp @account_name               = 'TestMailAccount',
                                        @description                = 'Test Mail Account for sending notifications',
                                        @email_address              = '[email protected]',
                                        @display_name               = 'Test Mail Notification',
                                        @username                   = '[email protected]',
                                        @password                   = 'my_gmail_password',
                                        @mailserver_name            = 'smtp.gmail.com',
                                        @port                       = 587,
                                        @enable_ssl                 = 1;

EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'TestMailProfile',
                                        @description = 'Main test profile used to send notification email';

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  @profile_name       = 'TestMailProfile',
                                                @account_name       = 'TestMailAccount',
                                                @sequence_number    = 2;

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
                                                @profile_name   = 'TestMailProfile',
                                                @principal_name = 'public',
                                                @is_default     = 0;

Now to send the mail, I executed:

DECLARE @mail_body NVARCHAR(MAX);
SET @mail_body  = CONCAT(   N'<html>',
                                N'<body>',
                                    N'<h1>Test Mail</h1>',
                                N'</body>',
                            N'</html>');
EXECUTE msdb.dbo.sp_send_dbmail 
        @profile_name   = 'TestMailProfile', 
        @recipients     = '[email protected]', 
        @subject        = N'DB Test Mail', 
        @body           = @mail_body,
        @body_format    = 'HTML';

After this, I checked the log:

select * from sysmail_event_log

The description shows:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 42 (2020-03-01T18:41:09). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication Required. Learn more at).

I already have enabled google account's setting for using Less secure app access.

I am not sure what I am missing and any help will be highly appritiated.

Upvotes: 0

Views: 872

Answers (1)

granadaCoder
granadaCoder

Reputation: 27904

Gmail settings look correct. With the caveat of "try different port" (also mentioned in the comments of the original question above)

From https://granadacoder.wordpress.com/2006/02/08/smarter-emailsmtp-setup-with-dotnet-configuration-sections-1-1-and-2-0/

<!–Note, with the 2.0 Framework, my tests show that gmail likes port 587–>

    <smtpServer 
        smtpServerName="smtp.gmail.com" 
        defaultEmailFrom="[email protected]" 
        portNumber="465" 
        authenicationMode="SSL" 
        smtpUserName="[email protected]" 
        smtpUserPassword="mygmailpassword"/>

More likely, you probably have a port blockage.

https://www.microsoft.com/en-us/download/details.aspx?id=24009

This is what I use to ping ports. If you can install this small utility on the sql-server machine, it'll go a long way.

On the outside chance you're running sql-server on linux...you can "apt install curl" (or similar) and ping the port that way.

But sending email from sql-server has always been tricky. You need the exact settings, and you need port (un)blocks. Most times its the port(s) issue.

If the portqueryui tool above reports "blocked" or "filtered"...you may have to open an port on your computer.

https://support.microsoft.com/en-us/help/4028544/windows-10-turn-windows-defender-firewall-on-or-off

Your router/network can also block ports. Again,the portqueryui tool is a quick way to test the path from the machine you're trying to get out to internet-land.

Upvotes: 0

Related Questions